Home API Notes

Database Programming is Program with Data

Each Tri 2 Final Project should be an example of a Program with Data.

Prepare to use SQLite in common Imperative Technique

Schema of Users table in Sqlite.db

Uses PRAGMA statement to read schema.

Describe Schema, here is resource Resource- What is a database schema?

  • What is the purpose of identity Column in SQL database?
  • What is the purpose of a primary key in SQL database?
  • What are the Data Types in SQL table?
import sqlite3

database = 'instance/sqlite.db' # this is location of database

def schema():
    
    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL queries
    cursor = conn.cursor()
    
    # Fetch results of Schema
    results = cursor.execute("PRAGMA table_info('users')").fetchall()

    # Print the results
    for row in results:
        print(row)

    # Close the database connection
    conn.close()
    
schema()
(0, 'id', 'INTEGER', 1, None, 1)
(1, '_name', 'VARCHAR(255)', 1, None, 0)
(2, '_uid', 'VARCHAR(255)', 1, None, 0)
(3, '_password', 'VARCHAR(255)', 1, None, 0)
(4, '_dob', 'DATE', 0, None, 0)

Reading Users table in Sqlite.db

Uses SQL SELECT statement to read data

  • What is a connection object? After you google it, what do you think it does?
  • Same for cursor object?
  • Look at conn object and cursor object in VSCode debugger. What attributes are in the object?
  • Is "results" an object? How do you know?
import sqlite3

def read():
    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL queries
    cursor = conn.cursor()
    
    # Execute a SELECT statement to retrieve data from a table
    results = cursor.execute('SELECT * FROM users').fetchall()

    # Print the results
    if len(results) == 0:
        print("Table is empty")
    else:
        for row in results:
            print(row)

    # Close the cursor and connection objects
    cursor.close()
    conn.close()
    
read()
(1, 'Thomas Edison', 'toby', 'sha256$dKm9cAQuN8UjZ4w6$1d5671d87dcf5db16efa3abf866a669f67c988e1f486dbf614bf74223eea5b03', '1847-02-11')
(2, 'Nikola Tesla', 'niko', 'sha256$Q2Lz8WgTyTg7wpAw$bfad03f7109c3357222e3f10bc82a86a3bee237c51179a45e46464fccb855cdf', '2023-03-15')
(3, 'Alexander Graham Bell', 'lex', 'sha256$u3vl7gdpYuzW0j09$eb7c64e407e64790fe85585d0175f45ba2794ee27a2efe96373b9c1fd5d6e735', '2023-03-15')
(4, 'Eli Whitney', 'whit', 'sha256$pN2qjnPLo6sLQDN9$3bb48cbcd134524552c8615ad9fdb083feacebc16b70904dba2c189dc43f0c50', '2023-03-15')
(5, 'Indiana Jones', 'indi', 'sha256$KuQbAU5jv4AZaFR1$2d4a24e3181391b0637ebd0bcb50b3b2b8aeac058c761a684d23abdae8c1c9af', '1920-10-21')
(6, 'Marion Ravenwood', 'raven', 'sha256$r99JMkxAEXDOgrGo$15c82f528b56b526e1c5d989339110ed57c94b2d02bc5ae3221f98ab2ed2afbb', '1921-10-21')
(7, 'Ethan', 'ethan123', 'testpass', '1880-01-01')
(8, 'John', 'doe123', '555', '1890-01-01')
(9, '', '', 'gothackednewpassword123', '')

Create a new User in table in Sqlite.db

Uses SQL INSERT to add row

  • Compore create() in both SQL lessons. What is better or worse in the two implementations?
  • Explain purpose of SQL INSERT. Is this the same as User init?
import sqlite3

def create():
    name = input("Enter your name:")
    uid = input("Enter your user id:")
    password = input("Enter your password")
    dob = input("Enter your date of birth 'YYYY-MM-DD'")
    
    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()

    try:
        # Execute an SQL command to insert data into a table
        cursor.execute("INSERT INTO users (_name, _uid, _password, _dob) VALUES (?, ?, ?, ?)", (name, uid, password, dob))
        
        # Commit the changes to the database
        conn.commit()
        print(f"A new user record {uid} has been created")
                
    except sqlite3.Error as error:
        print("Error while executing the INSERT:", error)


    # Close the cursor and connection objects
    cursor.close()
    conn.close()
    
