SQLite – Comparing Multiple Tables Using Python

pythonsqlite

I am quite new to SQLITE3 as well as python. I am working on a project where I have one database with about 20 tables inside of it. One table is for user input and the other tables are pre-loaded with values. I want to be able to compare and match which values are in the pre-loaded tables with the user table. For example: Here is a link to the example tables
enter image description here

Now, I want to be able to compare each row in the Pre-Loaded Table to each row in the Users Table. They both have the Barcode column in common to be able to compare. As a result, during the query process, it should check each row: 1234 – milk – 1 (those columns are equal ) 5678 – butter – 1 ( those columns are equal) 9876 – sugar – 1 (those columns are equal) 1100 – – 1 ( this barcode does not exist in the Users Table)

so when a Barcode, in this case, 1100 doesn't exist in the Users Table, the code should print: You don't have all the items for the Pre-Loaded Table.

so far I have this: 

import sqlite3 as sq


connect = sq.connect('Food_Data.db')
con = connect.cursor()

sql = ("SELECT Users_Food.Barcode, Users_Food.Item,     Recipe1.Ham_Swiss_Omelet FROM Users_Food INNER JOIN Recipe1 ON Users_Food.Barcode = Recipe1.Barcode WHERE Recipe1.Ham_Swiss_Omelet = '1'")
con.execute(sql)
data = con.fetchall()
print("You can make: Ham Swiss Omelet")
formatted_row = '{:<10} {:<9} {:>9} '
print(formatted_row.format("Barcode", "Ingredients", "Availability"))
for row in data:
    print(formatted_row.format(*row))
    #print (row[:])
    #connect.commit()

Best Answer

I figured it out. I was able to do a FULL OUTER JOIN to display what is missing from one table but included in another table. 

SELECT d.type,
         d.color,
         c.type,
         c.color
FROM dogs d
LEFT JOIN cats c USING(color)
UNION ALL
SELECT d.type,
         d.color,
         c.type,
         c.color
FROM cats c
LEFT JOIN dogs d USING(color)
WHERE d.color IS NULL;
Related Question