Python
import sqlite3
import pandas as pd
Python
conn = sqlite3.connect("mydb.db")
if conn:
    print("database connected")
database connected
Python
conn.execute("""CREATE TABLE IF NOT EXISTS PRODUCTS(
ID INT,
NAME TEXT,
SUPPLIER_ID INT,
CUSTOMER_ID INT,
UNIT TEXT,
PRICE REAL
    );""")
<sqlite3.Cursor object at 0x17945f0>
Python
Python
conn.execute("""INSERT INTO PRODUCTS VALUES
(1, 'cahis',1 , 1, '20 boxes', 19),
(2, 'ANISEED SYRUP', 1 , 1, '16 oz JARS', 12),
(3, 'SEASONING', 1 , 2, '46 jars', 10),
(4, 'CHEF ANTON SPECIAL', 2 , 2, '20 boxes', 16),
(5, 'CHEF ANTON SEASONING', 2 , 3, '12 oz jars', 11)
;""")
<sqlite3.Cursor object at 0x13082c0>
Python
pd.read_sql_query("""SELECT * FROM PRODUCTS; """, conn)
Python
pd.read_sql_query("""SELECT COUNT(ID) AS NO_OF_PRODUCTS FROM PRODUCTS; """, conn)
Python
pd.read_sql_query("""SELECT SUM(PRICE) AS TOTAL_PRICE FROM PRODUCTS; """, conn)
Python
pd.read_sql_query("""SELECT AVG(PRICE) AS AVERAGE_PRICE FROM PRODUCTS; """, conn)
Python
pd.read_sql_query("""SELECT COUNT(CUSTOMER_ID) AS UNIQUE_SUPPLIERS FROM PRODUCTS; """, conn)