help loading data (sqlite) into tableview

sendog3c11

Hi:

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

cvp

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

😂😂😂😂

cvp

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

sendog3c

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

sendog3c

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.

cvp

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

sendog3c

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?

cvp

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

sendog3c

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

cvp

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

sendog3c

How do I do to obtains the file path? Thanks

sendog3c

@cvp correct, via external file

cvp

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

sendog3c

@cvp said:

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

cvp

@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

sendog3c

Ready:

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

sendog3c

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

cvp

@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,...)

sendog3c

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