Comparing multiple tables in Sqlite 3 using python

I am quite new to SQLITE3 as well as python. I a complete beginner in SQLite. I don’t understand much. I am right now learning as a go for my project.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. How can I compare and match which values that are in the pre-loaded table with the user table?? For example:

Users Table: 
Barcode:      Item: 
1234          milk
4321          cheese
5678          butter
8765          water
9876          sugar

Pre-Loaded Table: 
Barcode:       Availability:
1234               1
5678               1
9876               1
1111               1

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. How can I get the code to this?

so far I have this: This code does work by the way.

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

It prints:

You can make: Ham Swiss Omelet
Barcode    Ingredients Availability 
9130849874 butter            1 
2870896881 eggs              1 
5501066727 water             1 
1765023029 salt              1 
9118188735 pepper            1 
4087256674 ham               1 
3009527296 cheese            1 

The SQLite code:

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'")

It combines the two tables with the Barcode in common and and the corresponding food names and availability. However, If one of the barcode values is not present in the Pre-Loaded table, when I compare how can I go about coding to know that it is not there while still displaying what is there in common between those two tables? It is like checking to see if the tables are identical.

Answer

Perhaps try your luck with LEFT JOIN and a CASE statement.

From sqlite doc

If the join-operator is a “LEFT JOIN” or “LEFT OUTER JOIN”, then after the ON or USING filtering clauses have been applied, an extra row is added to the output for each row in the original left-hand input dataset that corresponds to no rows at all in the composite dataset (if any).

You need the Recipe1 table to be the left-hand table, because you need to select every row in that table. All columns from Users_Food will be null in the extra row. The sample query adds another column “status”, which you can use in the python. With a little rearranging:

SELECT Users_Food.Barcode, Users_Food.Item, Recipe1.Ham_Swiss_Omelet,
CASE WHEN (Users_Food.Barcode is null then 'You cannot make this recipe' else ' ' END as status
FROM Recipe1
LEFT JOIN Users_Food ON Users_Food.Barcode = Recipe1.Barcode
WHERE Recipe1.Ham_Swiss_Omelet = '1'

In python you might not want to print("You can make: Ham Swiss Omelet") since you won’t know whether that is true until you fetch all the returned rows.

After you get the SQL to return the rows that you want, you can play around with the python to get the desired output.