2.4 Hacks

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/basketball.db'

    createTblSql = """ CREATE TABLE IF NOT EXISTS basketball (
                                        _id integer PRIMARY KEY,
                                        _player text NOT NULL,
                                        _nationality text NOT NULL,
                                        _strengths text NOT NULL,
                                        _weaknesses text NOT NULL
                                    ); """
    connection = create_connection(database)

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


if __name__ == '__main__':
    main()

Create

def create():
    database = 'instance/basketball.db'
    player = input("Enter the player name")
    nationality = input("Enter nationality")
    strengths = input("Enter player strengths")
    weaknesses = input("Enter your player weaknesses")

    # 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 basketball (_player, _nationality, _strengths, _weaknesses) VALUES (?, ?, ?, ?)", (player, nationality, strengths, weaknesses))
        # Commit the changes
        connection.commit()
        print(f"New player {player} is added.")
                
    except sqlite3.Error as error:
        print("Error while inserting record", error)

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

New basketball with Mugsy Bogues is added.

Read

def read():

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

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

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

read()

(1, 'Lebron James', 'American', 'Clutch', 'Shooting')
(2, 'Kevin Durant', 'American', 'Shooting', 'Strength')
(3, 'Mugsy Bogues', 'American', 'Dunking', 'Short')

Update

import sqlite3

# updating review
def update():  
    
    database = 'instance/basketball.db'
    basketballId = input("Enter a basketball id to update the review")
    weaknesses = input("Enter new weakness")

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

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


update()

Review for the basketball is updated to Shooting

Delete

import sqlite3

def delete():
    
    database = 'instance/basketball.db'
    basketballId = input("Enter basketball 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 basketball WHERE _id = ?", (basketballId,))
        if cursor.rowcount == 0:
            print(f"{basketballId} does not exist")
        else:
            print(f"Successfully deleted player with id {basketballId}")
        connection.commit()
    except sqlite3.Error as error:
        print("Error occurred: ", error)
        
    # Closing cursor and connection
    cursor.close()
    connection.close()

delete()

Successfully deleted book with id 2

Menu

def process():
    endpoint = input("r for read, d for delete, u for update, c for create")
    if endpoint == "r":
        with app.app_context():
            table = User.query.all()
            json_ready = [user.read() for user in table] # "List Comprehensions", for each user add user.read() to list
        return json_ready
    elif endpoint == "d":
        delete()
    elif endpoint == "u":
        update()
    elif endpoint == "c":
        create()
    else:
        print('function not found')


process()

New basketball with Micheal Jordan is added.