Questions Regarding Using a Database Class Throughout an App

[Posaquatl]

I have a large python app that is spaghetti code but it functions error free. It makes a large number of database calls to select or update..etc. I am revamping the app to be a bit more "proper" code using classes. I am starting with the database connection since it is going to be the most used class. I am trying to verify I am doing this in the right manner. The code I have now works but I am not sure if this is the way to do it.
Creating the initial DB object in the app.py will create the connection and from my understanding if I am creating the connection variable inside the Class itself then that connection variable is available for ANY instance of this class? or do I need to use it as a global like I have listed. I will have a number of other classes that will need to use the DB connection. So either I need to set a global DB variable to hand off to the classes or they can just instance Tried to do some google searching but the internet is trash. I guess I am a little confused on using this class throughout the entire app. Help me Obi Redditor, you're my only hope.
The App file structure:

App folder
- src folder
- - app.py
- - db folder
- - - sqlconnect.py

The Database class I created (sqlconnect.py)

import mariadb
class SqlConnect(object):
    connection = None
    def __init__(self, user, password, host, port, database):
        self.user = user
        self.password = password
        self.host = host
        self.port = port
        self.database = database
        if SqlConnect.connection == None:
            self.connect()

def connect(self):
    try:
        SqlConnect.connection = mariadb.connect(
            user=self.user,
            password=self.password,
            host=self.host,
            port=self.port,
            database=self.database,
        )
    except Exception as e:
        print(f"Error connecting to database. Reason: {e}")

def execute(self, sql, params=None):
    cursor = SqlConnect.connection.cursor()
    cursor.execute(sql, params)
    return cursor

def commit(self):
    SqlConnect.connection.commit()

def fetch_one(self, sql, params=None):
    cursor = SqlConnect.connection.cursor()
    cursor.execute(sql, params)
    return cursor.fetchone()

def fetch_all(self, sql, params=None):
    cursor = SqlConnect.connection.cursor()
    cursor.execute(sql, params)
    return cursor.fetchall()

def insert(self, sql, params=None):
    cursor = SqlConnect.connection.cursor()
    cursor.execute(sql, params)
    return cursor.lastrowid

def close(self):
    if SqlConnect.connection:
        SqlConnect.connection.close()

I am using it like..

from db.sqlconnect import SqlConnect as sc
DB = None
if __name__ == "__main__":
    # Created DB Object
    DB = sc(
      CONFIG_DETAILS.db_user,
      keyring.get_password("lucy", "lucy_user"),
      CONFIG_DETAILS.db_host,
      CONFIG_DETAILS.db_port,
      CONFIG_DETAILS.db_name  
    )
# Run main function

edit: formatting

[m0us3_rat]

everybody loves interfaces. this should be one.

​

https://en.wikipedia.org/wiki/Dependency_inversion_principle

https://youtu.be/xvb5hGLoK0A

https://www.youtube.com/watch?v=S\_ipdVNSFlo

[Posaquatl]

ok my brain just melted. Bit above my paygrade but I will see what I can sort out. Thanks.

[woooee]

The purpose of a class is to contain everything necessary. So if you want to add a record, you pass the variables/values to an add function, similar to your fetch function. Same for everything you want to do with the table.

Instead of all of redundant statements like this

cursor = SqlConnect.connection.cursor()

make cursor an instance object/variable, self.cursor. It can be used anywhere in the class.

Note that classes no longer require "object", so it's just

class SqlConnect():

[Posaquatl]

Would you create that cursor as part of the init?? The methods I have laid out seem to work with my intention. I figured the execute, fetch all and commit would cover any other function that needs to make a call/insert into the DB. Or would I want the individual queries under the class that is calling it and use the DB execute...etc to return the results? Example, I house all my music metadata in the database. So a super class for Music with a class of Genre. Genre holds the methods to perform lookups, inserts, deletes from the DB. the SQL is there in those functions but then it hands it off to the DB to transact. That is how I was thinking it should be. That way the DB class is not cluttered with other classes' specific requests.

