sqlite python tutorial

sqlitepython.py

import sqlite3
import pandas as pd

def create_connection(db):
    conn = None
    try:
        conn = sqlite3.connect(db)
        conn.execute("PRAGMA foreign_keys = 1")
    except sqlite3.Error as err:
        print(err)
    return conn


def createSuppliers(conn):
    query = """CREATE TABLE IF NOT EXISTS suppliers (
        id INTEGER PRIMARY KEY NOT NULL,
        name TEXT NOT NULL,
        address TEXT
        );"""
    execute(conn, query)

def createProducts(conn):
    query = """CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY NOT NULL,
        description TEXT NOT NULL,
        quantity INTEGER NOT NULL,
        supplier_id INTEGER NOT NULL,
        FOREIGN KEY(supplier_id) REFERENCES suppliers(id)
        );"""
    execute(conn, query)

def insertSupplier(conn, supplier):
    query = "INSERT INTO SUPPLIERS (name, address) VALUES (?,?);"
    execute(conn, query, supplier)

def insertProduct(conn, product):
    query = "INSERT INTO PRODUCTS (description, quantity, supplier_id) VALUES (?,?,?);"
    execute(conn, query, product)

def selectAllSuppliers(conn):
    query = "SELECT * FROM suppliers;"
    print (pd.read_sql_query(query, conn))

def selectAllProducts(conn):
    query = "SELECT * FROM products;"
    print (pd.read_sql_query(query, conn))

def updateProductQuantity(conn, productId, quantity):
    query = "UPDATE products SET quantity = (quantity + ?) WHERE id = ?;"
    execute(conn, query, (quantity, productId))

def deleteProduct(conn, productId):
    query = "DELETE FROM products WHERE id = ?;"
    execute(conn, query, productId)

def deleteSupplier(conn, supplierId):
    query = "DELETE FROM suppliers WHERE id = ?;"
    execute(conn, query, supplierId)

def execute(conn, query, args = None):
    if args:
        conn.execute(query, args)
        conn.commit()
    else:
        conn.execute(query)
        conn.commit()

def showMenu():
    print("1) List suppliers")
    print("2) List products")
    print("3) Create supplier")
    print("4) Create product")
    print("5) Change product quantity")
    print("6) Delete supplier")
    print("7) Delete product")
    print("8) Exit")

def main():
    DBFILE = "db.sqlite3"

    conn = create_connection(DBFILE)
    with conn:
        createSuppliers(conn)
        createProducts(conn)
        showMenu()
        option = input("Choose an option: ")
        while option != "8":
            if option == "1":
                selectAllSuppliers(conn)
            if option == "2":
                selectAllProducts(conn)
            if option == "3":
                name = input("Supplier name: ")
                address = input("Supplier address: ")
                insertSupplier(conn,(name, address))
            if option == "4":
                description = input("Product description: ")
                quantity = input("Product quantity: ")
                supplier_id = input("Supplier id: ")
                insertProduct(conn,(description, quantity, supplier_id))
            if option == "5":
                productId = input("Product id: ")
                changeQuantity = input("How many to add/substract (e.g.: 13, 1, -8): ")
                updateProductQuantity(conn, productId, changeQuantity)
            if option == "6":
                supplierId = input("Supplier id: ")
                deleteSupplier(conn, supplierId)
            if option == "7":
                productId = input("Product id: ")
                deleteProduct(conn, productId)
            showMenu()
            option = input("Choose an option: ")
            print(option)

if __name__ == '__main__':
    main()