planet-express-co-sqlite3-basics-tutorial.py

import sqlite3


def create_employee_table(cursor):
    stmt = """
    CREATE TABLE IF NOT EXISTS employee(
        id INTEGER PRIMARY KEY,
        first_name TEXT,
        last_name TEXT,
        email TEXT UNIQUE NOT NULL,
        active BOOLEAN DEFAULT TRUE NOT NULL
    )
    """
    cursor.execute(stmt)


def create_delivery_table(cursor):
    stmt = """
    CREATE TABLE IF NOT EXISTS delivery(
        id INTEGER PRIMARY KEY,
        sender_name TEXT NOT NULL,
        sender_address TEXT NOT NULL,
        recipient_name TEXT NOT NULL,
        recipient_address TEXT NOT NULL,
        cost FLOAT NOT NULL,
        delivered_by INTEGER,
        delivered_at DATETIME,
        FOREIGN KEY(delivered_by) REFERENCES employee(id)
    )
    """
    cursor.execute(stmt)


def show_tables(cursor):
    stmt = """
    SELECT name FROM sqlite_master
    WHERE type = 'table'
    """
    result = cursor.execute(stmt)
    print(result.fetchall())


def insert_employee(cursor, first_name, last_name, email, active=True):
    stmt = """
    INSERT INTO employee VALUES(?, ?, ?, ?, ?)
    """
    cursor.execute(stmt, (None, first_name, last_name, email, active))


def find_employee_by_email(cursor, email):
    stmt = """
    SELECT * FROM employee WHERE email = ?
    """
    result = cursor.execute(stmt, (email,))
    return result.fetchall()


def find_employee_by_id(cursor, _id):
    stmt = """
    SELECT * FROM employee WHERE id = ?
    """
    result = cursor.execute(stmt, (_id,))
    return result.fetchall()


def update_employee_email_by_id(cursor, _id, new_email):
    stmt = """
    UPDATE employee SET email = ? WHERE id = ?
    """
    result = cursor.execute(stmt, (new_email, _id))
    return result


def main():
    conn = sqlite3.connect("planet-express-co.db")
    cursor = conn.cursor()

    create_employee_table(cursor)
    create_delivery_table(cursor)
    show_tables(cursor)

    # Comment this out after first run or you can run into odd issues
    insert_employee(cursor, "Hubert", "Farnsworth", "hubert@planex.co", True)
    conn.commit()

    email = "hubert@planex.co"
    employee = find_employee_by_email(cursor, email)
    print(employee)
    employee = find_employee_by_id(cursor, 1)
    print(employee)

    new_email = "hubert@plan-ex.co"
    update_employee_email_by_id(cursor, _id=1, new_email=new_email)
    conn.commit()

    employee = find_employee_by_email(cursor, new_email)
    print(employee)


if __name__ == "__main__":
    main()