Python Forum

Hello,

I have two SQLite databases that I want to compare (My 'inventory.db' and my 'categories.db').

So, my Inventory Database stores the Name, Quantity & Category(The user chooses from a list of categories provided by the Category Database) of an item, and my Category Database stores the Categories and Low_Quantity_Values for those categories.

What I'm looking to do is to take the Category, Quantity, & Name of an item from my 'inventory.db' and compare it to the Category & Low_Quantity_Value from the 'categories.db' so I can return/print what items from the 'inventory.db' are low in quantity.

So if the categories from both databases match and the quantity from the 'inventory.db' is lower than the Low_Quantity_Value from 'categories.db' then I would like the item name & quantity to be printed/returned.

Is there any way to do this?

Thanks in advance.


What I have now (FYI: MainDatabase = 'inventory.db' & CategoryDatabase = 'categories.db'):

#------------------------------------------------------------------------
#                     Low Quantity Alert
#------------------------------------------------------------------------
    def LowQuantityAlert(self):
    
        #Connect to the Category database
        connection = sqlite3.connect(CategoryDatabase)
        cursor = connection.cursor()
        #Get the low quantity values for each category
        cursor.execute('''
            SELECT Category, Low_Quantity_Value From Categories
            ''')
        connection.commit()
        LowQuantityResult = cursor.fetchall()
        print(LowQuantityResult)
        #Close the connection
        connection.close()

 
        #Connect to the Inventory database
        connection = sqlite3.connect(MainDatabase)
        cursor = connection.cursor()
        #Get the quantity values for each category
        cursor.execute('''
            SELECT Category, Quantity, Name From Items
            ''')
        connection.commit()
        InventoryResult = cursor.fetchall()
        print(InventoryResult)
        #Close the connection
        connection.close()
#----------------------------------

Output:
categories.db: [('N/A', 0), ('Test', 10), ('Other', 10), ('Small Parts', 100), ('Wire', 2), ('---', 0), ('---', 0), ('---', 0), ('---', 0), ('---', 0), ('---', 0), ('---', 0), ('---', 0), ('---', 0), ('---', 0), ('---', 0), ('---', 0), ('---', 0), ('---', 0), ('---', 0), ('---', 0)]

inventory.db: [('N/A', 20, 'MG90S'), ('N/A', 0, 'SG90'), ('Other', 15, 'MG996R'), ('Other', 20, 'DMS-2000MD'), ('Small Parts', 50, 'Indicator Led'), ('Small Parts', 4, 'RGB Fan'), ('Wire', 1, '12 Awg Wire -Red'), ('N/A', 200, 'Test Item'), ('Small Parts', 25, '1/2 inch Chase Nipple'), ('Test', 5, 'Test Item 2')]

Side note: (---,0) are blank placeholder values for the categories


Inventory.db:

#----------------------------------------------------------------------------------------------
#                                 Create Inventory Database
#----------------------------------------------------------------------------------------------
def createInventoryDatabase():
        #Create a database (inventory.db)
        connection = sqlite3.connect("inventory.db")
        cursor = connection.cursor()

        table = """CREATE TABLE IF NOT EXISTS Items
                (ID INTEGER PRIMARY KEY  AUTOINCREMENT,
                Name           TEXT    NOT NULL,
                Quantity       INT     NOT NULL,
                Price_$        DOUBLE  NOT NULL,
                Sell_Price_$   DOUBLE,
                Description    TEXT,
                Category       TEXT,
                Location       TEXT    NOT NULL,
                Length_Ft      INT,
                Barcode        INT,
                Date Updated   datetime default current_timestamp);"""

        #Execute the creation of the table
        cursor.execute(table)
        #print("The database has been created")
        #Commit the changes
        connection.commit()
        #Close the connection
        connection.close() 
#----------------------------------------------------------------------------------------------

Categories.db:

#----------------------------------------------------------------------------------------------
#                          Create Category Database
#----------------------------------------------------------------------------------------------
def createCategoryDatabase():
        #Create a database (users.db)
        connection = sqlite3.connect("categories.db")
        cursor = connection.cursor()
 
        table = """CREATE TABLE IF NOT EXISTS Categories
                (ID INTEGER PRIMARY KEY  AUTOINCREMENT,
                Category            TEXT    NOT NULL,
                Low_Quantity_Value  INT     NOT NULL);"""
 
        #Execute the creation of the table
        cursor.execute(table)
        #print("The database has been created")
        #Commit the changes
        connection.commit()

        #Add default values to table:
        defaultValues = cursor.execute(
        """SELECT * FROM Categories """).fetchall()
        if len(defaultValues) == 0:      
                #Add a default category
                cursor.execute('''
                INSERT into Categories (Category, Low_Quantity_Value)
                VALUES ('N/A','0')
                ''')

                #Create 20 Blank Rows For Categories 
                for x in range (0,20):
                        cursor.execute('''
                        INSERT into Categories (Category, Low_Quantity_Value)
                        VALUES ('---','0')
                        ''')
                connection.commit()
                connection.close()
        else:
                connection.close() 
#----------------------------------------------------------------------------------------------

Is there a reason why you store these tables in separate databases? This complicates the solution. To benefit from a database you should let the database do the work. To have the database do the work, all the related data should be in one database. Then your database can answer your question in one query:

select C.Category, 
       I.Name,
       C.Low_Quantity_Value as Minimum,
       I.Quantity           as Current
from   Category as C,
       Items    as I
where  C.Category = I. Category
  and  I.Quantity < C.Low_Quantity_Value

---

Explanation:

from   Category as C,
       Items    as I

Use data from these two tables. For short: name them C an I in this query.

where  C.Category = I. Category

Join the tables on the Category column.

and  I.Quantity < C.Low_Quantity_Value

Select only rows where the quantity is below the limit.

Thanks.
I made a second table in my MainDatabase(inventory.db) to hold the Categories & Low_Quantity_Values, and now everything works the way I want it to.
And yes, you're right. It's much easier to do it that way.