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.


    [SHARE CODE] PRAGMA query for sqlite

    Pythonista
    pragma sqlite share
    4
    32
    21648
    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.
    • ccc
      ccc last edited by ccc

      print('''
      {pad} Orderdict PRAGMA settings {pad}
      {}
      
      
      {pad} __str__ Version {pad}
      {}
      
      
      {pad} PRAGMA HELP LINKS {pad}'
      {}
      '''.format(obj[0], sqllite_pragmas, sqllite_pragmas.print_pragma_help_links(),
                 pad = '#' * 5))
      
      Phuket2 2 Replies Last reply Reply Quote 1
      • Phuket2
        Phuket2 @ccc last edited by

        @ccc , thanks . Looks a lot better and more controllable.
        Pretty is better than ugly 😍

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

          Oh, my Buddha!!!!!
          I made a huge rookie mistake... I couldn't understand why I was having so many problems.
          I some how thought 3 tick mark comments were comments in my dicts and lists. I have no idea why I was thinking like this. maybe I was a little to drunk to be programming then. Oh well.

          I can believe how stupid I have been. I was so proud of myself documenting everything better than I normally do....
          I can't even believe it complied let alone seemed to work....

          So once again fixing it. Will repost a gist for anyone who is remotely interested later.

          Embarrassing, but we all have to start somewhere I guess.

          1 Reply Last reply Reply Quote 0
          • Phuket2
            Phuket2 @ccc last edited by

            @ccc , again thanks for your previous comment. I also changed how the pragma_help_links work. Rather than print to the console inside the class method (which was a stupid idea in the first place), I return a list of tuples using list comp.
            until you really get comfortable using these patterns it's easy to over look their power and the ability to solve problems using expressions or call methods or functions from inside the pattern.
            But the more I get used to them the more I love them!

            def pragma_help_links(self):
            	return [(k, pragma_messages[v], 
            	_web_help_link_adjustments.get(k, None) )
            	for k,v in _pragmas.items() ]
            
            1 Reply Last reply Reply Quote 0
            • ccc
              ccc last edited by

              try:
                  conn = sqlite3.connect(self.db_filename)
              except sqlite3.Error as err:
                  return (None, err)
              

              I can not find any way to get sqlite3.connect() to raise a sqlite3.Error.

              Phuket2 2 Replies Last reply Reply Quote 0
              • Phuket2
                Phuket2 @ccc last edited by

                @ccc , also I can not find away.
                In the standard libraries- sqlite3-test folder, the file dbapi.py has unit tests. But they don't fail. I tried this as in there unit tests.

                    def CheckFailedOpen(self):
                        YOU_CANNOT_OPEN_THIS = "/foo/bar/bla/23534/mydb.db"
                        try:
                            con = sqlite.connect(YOU_CANNOT_OPEN_THIS)
                        except sqlite.OperationalError:
                            return
                        self.fail("should have raised an OperationalError")
                

                But it does not fail. God knows we're the database ends up

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

                  I don't understand enough if this is a problem or not, but CheckExecuteArgStringWithZeroByte fails the unit test (sqlite3, dbapi.py). Is the only failure out of 79 tests.
                  @omz, I am not if this is a Pythonista problem or not

                  1 Reply Last reply Reply Quote 0
                  • Phuket2
                    Phuket2 @ccc last edited by

                    @ccc , sorry I am wrong again. This funky file name, does get conn.connect() to generate an error.
                    YOU_CANNOT_OPEN_THIS = "/foo/bar/bla/23534/mydb.db"

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

                      OK... So, do not catch the error in sqllite_pragmas.get_pragmas(). Instead, just catch it in your main code...

                          try:
                              obj = sqllite_pragmas.get_pragmas()
                          except sqlite3.Error as err:
                              exit('Program can not continue: {}'.format(err))
                      
                      Phuket2 1 Reply Last reply Reply Quote 0
                      • Phuket2
                        Phuket2 @ccc last edited by

                        @ccc , thanks very nice. Because I haven't finished many projects, I don't understand the the error system yet. But yes, I can see trapping the error in .get_params() will cause me problems when trying to call it from str or from other methods in the class.

                        I am sure there is more to learn, with raise error etc... And I will have understand how errors propagate up the calling stack. But later ;)

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

                          Btw, I am still working on this. I haven't published a new gist because as usual, I am changing everything 😭 (Also have had many visitors)
                          As I wanted to have more static data associated with each PRAGMA, my approach to the data representation fell apart.

                          And it's more than likely I am going down the wrong path again or fingers crossed, possibly not.

                          I have decided on a list of namedtuples to define the PRAMA's with thier static data.

                          Will look something like this ;

                          pragma_recs = [
                          	_prec(cmd='application_id', query=True, call=True, ui_type='number', msg=None, unused=None),
                          
                          	_prec(cmd='auto_vacuum', query=True, call=True, ui_type='number', msg=None, unused=None),
                          ]
                          

                          Will be a list item for each PRAGMA. Maybe I am wrong.
                          I could do this as a normal list or just a normal set of tuples but there are 63 records. To go back and edit these records or review them later would be frustrating and error prone , this seems to be the best solution I can find.

                          From list of the namedtuple records, I will have to decide how to deal with them, as namedtuple's, dicts etc...

                          Anyway, still working on it

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

                            Was hoping to get some Guru advice here. The script I list below, I include into my file that has my PRAGMA class in it.

                            It appears a little wasteful. But I am going for clarity and maintain ability over the small hit on speed. After screwing around a lot with classes and dicts of dicts , lists etc... This seems good to me.

                            But if you are inclined to comment. Please give it to me between the eyes. If you think it's crap, please let me know. No sugar coating required. I really want to do it correctly.

                            # coding: utf-8
                            
                            from collections import namedtuple
                            _prec_flds = ['cmd', 'attr', 'query', 
                            						'call', 'ui_type', 'msg']
                            						
                            _prec = namedtuple('_prec', _prec_flds )
                            
                            # 63 + records in pragma_static_data (sample here)
                            # i find defining the static data as namedtuples. if i need
                            # to edit by hand of just review the data, its very clear
                            # also, if i want to add another field, i have a script i can
                            # just that will generate the pragma_static_data again
                            # with the new field.
                            
                            pragma_static_data = [
                            	_prec(cmd='application_id', attr=None, 
                            			query=True, call=True, ui_type='number', msg=None),
                            			
                            	_prec(cmd='auto_vacuum', attr=None,
                            			query=True, call=True, ui_type='number', msg=None),
                            ]
                            
                            # creates a list of ordered dicts, containing the data
                            # from pragma_static_data
                            _params_dict = [ nt._asdict() for nt in pragma_static_data]
                            
                            # dont want to use the named_tuples in the class
                            # mainly because of the difficultly updating flds
                            # in place.
                            del pragma_static_data
                            
                            # make sure its working
                            print _params_dict
                            
                            1 Reply Last reply Reply Quote 0
                            • ccc
                              ccc last edited by ccc

                              You can write:

                              _prec_flds = 'cmd attr query call ui_type msg'
                              

                              namedtuple.__init__() will do a str.split() on the second parameter if it is a str. This helps a lot with readability.

                              You could write:

                              pragma_static_data = [_prec(cmd=cmd, attr=None,  query=True, call=True,
                                  ui_type='number', msg=None) for cmd in ('application_id', 'auto_vacuum')]
                              

                              _params_dict is a list of dicts, not a dict ;-( Perhaps add an 's' on the end of the variable name to make that clear.

                              Phuket2 1 Reply Last reply Reply Quote 0
                              • Phuket2
                                Phuket2 @ccc last edited by Phuket2

                                @ccc , I see your point about the creating the namedtuple's the way you did it. Also a nice way. But I just picked the first 2 records that just happen to have the fields other than cmd being the same. There are 63 records and the fields all differ, so that pattern you suggest I can not use in this case. Of course I can use you fields suggestion.

                                I agree with the naming of params_dict, was a last min tack on. Should be pragma also. Like pragma_dicts or pragma_dict_list

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

                                  Hi guys I know this is a little bit off topic, but I need some help and at the very least you guys discuss sqlite in this thread. Maybe I'm too shy to start a new thread...

                                  So I'm trying to use sqlite3 with a local database file to store values that have to do with whether it is being run for the first time etc. I want to initialize it with certain values that later, when users run the app will change.

                                  Its my understanding that doing sqlite3.connect('path/to/file/blablabla.db") will create a db where I'd like. How do I get the relative path to the main scripts folder of my app in the Xcode template? I'd like to save the .db file along side my scripts and then access it from other instances of my app, and also be able to peek into it during development. Thank you all. :)

                                  Phuket2 1 Reply Last reply Reply Quote 0
                                  • Phuket2
                                    Phuket2 @Tizzy last edited by ccc

                                    @Tizzy l sorry I can not help. I have never worked with Xcode. So I have no idea were your scripts are being saved. But I am assuming you would be using some relative path from your script director. But I am just guessing. But yes the connect() will make the database
                                    Below is a bit of code I got from stackflow you might find useful later.

                                    def isSQLite3(filename):
                                    	'''
                                    		try and determine if the filename is a valid sqlite3 database. if the filename does not exist, still returns False. 
                                    		
                                    		copied this code from stackflow
                                    	'''
                                    
                                    	if not os.path.isfile(filename):
                                    		return False
                                    	if os.path.getsize(filename) < 100: 
                                    		# SQLite database file header is 100 bytes
                                    		return False
                                    
                                    	with open(filename, 'rb') as fd:
                                    		header = fd.read(100)
                                    	
                                    	return header[:16] == 'SQLite format 3\x00'
                                    
                                    1 Reply Last reply Reply Quote 0
                                    • Tizzy
                                      Tizzy last edited by

                                      that looks like it could be useful. However I'm having problems creating a db file where I specify the path. here's what I've tried.

                                      import sqlite3
                                      import os
                                      
                                      dir = os.path.dirname(__file__)
                                      filename = os.path.join(dir,'/sillllly.db')
                                      conn = sqlite3.connect(filename)
                                      
                                      
                                      

                                      if you don't specify a path the database is created simply by making the call to .connect, but it doesn't seem to with this relative path.

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

                                        Do you get an error / exception or does it fail silently.

                                        You might not have write access in an XCode created app. Can you open('junk.txt', 'w') in an XCode app?

                                        A few things to try out:

                                        import os
                                        print('\n'.join(func(__file__) for func in (str, os.path.abspath, os.path.normcase, os.path.realpath)))
                                        print(os.path.relpath(__file__, os.curdir))
                                        
                                        1 Reply Last reply Reply Quote 1
                                        • Tizzy
                                          Tizzy last edited by Tizzy

                                          it was just saying could not create database...but I understand why now it was a failure of my path string- for some reason filename = os.path.join(dir,'/sillllly.db') was just returning '/sillllly.db.' instead of

                                          
                                          /Users/username/Library/Developer/CoreSimulator/Devices/randomalphanumericstrings/data/containers/Data/Application/more-alphan-numeric-mumbo-jumbo/Documents/sillllly.db
                                          
                                          
                                          

                                          ...Now I got it to work using

                                          diry = os.path.dirname(__file__)
                                          combinedViaString = diry+"/sillllly.db"
                                          
                                          

                                          and plugging that into connect()

                                          Now, the above works, and the database says it's been created, and i can even create a table, however I can't see the database file in the main directory where my script is actually located. I guess this "/documents" area is some sort of temporary directory during runtime since that's not the actual directory I see in the Xcode file structure? or is it inaccessible to me?

                                          And that really is my goal - to create a database file which i can see and have full control over in the file structure.

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

                                            @Tizzy os.path.join treats both arguments as full paths. If the second one has a slash at the beginning, it is considered an absolute path (relative to the root directory /), and the first path is effectively ignored. If you want to join two paths together, the second one must be a relative path. For example os.path.join("/usr/bin", "python") returns "/usr/bin/python", but os.path.join("/usr/bin", "/python") returns "/python".

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