BMI Database
import sqlite3
def create_connection(db_file):
conn = None
try:
conn = sqlite3.connect(db_file)
return conn
except Error as e:
print(e)
return conn
def create_table(conn, create_table_sql):
try:
c = conn.cursor()
c.execute(create_table_sql)
except Error as e:
print(e)
def main():
database = 'instance/sqlite.db'
sqlite_table = """CREATE TABLE IF NOT EXISTS movies (
_id integer PRIMARY KEY,
_name text,
_height integer,
_weight integer,
_BMI integer
); """ # not null makes coloum not accpet null values
conn = create_connection(database)
# create table
create_table(conn, sqlite_table)
if __name__ == '__main__':
main()
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('BMIs')").fetchall()
# Print the results
for row in results:
print(row)
# Close the database connection
conn.close()
schema()
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 BMIList').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()
def create():
database = 'instance/sqlite.db'
name = input("Enter your name:")
height = input("Enter your height:")
weight = input("Enter your weight")
BMI = input("Enter your BMI")
# Connect to the database file
conn = sqlite3.connect(database)
# execute SQL commands
cursor = conn.cursor()
try:
# insert data into a table
cursor.execute("INSERT INTO BMIList (_name, _height, _weight, _BMI) VALUES (?, ?, ?, ?)",
(name, height, weight, BMI))
# Commit the changes
conn.commit()
print(f"A new BMI record {BMI} has been created")
except sqlite3.Error as error:
print("Error with inserting", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
create()
read()
import sqlite3
def delete():
database = 'instance/sqlite.db'
BMI = input("Enter BMI 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 BMIList WHERE _BMI = ?", (BMI,))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No BMI {BMI} was not found in the table")
else:
# The uid was found in the table and the row was deleted
print(f"The row with BMI {BMI} has been deleted")
conn.commit()
except sqlite3.Error as error:
print("Error in DELETE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
print("----Original Table:----")
read()
print("")
delete()
read()
import sqlite3
def update():
name = input("Enter your name to update your BMI")
BMI = input("Enter updated BMI")
if BMI == 19:
message = "you are normal"
else:
message = "you are either underweight or overweight"
# 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
cursor.execute("UPDATE BMIList SET _BMI = ? WHERE _name = ?", (BMI, name))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No name {name} was not found in the table")
else:
print(f"The row with his/her {name} implies that {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()
print('----Original Database:----')
read()
print("")
print('----Updated Database:----')
update()
read()
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, d")
menu() # recursion (repeats)
try:
menu() # start menu
except:
print("Perform Jupyter 'Run All' prior to starting menu")