Top Swimmers SQL Menu (sqlite3 Library)
"""
This uses Python sqlite3 library with connection and cursor to execute CRUD on swimmer DB
"""
import sqlite3
database = 'instance/sqlite.db'
# Gets the schema of the swimmer DB
def schema():
conn = sqlite3.connect(database)
cursor = conn.cursor()
results = cursor.execute("PRAGMA table_info('swimmer')").fetchall()
for row in results:
print(row)
conn.close()
schema()
import sqlite3
# read the swimmer rows from DB
def read():
conn = sqlite3.connect(database)
cursor = conn.cursor()
# Use SQL command to read
results = cursor.execute('SELECT * FROM swimmer').fetchall()
if len(results) == 0:
print("Table is empty")
else:
for row in results:
print(row)
cursor.close()
conn.close()
read()
import sqlite3
# Add a new swimmer to the DB
def create():
name = input("Enter swimmer name:")
speed = input("Enter swimmer speed:")
height = input("Enter swimmer height:")
weight = input("Enter swimmer weight:")
conn = sqlite3.connect(database)
cursor = conn.cursor()
# use SQL command to insert new swimmer to DB
try:
cursor.execute("INSERT INTO swimmer (_name, _speed, _height, _weight) VALUES (?, ?, ?, ?)", (name, speed, height, weight))
conn.commit()
print(f"A new swimmer record {name} has been created")
except sqlite3.Error as error:
print("Error while executing the INSERT:", error)
cursor.close()
conn.close()
# Unit Testing
# create()
import sqlite3
# Update a swimmer to the DB
def update():
name = input("Enter name to update")
speed = input("Enter updated speed")
height = input("Enter updated height")
weight = input("Enter updated weight")
conn = sqlite3.connect(database)
cursor = conn.cursor()
# Update swimmer data to the DB
try:
cursor.execute("UPDATE swimmer SET _speed = ? WHERE _name = ?", (speed, name))
cursor.execute("UPDATE swimmer SET _height = ? WHERE _name = ?", (height, name))
cursor.execute("UPDATE swimmer SET _weight = ? WHERE _name = ?", (weight, name))
if cursor.rowcount == 0:
print(f"No name {name} was not found in the table")
else:
print(f"The row with swimmer name {name} has been successfully updated")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the UPDATE:", error)
cursor.close()
conn.close()
# Unit Testing
#update()
import sqlite3
# Delete a swimmer from the DB
def delete():
name = input("Enter swimmer name to delete")
conn = sqlite3.connect(database)
cursor = conn.cursor()
# Delete using SQL command from the DB
try:
cursor.execute("DELETE FROM swimmer WHERE _name = ?", (name,))
if cursor.rowcount == 0:
print(f"No name {name} was not found in the table")
else:
print(f"The row with name {name} was successfully deleted")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the DELETE:", error)
cursor.close()
conn.close()
# Unit Testing
#delete()
def menu():
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete or (S)chema")
print("\n")
if operation.lower() == 'c':
print("Create: \n")
create()
elif operation.lower() == 'r':
print("Read: \n")
read()
elif operation.lower() == 'u':
print("Update: \n")
update()
elif operation.lower() == 'd':
print("Delete: \n")
delete()
elif operation.lower() == 's':
print("Schema: \n")
schema()
elif len(operation)==0:
return
else:
print("Please enter c, r, u, d, or s\n")
menu()
try:
menu()
except:
print("Perform Jupyter 'Run All' prior to starting menu")
read()