omz:forum

    • Register
    • Login
    • Search
    • Recent
    • Popular

    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

    Pythonista
    data&tableview
    3
    21
    6382
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • sendog3c11
      sendog3c11 last edited by

      Hi:

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

      cvp 1 Reply Last reply Reply Quote 0
      • cvp
        cvp @sendog3c11 last edited by cvp

        @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') 
        
        1 Reply Last reply Reply Quote 0
        • sendog3c
          sendog3c last edited by

          😂😂😂😂

          cvp 1 Reply Last reply Reply Quote 0
          • cvp
            cvp @sendog3c last edited by

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

            1 Reply Last reply Reply Quote 0
            • sendog3c
              sendog3c last edited by

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

              1 Reply Last reply Reply Quote 0
              • sendog3c
                sendog3c last edited by

                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 1 Reply Last reply Reply Quote 0
                • cvp
                  cvp @sendog3c last edited by

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

                  1 Reply Last reply Reply Quote 0
                  • sendog3c
                    sendog3c last edited by

                    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 1 Reply Last reply Reply Quote 0
                    • cvp
                      cvp @sendog3c last edited by

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

                      1 Reply Last reply Reply Quote 0
                      • sendog3c
                        sendog3c last edited by

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

                        cvp 1 Reply Last reply Reply Quote 0
                        • cvp
                          cvp @sendog3c last edited by cvp

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

                          sendog3c 1 Reply Last reply Reply Quote 0
                          • sendog3c
                            sendog3c last edited by

                            How do I do to obtains the file path? Thanks

                            1 Reply Last reply Reply Quote 0
                            • sendog3c
                              sendog3c @cvp last edited by

                              @cvp correct, via external file

                              cvp 1 Reply Last reply Reply Quote 0
                              • cvp
                                cvp @sendog3c last edited by

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

                                sendog3c 1 Reply Last reply Reply Quote 0
                                • sendog3c
                                  sendog3c @cvp last edited by

                                  @cvp said:

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

                                  cvp 1 Reply Last reply Reply Quote 0
                                  • cvp
                                    cvp @sendog3c last edited by cvp

                                    @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() 
                                    
                                    1. édit your .db file in a Pythonista tab and execute the tool

                                    1 Reply Last reply Reply Quote 0
                                    • sendog3c
                                      sendog3c last edited by

                                      Ready:

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

                                      1 Reply Last reply Reply Quote 0
                                      • sendog3c
                                        sendog3c last edited by

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

                                        cvp 1 Reply Last reply Reply Quote 0
                                        • cvp
                                          cvp @sendog3c last edited by 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,...) 
                                          
                                          1 Reply Last reply Reply Quote 0
                                          • sendog3c
                                            sendog3c last edited by

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

                                            cvp 1 Reply Last reply Reply Quote 0
                                            • First post
                                              Last post
                                            Powered by NodeBB Forums | Contributors