4a and 4b Database Hacks

• Table Creation
• Create
• Read
• Update
• Delete

Table Creation

import sqlite3 

def create_connection(db): 
    connection = None
    try: 
        connection = sqlite3.connect(db)
        return connection 
    except Error as e: 
        print(e)
    return connection 

def create_table(connection, createTblSql): 
    try: 
        cursor = connection.cursor()
        cursor.execute(createTblSql)
    except Error as e: 
        print(e)

def main(): 
    database = 'instance/exersizes.db'

    createTblSql = """ CREATE TABLE IF NOT EXISTS exersizes (
                                        _id integer PRIMARY KEY,
                                        _name text NOT NULL,
                                        _muscle text NOT NULL,
                                        _review text NOT NULL
                                    ); """
    connection = create_connection(database)

    # create exersizes table
    if connection is not None:
        create_table(connection, createTblSql)
    else:
        print('Connection Error')


if __name__ == '__main__':
    main()

Create

def create():
    database = 'instance/exersizes.db'
    name = input("Enter the exersize name")
    muscle = input("Enter muscle group it works")
    review = input("Enter review of that exersize")

    # Connecting to the database, create cursor to execute the SQL command
    connection = sqlite3.connect(database)
    cursor = connection.cursor()

    try:
        # Execute SQL to insert record in to db
        cursor.execute("INSERT INTO exersizes (_name, _muscle, _review) VALUES (?, ?, ?)", (name, muscle, review))
        # Commit the changes
        connection.commit()
        print(f"New exersize with {name} is added.")
                
    except sqlite3.Error as error:
        print("Error while inserting record", error)

    # Closing cursor and connection
    cursor.close()
    connection.close()
    
create()

New exersize with  is added.

Read

def read():

    database = 'instance/exersizes.db'
    # Connecting to the database, create cursor to execute the SQL command
    connection = sqlite3.connect(database)
    cursor = connection.cursor()
    
    # Fetch all the records from exersize table
    results = cursor.execute('SELECT * FROM exersizes').fetchall()

    if len(results) != 0:
        for row in results:
            print(row)
    else:
        print("No exercizes")

    # Closing cursor and connection
    cursor.close()
    connection.close()

read()

(1, '', '', '')

Update

import sqlite3

# updating review
def update():  
    
    database = 'instance/exersizes.db'
    exersizeId = input("Enter a exersize id to update the review")
    review = input("Enter new review")

   # Connecting to the database, create cursor to execute the SQL command
    connection = sqlite3.connect(database)
    cursor = connection.cursor()

    try:
        # Updating review for the exersize
        cursor.execute("UPDATE exersizes SET _review = ? WHERE _id = ?", (review, exersizeId))
        if cursor.rowcount != 0:
            print(f"Review for the exersize is updated to {review}")
            connection.commit()
        else:
            print(f"Exersize not found")
    except sqlite3.Error as error:
        print("Error occurred", error)
        
    # Closing cursor and connection
    cursor.close()
    connection.close()


update()

Exersize not found

Delete

import sqlite3

def delete():
    
    database = 'instance/exersizes.db'
    exersizeId = input("Enter Exersize id to delete")

    # Connecting to the database, create cursor to execute the SQL command
    connection = sqlite3.connect(database)
    cursor = connection.cursor()
    
    try:
        cursor.execute("DELETE FROM exersizes WHERE _id = ?", (exersizeId))
        if cursor.rowcount == 0:
            print(f"{exersizeId} does not exist")
        else:
            print(f"Successfully deleted exersize with id {exersizeId}")
        connection.commit()
    except sqlite3.Error as error:
        print("Error occurred: ", error)
        
    # Closing cursor and connection
    cursor.close()
    connection.close()

delete()

Error occurred:  Incorrect number of bindings supplied. The current statement uses 1, and there are 0 supplied.

options = [
    ('c', 'Create'),
    ('r', 'Read'),
    ('u', 'Update'),
    ('d', 'Delete')
]

# Define the menu function
def menu():
    # Display the menu options
    print('Select an option:')
    for option in options:
        print(f'({option[0]}) {option[1]}')

    # Get the user's choice
    operation = input("See the output below and choose an option, or press 'enter' to exit.").lower()

    # Find the selected option in the options list
    selected_option = None
    for option in options:
        if operation == option[0]:
            selected_option = option
            break

    # Call the corresponding function for the selected option
    if selected_option:
        if selected_option[0] == 'c':
            create()
        elif selected_option[0] == 'r':
            read()
        elif selected_option[0] == 'u':
            update()
        elif selected_option[0] == 'd':
            delete()
    elif operation == '':
        return
    else:
        print('Invalid option')

    # Repeat the menu
    menu()


menu()

Select an option:
(c) Create
(r) Read
(u) Update
(d) Delete
(2, 'pushup', 'chest ', '9/10')
Select an option:
(c) Create
(r) Read
(u) Update
(d) Delete