create()
A new user record  has been created

Updating a User in table in Sqlite.db

Uses SQL UPDATE to modify password

  • What does the hacked part do?
  • Explain try/except, when would except occur?
  • What code seems to be repeated in each of these examples to point, why is it repeated?
import sqlite3

def update():
    uid = input("Enter user id to update")
    dob = input("Enter your date of birth 'YYYY-MM-DD'")
    password = input("Enter updated password")
    if len(password) < 2:
        message = "hacked"
        password = 'gothackednewpassword123'
    else:
        message = "successfully updated"

    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()

    try:
        # Execute an SQL command to update data in a table
        # Trying to add dob right now...
        cursor.execute("UPDATE users SET _password = ?, _dob = ? WHERE _uid = ?", (password, dob, uid))
        if cursor.rowcount == 0:
            # The uid was not found in the table
            print(f"No uid {uid} was not found in the table")
        else:
            print(f"The row with user id {uid} the password has been {message}")
            conn.commit()
    except sqlite3.Error as error:
        print("Error while executing the UPDATE:", error)
        
    
    # Close the cursor and connection objects
    cursor.close()
    conn.close()
    
update()
The row with user id ethan123 the password has been successfully updated

Delete a User in table in Sqlite.db

Uses a delete function to remove a user based on a user input of the id.

  • Is DELETE a dangerous operation? Why?
  • In the print statemements, what is the "f" and what does {uid} do?
import sqlite3

def delete():
    uid = input("Enter user id to delete")

    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()
    
    try:
        cursor.execute("DELETE FROM users WHERE _uid = ?", (uid,))
        if cursor.rowcount == 0:
            # The uid was not found in the table
            print(f"No uid {uid} was not found in the table")
        else:
            # The uid was found in the table and the row was deleted
            print(f"The row with uid {uid} was successfully deleted")
        conn.commit()
    except sqlite3.Error as error:
        print("Error while executing the DELETE:", error)
        
    # Close the cursor and connection objects
    cursor.close()
    conn.close()
    
delete()
The row with uid  was successfully deleted

Menu Interface to CRUD operations

CRUD and Schema interactions from one location by running menu. Observe input at the top of VSCode, observe output underneath code cell.

  • Why does the menu repeat?
  • Could you refactor this menu? Make it work with a List?
def menu():
    operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
    if operation.lower() == 'c':
        create()
    elif operation.lower() == 'r':
        read()
    elif operation.lower() == 'u':
        update()
    elif operation.lower() == 'd':
        delete()
    elif operation.lower() == 's':
        schema()
    elif len(operation)==0: # Escape Key
        return
    else:
        print("Please enter c, r, u, or d") 
    menu() # recursion, repeat menu
        
try:
    menu() # start menu
except:
    print("Perform Jupyter 'Run All' prior to starting menu")
A new user record doe123 has been created
A new user record  has been created

Hacks

  • Add this Blog to you own Blogging site. In the Blog add notes and observations on each code cell.
  • In this implementation, do you see procedural abstraction?
  • In 2.4a or 2.4b lecture
    • Do you see data abstraction? Complement this with Debugging example.
    • Use Imperative or OOP style to Create a new Table or do something that applies to your CPT project.

Reference... sqlite documentation

import sqlite3

database = 'instance/cars.db' # this is location of database

def schema():
    
    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL queries
    cursor = conn.cursor()
    
    # Fetch results of Schema
    results = cursor.execute("PRAGMA table_info('cars')").fetchall()

    # Print the results
    for row in results:
        print(row)

    # Close the database connection
    conn.close()
    
schema()
(0, 'id', 'INTEGER', 1, None, 1)
(1, '_make', 'VARCHAR(255)', 1, None, 0)
(2, '_model', 'VARCHAR(255)', 1, None, 0)
(3, '_price', 'INTEGER', 1, None, 0)
(4, '_year', 'INTEGER', 1, None, 0)
(5, '_desc', 'VARCHAR(255)', 1, None, 0)
(6, '_engine', 'VARCHAR(255)', 1, None, 0)
(7, '_body_style', 'VARCHAR(255)', 1, None, 0)
(8, '_owner', 'VARCHAR(255)', 1, None, 0)

