CODEWITHSUNDEEP
pythonsql
Tony
Tony

Reputation: 764

Where is best to declare database connection in python

I'm new to python and have questions about database connections - do I need to declare the connection outside of the class in the main body and in every function, or I can make the connection just inside the functions I need to use it with?

When I tried the second option I experienced a slow performance when using the functions.

My code is like this:

root = Tk()

conection = sql connection
cursor = connection cursor

database = database(root) #  class with all the functions for database management

connection.commit()
connection.close()

root.mainloop()

and in the database class I have functions like:

def select(self):
  query = '''select sql query'''
  conection = sql connection
  cursor = connection cursor

  cursor.execute(query)

  connection.commit()
  connection.close()

Upvotes: 1

Views: 1003

Answers (1)

ghchoi
ghchoi

Reputation: 5156

Inside a class, in my opinion. You would better follow Data Access Object (DAO) pattern. For example,

class DB:
    def __del__(self):
        self.connection.close()

    def __init__(self):
        self.connection = sqlite3.connect()

    def select(self):
        cursor = self.connection.execute(query)
        ...
        self.connection.commit()

In this way, you can provide functions related to the database with automatically established and closed connection.

Upvotes: 1

Related Questions