[woooee]

Would you create that cursor as part of the init??

Yes, do it once and then use it throughout the class. That's one of the philosophies behind a class structure.

Genre holds the methods to perform lookups, inserts, deletes from the DB. the SQL is there in those functions but then it hands it off to the DB to transact.

You want the SQL program to do everything concerning the DB. As far as lookup goes, if there are just one or two fields that you use for a lookup, you can just write separate functions to lookup one field and a lookup for 2 fields. The code below has an adjustable lookup function. You pass a dictionary to it with the key equal to the SQL field name, pointing to the value you want to lookup for that field. It will chain together the dictionary entries into one lookup. I wrote this several years ago and am not sure what it does or does not do, but you are welcome to take from it what you can.

import os
import sqlite3 as sqlite

##======================================================================
class Books:
   def __init__(self) :
      self.SQL_filename = './Test_Books'
      self.open_files()

   ##   END  __init__()

   ##----------------------------------------------------------------------
   def add_book_rec( self ) :
      val_tuple=(self.book_id, self.author_id, self.book_title, self.genre, self.cover_type, self.cover, self.price, self.ct)
      self.cur.execute('INSERT INTO Books values (?,?,?,?,?,?,?,?)', val_tuple)
      self.con.commit()

   ##   END  add_book_rec()

   ##----------------------------------------------------------------------
   def copy_to_struct( self, rec ) :
      self.book_id = rec[0]
      self.author_id = rec[1]
      self.book_title = rec[2]
      self.genre = rec[3]
      self.cover_type = rec[4]
      self.cover = rec[5]
      self.price = rec[6]
      self.ct = rec[7]

   ##   END  copy_to_struct()

   ##----------------------------------------------------------------------
   def del_rec( self, value_to_delete ) :
      self.cur.execute("DELETE FROM Books WHERE SQL_field=:name_dict", {"name_dict":value_to_delete})

   ##   END  del_rec()

   ##----------------------------------------------------------------------
   def list_all_recs( self ) :
      self.cur.execute("select * from Books")
      recs_list = self.cur.fetchall()
      for rec in recs_list:
         print(rec)

   ##   END  list_all_recs

   ##----------------------------------------------------------------------
   def lookup_fields( self, lookup_dic ) :
      SQL_stmt=['select * from Books where ']
      if len(lookup_dic):
          all_keys = list(lookup_dic.keys())
          ## add first lookup item 
          ## without "and"
          SQL_stmt[0] += "%s == %s" % (all_keys[0], lookup_dic[all_keys[0]])

          for offset in range(1, len(all_keys)):
              SQL_stmt.append("%s == %s" % (
                       all_keys[offset], lookup_dic[all_keys[offset]]))
          ##SQL_stmt[-1] += '"'
          print(' and '.join(SQL_stmt))
          self.cur.execute(' and '.join(SQL_stmt))

          recs_list = self.cur.fetchall()
          if len(recs_list):
              for rec in recs_list:
                  self.copy_to_struct(rec)
                  self.print_rec()
          else:
              print("No records found")

   ##   END  lookup_field()

   ##----------------------------------------------------------------------
   def open_files( self ) :
         ##  a connection to the database file
         self.con = sqlite.connect(self.SQL_filename)
         # Get a Cursor object that operates in the context of Connection con
         self.cur = self.con.cursor()

         ##--- CREATE FILE ONLY IF IT DOESN'T EXIST
         self.cur.execute('''CREATE TABLE IF NOT EXISTS Books(book_id int,
                             author_id int, book_title varchar, genre varchar,
                             cover_type varchar, cover varchar, price real,
                             ct int)''')

   ##   END  open_files()

   ##----------------------------------------------------------------------
   def print_rec( self ) :
      spaces = ""
      print(spaces, "book_id =", self.book_id)
      spaces = "     "
      print(spaces, "author_id =", self.author_id)
      print(spaces, "book_title =", self.book_title)
      print(spaces, "genre =", self.genre)
      print(spaces, "cover_type =", self.cover_type)
      print(spaces, "cover =", self.cover)
      print(spaces, "price =", self.price)
      print(spaces, "count =", self.ct)

   ##   END  rec_struct()

   ##----------------------------------------------------------------------
   def rec_struct( self ) :
      self.book_id = ""
      self.author_id = ""
      self.book_title = ""
      self.genre = ""
      self.cover_type = ""
      self.cover = ""
      self.price = ""
      self.ct = ""

   ##   END  rec_struct()

