Instructions
Requirements and Specifications
Source Code
import mysql.connector
"""
In the following THREE lines, define the username, password and database name
"""
USERNAME = "root"
PASSWORD = "root"
DATABASE = "adede_db"
def get_string(message) -> str:
"""
This function will ask user for a non-empty string. The functions keeps re-prompting until user enters
a valid string
:param message: message to be displayed to user when asking for input
:return: string
"""
while True:
string = input(message)
if len(string) > 0:
return string
else:
print("Please enter a non-empty string.")
def get_integer(message) -> int:
"""
This function will ask user for a positive integer. The functions keeps re-prompting until user enters
a valid integer
:param message: message to be displayed to user when asking for input
:return: int
"""
while True:
try:
integer = int(input(message))
if integer > 0:
return integer
else:
print("Please enter a positive number.")
except:
print("Please enter a valid number.")
def menu():
"""
This function displays the menu and gets an integer input from user
The function keeps asking for an input until the user enters a valid option
:return: string (menu option)
"""
while True:
print("MENU")
print("a - Add car")
print("d - Remove car")
print("u - Update car details")
print("r1 - Output all cars sorted by year (ascending)")
print("r2 - Output all cars of a certain color")
print("q - Quit")
# Define valid menu options
menu_options = ["a", "d", "u", "r1", "r2", "q"]
# Get option
option = input("Enter choice: ")
if option in menu_options:
return option
else:
print("Invalid option!")
if __name__ == '__main__':
# The first step is to create the connection to the database
try:
print("Connecting to MySQL... ", end="")
connection = mysql.connector.connect(
host = "localhost",
user = USERNAME,
password = PASSWORD,
database = DATABASE
)
if connection.is_connected():
print(f"successfully connected as {USERNAME} to the database {DATABASE}!")
cursor = connection.cursor()
# Begin with program
running = True
while running:
# Display menu
option = menu()
if option == "a": # Add car
# Ask user for make, model, year and color
make = get_string("Enter maker: ")
model = get_string("Enter model: ")
year = get_integer("Enter year: ")
color = get_string("Enter color: ")
# Now, insert into db
query_str = f"INSERT INTO garage (make, model, year, color) VALUES (\"{make}\", \"{model}\", {year}, \"{color}\")"
# Insert
cursor.execute(query_str)
connection.commit()
print(f"The following car has been added to the garage: make = {make}, model = {model}, year = {year}, color = {color}")
elif option == "d": # Remove car
car_id = get_integer("Please enter the id of the car to remove: ")
# CHeck if there is a car with that id
cursor.execute(f"SELECT * FROM garage WHERE id = {car_id};")
result = cursor.fetchall()
if len(result) > 0:
# Delete
cursor.execute(f"DELETE FROM garage WHERE id = {car_id}")
connection.commit()
print(f"Car with id {car_id} has been removed.")
else:
print("There is no car with that id in the garage.")
elif option == "u":
car_id = get_integer("Please enter the id of the car to remove: ")
# CHeck if there is a car with that id
cursor.execute(f"SELECT * FROM garage WHERE id = {car_id};")
result = cursor.fetchall()
if len(result) > 0:
# Ask for new details
new_make = get_string("Enter new maker: ")
new_model = get_string("Enter new model: ")
new_year = get_integer("Enter new year: ")
new_color = get_string("Enter new color: ")
cursor.execute(f"UPDATE garage SET make = \"{new_make}\", model =\"{new_model}\", year = {new_year}, color = \"{new_color}\" WHERE id = {car_id};")
connection.commit()
print(f"Car with id {car_id} has been updated.")
else:
print("There is no car with that id in the garage.")
elif option == "r1": # Output all cars sorted by year ascending
cursor.execute("SELECT * FROM garage ORDER BY year ASC;")
result = cursor.fetchall()
if len(result) > 0:
print("{:<10s} {:>10s} {:>15s} {:>10s} {:>10s}".format("ID", "Make", "Model", "Year", "Color"))
print("{:<10s} {:>10s} {:>15s} {:>10s} {:>10s}".format("--", "----", "----", "----", "----"))
for row in result:
print("{:<10d} {:>10s} {:>15s} {:>10d} {:>10s}".format(row[0], row[1], row[2], row[3],
row[4]))
else:
print("There are no cars in the garage.")
elif option == "r2": # Output all cars of certain color
# Ask color
color = get_string("Enter color: ")
cursor.execute(f"SELECT * FROM garage WHERE color = \"{color}\";")
result = cursor.fetchall()
if len(result) > 0:
print("{:<10s} {:>10s} {:>15s} {:>10s} {:>10s}".format("ID", "Make", "Model", "Year", "Color"))
print("{:<10s} {:>10s} {:>15s} {:>10s} {:>10s}".format("--", "----", "----", "----", "----"))
for row in result:
print("{:<10d} {:>10s} {:>15s} {:>10d} {:>10s}".format(row[0], row[1], row[2], row[3], row[4]))
else:
print("There are no cars in the garage with that color.")
elif option == "q": #quit
print("Good bye!")
running = False
print()
except mysql.connector.Error as e:
print("failed!")
print(e)
finally:
if connection.is_connected():
cursor.close()
connection.close()