[Tutor] Good Taste Question: Using SQLite3 in Python

[Alan Gauld]

On 29/04/15 04:06, Jugurtha Hadjar wrote:

> - Initially, each method had its SQL statement(s) inside, but I grouped
> all statements in a dictionary, with operations as keys, as a class
> 'constant' as per previous advice on this mailing list.

We can't see that in the code you posted.
In principle its an acceptable strategy. A close alternative
would be to name the queries as explicit class variables.
So for example you would use:

cur.execute(self.find_phone_query,(....))

Its less typing and less error prone in that you get a
syntax error on mistyping rather than a run time exception.

> - Each method used sqlite3 module on its own, but it was repetitive so I
> put that part in its own method `init_db` that returns a tuple
> consisting in a connection and a cursor.

That's a common approach.

> - Sometimes there was an exception raised, so I used `try` in `init_db`.
>
> - Methods closed the connection themselves, so I used `with` in
> `init_db` instead of `try`, as it would close the connection
> automatically and rollback (I hope I'm not making this up).

Try/except and with do different things but in this case
you can get away with it. The only loss is that your errors
here are not formatted in the same way as the other messages.

> Here's the excerpt


>      def __init__(self, phone):
>
>          # Get preliminary information on user and make them
>          # available.

Consider using docstrings rather than comments to describe the method
I see you do that below...

>          self.phone = phone
>          self.known = self.find()
>
>          if self.known:
>              self.balance = self.get_balance()
>          else:
>              self.balance = None
>
>      def init_db(self):
>          with sqlite3.connect(self.DB_FILE) as conn:
>              return conn, conn.cursor()
>
>      def find(self):
>          '''Find the phone in the users database.'''
>
>          (__, cursor) = self.init_db()

Don't use the __ 'variable' here, be explicit, it makes
maintenance much easier.

>          try:
>              cursor.execute(
>                  self.QUERIES['FIND_PHONE'],
>                  (self.phone,)
>              )
>              found = cursor.fetchone()
>              return True if found else False
>          except Exception as e:
>              return self.ERROR.format(e.args[0])

Don't catch Exception, it's too wide. Catch the
actual errors that might arise, be as specific as possible.

>      def create(self, seed_balance):
>          ''' Create a database entry for the sender.'''
>
>          conn, cursor = self.init_db()
>          try:
>              cursor.execute(
>                  self.QUERIES['CREATE'],
>                  (self.phone, seed_balance)
>              )
>              conn.commit()
>          except Exception as e:
>              return self.ERROR.format(e.args[0])


as above


-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos