3
\$\begingroup\$

KeepStock: Database Query

KeepStock is a lightweight inventory tracker written in python

All business logic is done via 4 classes:

  • Database- Queries sqlite3 database file
  • Excelwb- Creates and updates an excel workbook to display product information
  • Product- Holds individual product information, as well as methods to check them in/out
  • User- Holds user information, utilizes all other classes
  • Log- Records information to text file

This is the contents of Database.py

'''
---------------------------------------------------
Database class declaration: this class is inherited
by Product and User, but can also be used on it's 
own.
---------------------------------------------------
'''
import sqlite3

class Database(object):
    """
    Connects to the database file
    """
    def __init__(self, db="Item Data.db"):
        self.conn = sqlite3.connect(db) # creates connection
        self.c = self.conn.cursor() # creates cursor
        self.mk_table("Product_Data", "id text primary key, name text not null, stock int")
        self.mk_table("User_Data", "id text key, name text, po text, user text key, amount int")
    """
    User products with inventory count of 0
    No longer needed in database
    """
    def __del__(self):
        for product in self.get_allProducts("User_Data"):
            if not product[4]:
                self.delete_product("User_Data", id=product[0], po=product[2], user=product[3])
        self.conn.commit()
    """
    Formats an sqlite3 command depending on how many parameters it wants.
    Exact
    """
    def __create_command(self, command, *args):
        count=0
        for param in args:
            command +=" "+param+"=?" # param is a string denoting column for bind
            count+=1
            try:
                args[count] #index error on last loop
                command += " AND" # Last param wont have a trailing AND
            except:
                pass
        return command
    """
    Formats an sqlite3 command depending on how many parameters it wants.
    Similar
    """
    def __create_like_command(self, command, *args):        
        count=0
        for param in args:
            command +=" "+param+" like ?" # param is a string denoting column for bind
            count+=1
            try:
                args[count] #index error on last loop
                command += " AND" # Last param wont have a trailing AND
            except:
                pass
        return command
    """
    Creates an sqlite3 table in databse
    """
    def mk_table(self, table, command):
        self.c.execute("CREATE TABLE IF NOT EXISTS %s (%s)" % (table, command)) #! command is a string that completes the query
        self.conn.commit()    
    """
    Adds given stock change to existing stock, allows for checking out/in a higher quantity
    """
    def update_stock(self, col_name, stock_change, table, **kwargs): #sets stock to n_stock
        command = self.__create_command("UPDATE %s SET %s = %s + %d WHERE", *list(kwargs.keys()))
        self.c.execute(command % (table, col_name, col_name, int(stock_change)), (*list(kwargs.values()),))
        self.conn.commit()
    """
    Adds new product to the database
    """
    def add_product(self, table, *args):
        command = "INSERT INTO %s VALUES ("
        count=0
        for value in args: #same technqiue as __create_command method
            command += '?'
            count+=1
            try:
                args[count]
                command += ','
            except:
                pass
        command += ')'
        self.c.execute(command % table, (*args,))
        self.conn.commit()
    """
    Deletes a product from database
    """
    def delete_product(self, table, **kwargs):
        command = self.__create_command("DELETE FROM %s WHERE", *list(kwargs.keys()))
        self.c.execute(command % table, (*list(kwargs.values()),))
        self.conn.commit()    
    """
    Returns a list of product information similar to query
    """
    def get_like_product(self, table, **kwargs): # example kwargs (name="product1", date="12-12-12", id="123")
        command = self.__create_like_command("SELECT * FROM %s WHERE", *list(kwargs.keys()))
        for x in kwargs:
            kwargs[x] = '%'+kwargs[x]+'%'
        self.c.execute(command % table, (*list(kwargs.values()),))#values as arguments ("product1", "12-12-12, "123")
        product = self.c.fetchall() #! setting it to a variable for utils 
        self.conn.commit()
        if not product:
            none_left = True
            for arg in kwargs:                
                kwargs[x] = kwargs[x][1:len(kwargs[x])-2]
                if len(kwargs[x]): none_left = False                
            if none_left: return None
            else:
                return self.get_like_product(table, **kwargs)
        return product    
    """
    Returns product or None
    """
    def get_product(self, table, **kwargs): # example kwargs (name="product1", date="12-12-12", id="123")
        command = self.__create_command("SELECT * FROM %s WHERE", *list(kwargs.keys()))
        self.c.execute(command % table, (*list(kwargs.values()),))#values as arguments ("product1", "12-12-12, "123")
        product = self.c.fetchone() #! setting it to a variable for utils 
        self.conn.commit()
        return product
    """
    returns a list of products or None
    """
    def get_products(self, table, **kwargs):
        command = self.__create_command("SELECT * FROM %s WHERE", *list(kwargs.keys()))
        self.c.execute(command % table, (*list(kwargs.values()),))#values as arguments ("product1", "12-12-12, "123")
        products = self.c.fetchall() #! setting it to a variable for utils 
        self.conn.commit()
        return products
    """
    Returns a two dimensional array of all products
    """
    def get_allProducts(self, table):
        self.c.execute("SELECT * FROM %s" % table)
        product = self.c.fetchall() #! setting it to a variably for utils 
        self.conn.commit()
        return product    
    """
    Returns column value 
    """
    def get_col(self, table, col):
        self.c.execute("SELECT %s FROM %s", (col, table,))
        products = self.c.fetchall() 
        self.conn.commit()
    """
    Updates cell value
    """
    def edit_table(self, table, col_name, n_value, **kwargs):
        command = self.__create_command("UPDATE %s SET %s = ? WHERE", *list(kwargs.keys()))
        self.c.execute(command % (table, col_name), (n_value, *list(kwargs.values()),))
        self.conn.commit()

Most methods are structured the same, is there better way to accomplish this? Any criticism is welcome.

\$\endgroup\$
2
\$\begingroup\$

I see quite a lot of repeating code, repeating the same activity: get request, build string, query string, return result. The Single Responsibility Principal can help to clarify your code here. As a suggestion, you might want to turn something like get_products (and similar) to:

def get_products(self, table, **kwargs):
    command = build_command_here
    return get_database_results(command)

having them all send a sql string into a single function. That way, you separate the database functionality from the business logic of each activity.
For create_command and create_like_command, three things. Personally, I don't use double underscores for functions, a single one is sufficient to advise other developers that the function is intended for private use. Secondly, the string building process, you should attempt to use list comprehension to build the strings rather than a loop, this will result in much simpler code (and no "last param won't have...").
Thirdly, both these functions are the same, and only differ in their use of a like or =. You should have both refer their details to another function with a parameter. Something like (not functional code, just a typed up demo for the concept):

def build_sql_string(self, command, args, is_type):
    placeholders = ", ".join(['%s'] * len(args))
    query = f"{command} {is_type} {placeholders})"
    return query

and both create_command and create_like_command would call build_sql_string(is_type) to get the string.

Hope this helps, good luck!

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.