Forum Archive

Hi:

I am looking for help to load a tableview from a database. Thanks.

@sendog3c11 quick and dirty

import ui
import sqlite3
tv = ui.TableView()
path = '...'
conn = sqlite3.connect(path,check_same_thread=False)
cursor = conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables = cursor.fetchall()
lst = []
for table in tables:
    table_name = table[0]
    cursor.execute("PRAGMA table_info("+table_name+")")
    columns = cursor.fetchall()
    # ex: [(index, 'name', '', 0, None, 0),...]
    t = ''
    for column in columns:
        column_name = column[1]
        t = t + column_name + ','
    #cursor.execute("SELECT "+t[:-1]+" FROM " + table_name)
    cursor.execute("SELECT * FROM " + table_name)
    rows = cursor.fetchall()
    t = table_name + ':' + t + ' [' + str(len(rows)) + ' rows]'         
    for row in rows:
        lst.append(row) # (field,field,...)
conn.close()
tv.data_source = ui.ListDataSource(items=lst)
tv.present('fullscreen') 

😂😂😂😂

@sendog3c sorry, I understand that you smile but is that what you wanted or, one more time, I didn't understand correctly the request...

I am going to use it. If there are doubts (which is certainly occurring now) I will replay you. Thank you very much

Now I am looking the path to exchange it in the code given. I downloaded a app called SQLed to create the database. Now I have to find out where It is located in my phone. I am coding in my iPhone.

@sendog3c Do you see the dB in the Files app?

Yes I do. The app has binder Icon? If it is positive, I can see the DBA file I have created in SQLED. That is what are you referring?

@sendog3c Yes, to be sure you could open the file in Pythonista as a path.

Yes I have opened it and I am seeing the icon and the name of the SQLite file. Named “voluntariado”.

@sendog3c just to be sure that I understand, how did you open it? Via open external files?

How do I do to obtains the file path? Thanks

@cvp correct, via external file

@sendog3c do you know how to get the file path for use in my script?

@cvp said:

@sendog3c do you know how to get the file path for use in my script?
Do not know Sir.

@sendog3c just to be sure you get the correct path of your .db file when it is defined as an external file,
1) create this little script as a Pythonista tool

import editor

def main():
    print(editor.get_path())    

if __name__ == '__main__':
    main() 

2) édit your .db file in a Pythonista tab and execute the tool

Ready:

/private/var/mobile/Library/Mobile Documents/com~apple~CloudDocs/Voluntariado/voluntariado.sqlite

I copied the path and run the script. It is working. But is repeating the records. My table only have 3 records.

@sendog3c sorry error of indentation (script also updated in first post)

lst = []
for table in tables:
    table_name = table[0]
    print(table_name)
    cursor.execute("PRAGMA table_info("+table_name+")")
    columns = cursor.fetchall()
    # ex: [(index, 'name', '', 0, None, 0),...]
    t = ''
    for column in columns:
        column_name = column[1]
        print(column_name)
        t = t + column_name + ','
        #cursor.execute("SELECT "+t[:-1]+" FROM " + table_name)
    cursor.execute("SELECT * FROM " + table_name)
    rows = cursor.fetchall()
    t = table_name + ':' + t + ' [' + str(len(rows)) + ' rows]'         
    for row in rows:
       lst.append(row) # (field,field,...) 

Thank you very much. Now I am going to see how your code fit into the tableview methods

@sendog3c 👍