Unit 2.4b HACKS
Completing the 2.4b Hacks
"""
These imports define the key objects
"""
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
""" database dependencies to support sqlite examples """
import json
from sqlalchemy.exc import IntegrityError
"""
These object and definitions are used throughout the Jupyter Notebook.
"""
# Setup of key Flask object (app)
app = Flask(__name__)
# Setup SQLAlchemy object and properties for the database (db)
database = 'sqlite:///sqlite.db' # path and filename of database
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = database
app.config['SECRET_KEY'] = 'SECRET_KEY'
db = SQLAlchemy()
# This belongs in place where it runs once per project
db.init_app(app)
class Car(db.Model):
__tablename__ = 'cars'
# Define the User schema with "vars" from object
id = db.Column(db.Integer, primary_key=True)
_eid = db.Column(db.String(100), unique=False, nullable=False)
_name = db.Column(db.String(255), unique=False, nullable=False)
_speed = db.Column(db.Integer, unique=False, nullable=False)
_cost = db.Column(db.Integer, unique=False, nullable=False)
def __init__(self, eid, name, speed, cost):
self._eid = eid
self._name = name
self._speed = speed
self._cost = cost
@property
def name(self):
return self._name
@name.setter
def name(self, name):
self._name = name
@property
def eid(self):
return self._eid
@eid.setter
def points(self, eid):
self._eid = eid
@property
def speed(self):
return self._speed
@speed.setter
def speed(self, speed):
self._speed = speed
@property
def cost(self):
return self._cost
@cost.setter
def cost(self, cost):
self._cost = cost
# output content using json dumps, this is ready for API response
def __str__(self):
return json.dumps(self.read())
def create(self):
try:
db.session.add(self)
db.session.commit()
return self
except IntegrityError:
db.session.remove()
return None
"""Database Creation and Testing """
# Builds working data for testing
def initEmp():
with app.app_context():
"""Create database and tables"""
db.create_all()
"""Tester data for table"""
# Input data for objects which are made from the template defined by 'User'
e1 = Car(eid = 'lambo', name='Lamborghini', speed='200', cost='1500000')
e2 = Car(eid = 'bugatti', name='Bugatti', speed='210', cost='2000000000')
e3 = Car(eid = 'ferrari', name='Ferrari', speed='200', cost='2500000')
e4 = Car(eid = 'porsche', name='Porsche', speed='205', cost='1000000')
cars = [e1, e2, e3, e4]
"""Builds sample player/note(s) data"""
for e in cars:
try:
'''add user to table'''
object = e.create()
print(f"Created new car {object.name}")
except: # error raised if object not created
'''fails with bad or duplicate data'''
print(f"Records exist name {e.name}, or error.")
initEmp()
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('cars')").fetchall()
# Print the results
for row in results:
print(row)
# Close the database connection
conn.close()
def create():
eid = input("Enter your car id")
name = input("Enter name of car")
speed = input('Top speed of car?')
cost = input("What is the cost?")
# 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 insert data into a table
cursor.execute("INSERT INTO cars (_eid, _name, _speed, _cost) VALUES (?, ?, ?, ?)", (eid, name, speed, cost))
# Commit the changes to the database
conn.commit()
print(f"A new user record {eid} has been created")
except sqlite3.Error as error:
print("Error while executing the INSERT:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
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 cars').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()
def update():
eid = input("Enter car id to update")
speed = input("Enter updated top speed")
cost = input("Enter updated average cost")
# 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 cars SET _speed = ?, _cost = ? WHERE _eid = ?", (speed, cost, eid))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No eid {eid} was not found in the table")
else:
print(f"The row with car id {eid} the speed/cost has been successfully updated")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the UPDATE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
def delete():
eid = input("Enter car id 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 car WHERE _eid = ?", (eid,))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No eid {eid} was not found in the table")
else:
# The uid was found in the table and the row was deleted
print(f"The row with eid {eid} was successfully deleted")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the DELETE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
options = [
('c', 'Create'),
('r', 'Read'),
('u', 'Update'),
('d', 'Delete'),
('s', 'Schema')
]
# 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 selected_option[0] == 's':
schema()
elif operation == '':
return
else:
print('Invalid option')
# Repeat the menu
menu()
menu()