2.4 Database

• Creating the Table
• Create
• Read
• Update
• Delete

Creating the Table

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

    createTblSql = """ CREATE TABLE IF NOT EXISTS baseball (
                                        _id integer PRIMARY KEY,
                                        _player text NOT NULL,
                                        _team text NOT NULL,
                                        _position text NOT NULL,
                                        _age text NOT NULL
                                    ); """
    connection = create_connection(database)

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


if __name__ == '__main__':
    main()

Create

def create():
    database = 'instance/baseball.db'
    player = input("Enter the player name")
    team = input("Enter player team")
    position = input("Enter player position")
    age = input("Enter player age ")

    # 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 baseball (_player, _team, _position, _age) VALUES (?, ?, ?, ?)", (player, team, position, age))
        # Commit the changes
        connection.commit()
        print(f"New baseball with {player} is added.")
                
    except sqlite3.Error as error:
        print("Error while inserting record", error)

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

New baseball with Aaron Judge is added.

Read

def read():

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

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

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

read()

(1, 'Mike Trout', 'Angels', 'Center Field', '31')
(3, 'Fernando Tatis', 'Padres', 'shortstop', '24')
(4, 'Juan Soto', 'Padres', 'Right Field', '24')
(5, 'Aaron Judge', 'Yankees', 'Right Field', '30')

Update

import sqlite3

# updating review
def update():  
    
    database = 'instance/baseball.db'
    baseballId = input("Enter a baseball id to update the review")
    positon = input("Enter new position")

   # 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 baseball SET _position = ? WHERE _id = ?", (positon, baseballId))
        if cursor.rowcount != 0:
            print(f"Review for the baseball is updated to {positon}")
            connection.commit()
        else:
            print(f"baseball not found")
    except sqlite3.Error as error:
        print("Error occurred", error)
        
    # Closing cursor and connection
    cursor.close()
    connection.close()


update()

Review for the baseball is updated to second base

Delete

import sqlite3

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

delete()

Successfully deleted basketball with id 1

def menu():
    operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete")
    if operation.lower() == 'c':
        create()
    elif operation.lower() == 'r':
        read()
    elif operation.lower() == 'u':
        update()
    elif operation.lower() == 'd':
        delete()
    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")

New baseball with Cody Bellinger is added.