Create

import sqlite3

database = 'instance/cars.db'

def create():
    make = input("Enter the make of the car:")
    model = input("Enter the model:")
    price = input("Enter the price:")
    year = input("Enter the year:")
    desc = input("Enter a description of the car:")
    body_style = input("Enter the body style of the car:")
    engine = input("Enter the engine type:")
    owner = input("Who's the owner of the car?")
        
    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()

    try:
        # Execute an SQL command to insert data into a table
        cursor.execute("INSERT INTO cars (_make, _model, _price, _year, _desc, _body_style, _engine, _owner) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", (make, model, price, year, desc, body_style, engine, owner))
        
        # Commit the changes to the database
        conn.commit()
        print(f"A new car has been added")
                
    except sqlite3.Error as error:
        print("Error while executing the INSERT:", error)


    # Close the cursor and connection objects
    cursor.close()
    conn.close()
    
create()
Error while executing the INSERT: UNIQUE constraint failed: cars._desc

Read

import sqlite3

def read():
    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL queries
    cursor = conn.cursor()   # open connection object: contains methods and attributes you need to alter data 
    
    # Execute a SELECT statement to retrieve data from a table
    results = cursor.execute('SELECT * FROM cars').fetchall()  #results is an object 

    # Print the results
    if len(results) == 0:
        print("Table is empty")
    else:
        for row in results:
            print(row)

    # Close the cursor and connection objects
    cursor.close()
    conn.close()
    
read()
(1, 'BMW', '2 Series', 34000, 2021, 'The BMW 2 Series NEW DESCRIPTION!', '4 cylinder', 'Coupe', 'DoeJohn')
(7, 'Hyundai', 'Veloster N', 48000, 2019, 'New Description!', 'V6', 'Coupe', 'Jan')
(9, 'Kia Stinger', '45000', 2022, 'Kia Stinger, sports sedan!', 'Sedan', 'V6', 'Sedan', '')
(10, 'Dodge', 'Charger', 45000, 2021, 'Vroom!', 'V8', 'Sedan', "Scatpack's are cool")
(11, 'Ford', 'Mustang', 50000, 2019, 'American Muscle!', 'V8', 'Coupe', 'Mr. Muscle')
(12, 'Kia', 'Rio', 22000, 2020, 'Rioooooooo!', '4 cylinder', 'Sedan', 'Blue')
(13, 'TestMake', 'TestModel', 1000000, 2021, 'TestDesc', 'V12', 'NewBody', 'Test')
(14, 'BMW', '2 Series', 34000, 2021, 'The BMW 2 series is a stylish sports coupe that drives just as well as it looks.', '2.0L four-cylinder', 'coupe', 'John Doe')
(15, '', '', '', '', '', '', '', '')

Update

import sqlite3

database = 'instance/cars.db'

# Take the inputs for each attribute
def update():
    id = input("Enter the id of the car:")
    make = input("Enter the make of the car:")
    model = input("Enter the model:")
    price = input("Enter the price:")
    year = input("Enter the year:")
    desc = input("Enter a description of the car:")
    body_style = input("Enter the body style of the car:")
    engine = input("Enter the engine type:")
    owner = input("Who's the owner of the car?")

    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()

    try:
        # Execute an SQL command to update data in a table
        # Trying to add dob right now...
        cursor.execute("UPDATE cars SET _make = ?, _model = ?, _price = ?, _year = ?, _desc = ?,  _body_style = ?,  _engine = ?,  _owner = ?  WHERE id = ?", (make, model, price, year, desc, body_style, engine, owner, id))
        if cursor.rowcount == 0:
            # Car ID not found
            print(f"No id was found in the table")
        
        # Commit new data
        else: 
            print(f"Car with the id {id} has been updated")
            conn.commit()

    except sqlite3.Error as error:
        print("Error while updating the record:", error)
    
    # Close the cursor and connection objects
    cursor.close()
    conn.close()
    
update()
No id was found in the table

Delete

import sqlite3