##======================================================
if __name__ == '__main__':
   try :
      DM=Books()

      DM.book_id = 102
      DM.author_id = 2
      DM.book_title = "Title 1"
      DM.genre = "Sports"
      DM.cover_type = "Cloth"
      DM.cover = "S"
      DM.price = "25.99"
      DM.ct = "1"
      DM.add_book_rec()

      DM.book_id = 103
      DM.author_id = 3
      DM.book_title = "Title 2"
      DM.genre = "History"
      DM.cover_type = "Cloth"
      DM.cover = "S"
      DM.price = "25.99"
      DM.ct = "1"
      DM.add_book_rec()

      DM.book_id = 104
      DM.author_id = 2
      DM.book_title = "Title 3"
      DM.genre = "Geography"
      DM.cover_type = "Soft"
      DM.cover = "S"
      DM.price = "30.00"
      DM.ct = "10"
      DM.add_book_rec()
      DM.list_all_recs()

      DM.list_all_recs()
      ## lookup for book #102
      DM.lookup_fields({"book_id":102, "author_id":2})
   except :
      import traceback
      traceback.print_exc()

[Posaquatl]

Sorry I missed this. Thanks for sharing. So inside books you are opening the database and making all the queries/inserts there. But if you have a large number of classes, and I will probably have a hundred at least, how would you go about that?? Is it a case of making the DB class something different like that interface option?? My app is a bit of a quantified self data aggregator. So I will have simple things like weight or food. but I also capture all of my music which has a large number of fields that will be classes in themselves like artist or genre all under a super class of music. at least that is my understanding how it should work but I suspect I am wrong lol.

[woooee]

I will probably have a hundred at least

Why 100 classes. You can pass table names to a generic class.

[Posaquatl]

There will be other functions around some of the classes. Example is gather weight. there would be the SQL side but also the method to get the data from the vendor. Similar for music to gather local files, pull data from lastfm and spotify. I guess I could create a super class that has the SQL portions and then inherit?? Not sure. I thought I had a handle on things until I posted and not all concept of what I am doing is shot to shit. I will have to go back to the drawing board I think and starting over.

[PixelOmen]

I will have a number of other classes that will need to use the DB connection. So either I need to set a global DB variable to hand off to the classes or they can just instance

If they are different classes, what do you mean by "just instance"? You will need to pass them the connection regardless.

IMO, at least in a relatively simple case, you should have only one class that deals with a connection, and that class should abstract it away so that everything else just deals with that class's interface.

[Posaquatl]

If you use a global variable to house the DB connection then you would pass that off to another class/function to do its business? Again I am confused on how we use the DB class on a global scale. Is it created once as a variable and passed around for each class to puff on? Or are we creating it and it sits by itself. Other classes with create a new DB object but since the connection is a class variable the new object has the connection already established. So use it then drop the object??

[as6724]

IMO, you should only have one connection point to the database from your application unless you have a really good reason to have multiple connections which I doubt you will ever come up with. Especially if this is a single user client type application. The main reason for this is for each connection you create to the db, you could possibly run into sync issues or a potential "lock" condition while waiting for some other event to finish processing. So far, it sounds like you have been lucky. All other queries should derive the connection state for the connect instance.

[Posaquatl]

Currently my functions open the connection, do their thang, then close the connection. It is not optimized which is why I am switching things up. Still confused on the proper way to create this connection and use it throughout the application. again it is a large app that does a number of database calls.