[Solved]Help comparing 2 databases

[Extra]

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() 
#----------------------------------------------------------------------------------------------