def delete():

    id = input("Select the ID of the car you wish to remove: ")
    
    # Connect to the database file
    conn = sqlite3.connect(database)

    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()

    # Validate the input
    try:
        id = int(id)
    except ValueError:
        print("Invalid input: ID must be an integer.")
        return

    try:
        # Use a parameterized query to prevent SQL injection
        c = conn.cursor()
        c.execute("DELETE FROM cars WHERE id = ?", (id,))
        conn.commit()
        print(f"Row with ID {id} has been deleted.")
    except sqlite3.Error as e:
        print(f"Error deleting row with ID {id}: {e}")
    finally:
        # Close the connection
        cursor.close
        conn.close()

delete()
Row with ID 16 has been deleted.

CRUD Menu

def menu():
    operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
    if operation.lower() == 'c':
        create()
    elif operation.lower() == 'r':
        read()
    elif operation.lower() == 'u':
        update()
    elif operation.lower() == 'd':
        delete()
    elif operation.lower() == 's':
        schema()
    elif len(operation)==0: # Escape Key
        return
    else:
        print("Please enter c, r, u, or d") 
    menu() # recursion, repeat menu
        
try:
    menu() # start menu
except:
    print("Perform Jupyter 'Run All' prior to starting menu")
(1, 'BMW', '2 Series', 34000, 2021, 'The BMW 2 Series NEW DESCRIPTION!', '4 cylinder', 'Coupe', 'DoeJohn')
(7, 'Hyundai', 'Veloster N', 48000, 2019, 'New Description!', 'V6', 'Coupe', 'Jan')
(9, 'Kia Stinger', '45000', 2022, 'Kia Stinger, sports sedan!', 'Sedan', 'V6', 'Sedan', '')
(10, 'Dodge', 'Charger', 45000, 2021, 'Vroom!', 'V8', 'Sedan', "Scatpack's are cool")
(11, 'Ford', 'Mustang', 50000, 2019, 'American Muscle!', 'V8', 'Coupe', 'Mr. Muscle')
(12, 'Kia', 'Rio', 22000, 2020, 'Rioooooooo!', '4 cylinder', 'Sedan', 'Blue')
(13, 'TestMake', 'TestModel', 1000000, 2021, 'TestDesc', 'V12', 'NewBody', 'Test')
(14, 'BMW', '2 Series', 34000, 2021, 'The BMW 2 series is a stylish sports coupe that drives just as well as it looks.', '2.0L four-cylinder', 'coupe', 'John Doe')
(15, '', '', '', '', '', '', '', '')
Row with ID 15 has been deleted.
(1, 'BMW', '2 Series', 34000, 2021, 'The BMW 2 Series NEW DESCRIPTION!', '4 cylinder', 'Coupe', 'DoeJohn')
(7, 'Hyundai', 'Veloster N', 48000, 2019, 'New Description!', 'V6', 'Coupe', 'Jan')
(9, 'Kia Stinger', '45000', 2022, 'Kia Stinger, sports sedan!', 'Sedan', 'V6', 'Sedan', '')
(10, 'Dodge', 'Charger', 45000, 2021, 'Vroom!', 'V8', 'Sedan', "Scatpack's are cool")
(11, 'Ford', 'Mustang', 50000, 2019, 'American Muscle!', 'V8', 'Coupe', 'Mr. Muscle')
(12, 'Kia', 'Rio', 22000, 2020, 'Rioooooooo!', '4 cylinder', 'Sedan', 'Blue')
(13, 'TestMake', 'TestModel', 1000000, 2021, 'TestDesc', 'V12', 'NewBody', 'Test')
(14, 'BMW', '2 Series', 34000, 2021, 'The BMW 2 series is a stylish sports coupe that drives just as well as it looks.', '2.0L four-cylinder', 'coupe', 'John Doe')
def menu():
    options = ['c', 'r', 'u', 'd', 's']
    operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
    if operation.lower() in options:
        if operation.lower() == 'c':
            create()
        elif operation.lower() == 'r':
            read()
        elif operation.lower() == 'u':
            update()
        elif operation.lower() == 'd':
            delete()
        elif operation.lower() == 's':
            schema()
    elif len(operation) == 0: # Escape Key
        return
    else:
        print("Please enter c, r, u, or d") 
    menu() # recursion, repeat menu
        
try:
    menu() # start menu
except:
    print("Perform Jupyter 'Run All' prior to starting menu")