1

I have been trying to find an answer to this question all night and I still haven't come across a definite answer. Normally when I work with sqlite3 i would use the following pattern:

import sqlite3

db = sqlite3.connect('whatever.db')
cursor = db.cursor()

#do some stuff

cursor.close()

Now I trying to evolve my understanding of OOP and databases so I thought I would create a controller object to interact with the database. I have come up with the following:

A class which just defines the connection and cursor:

import sqlite3

class coffee_shop_controller:

    def __init__(self):
        self.db = sqlite3.connect("coffeeshop.db")
        self.cursor = self.db.cursor()

    def close(self):
        self.cursor.close()

I subclass this for the various controllers that I need. For example:

class customer_controller(coffee_shop_controller):

    """creates a controller to add/delete/amend customer records in the
       coffee shop database"""

    def __init__(self):
        super().__init__()

    def add_customer(self,fn,ln,sa,t,pc,tn):
        sql = """insert into customer
                 (first_name,last_name,street_address,town,post_code,telephone_number)
                 values
                 ('{0}','{1}','{2}','{3}','{4}','{5}')""".format(fn,ln,sa,t,pc,tn)
        self.cursor.execute(sql)
        self.db.commit()

I appreciate that the design pattern may not be great (open to suggestions) and that I really should be preventing SQL injection but the closing the connection is interesting me at the moment.

From searching around the Python docs the comment line suggests that we can close the connection, not that we must. Is this right? Do I not really need to bother?

If I do need to bother then there seems to be a split on what I should do:

Is there anything definitive here? The __del__ method makes the most sense to me but maybe that's my ignorance talking.

Thanks for any suggestions you can offer.

Adam.

Community
  • 1
  • 1
Adam McNicol
  • 405
  • 1
  • 4
  • 15

1 Answers1

0

It's a good practice to free the resources that you do not need anymore. Normally, database connections are pretty "expensive" and I would definitely recommend to open the connection, do the actual query and close the connection right after that.

In order to achieve a better control over this, I would also recommend to follow the Unit Of Work design pattern. Moreover, it will be great for you if you can combine Unit Of Work with some good ORM (such as SQLAlchemy or Django)

Mikhail
  • 898
  • 7
  • 21