Skip to content

Instantly share code, notes, and snippets.

@marcelozarate
Last active October 1, 2021 17:47
Show Gist options
  • Save marcelozarate/3e4b8bfaf2d3d4652c4f93371a62de8f to your computer and use it in GitHub Desktop.
Save marcelozarate/3e4b8bfaf2d3d4652c4f93371a62de8f to your computer and use it in GitHub Desktop.
sqlite python tutorial
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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment