Регистрация и отслеживание запасов товара


Вам: Запрос К Базе Данных

Вам легкий инвентаризации трекера, написанный на Python

Вся бизнес-логика выполняется с помощью 4-х классов:

  • Базы данных - запросы файл базы данных sqlite3
  • Excelwb - создает и обновляет книгу Excel для отображения информации о продукте
  • Продукт - проводит индивидуальные информация о продукте, а также методы, чтобы проверить их в/из
  • Пользователя содержит информацию пользователя, использует все другие классы
  • Лог - запись информации в текстовый файл

Это содержание 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()

Большинство методов структурированы так же, есть ли лучший способ добиться этого? Любая критика приветствуется.



407
3
задан 12 марта 2018 в 05:03 Источник Поделиться
Комментарии