Welcome!
This is the community forum for my apps Pythonista and Editorial.
For individual support questions, you can also send an email. If you have a very short question or just want to say hello — I'm @olemoritz on Twitter.
help loading data (sqlite) into tableview
-
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?
-
-
@sendog3c just to be sure you get the correct path of your .db file when it is defined as an external file,
- create this little script as a Pythonista tool
import editor def main(): print(editor.get_path()) if __name__ == '__main__': main()
- é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