Texts: Hello Chegg, I need to use SQLAlchemy to do the following assignment. An expert previously answered this question, but this time I am asked to use SQLAlchemy for the whole thing. I need you to give me the code with the explanation.
Lab 10 Question: In Lab 10, you are asked to re-implement the address book application as described in Lab 9 using SQLAlchemy.
Lab 9 Question: Here is the code previously sent by an expert for Lab 9:
import mysql.connector # Connect to the MySQL database
cnx = mysql.connector.connect(user='your_username', password='your_password', database='your_database')
cursor = cnx.cursor()
def search_by_last_name(last_name):
query = "SELECT street_address, city, state, zip_code, active_phone_number "
"FROM people_master "
"INNER JOIN people_address ON people_master.person_id = people_address.person_id "
"INNER JOIN addresses ON people_address.address_id = addresses.address_id "
"WHERE person_name LIKE %s AND end_date IS NULL"
cursor.execute(query, (last_name + '%',))
results = cursor.fetchall()
if results:
print("Search Results:")
for result in results:
street_address, city, state, zip_code, phone_number = result
print("Address:", street_address, city, state, zip_code)
print("Phone Number:", phone_number)
print()
else:
print("No results found.")
def search_by_prefix(prefix):
query = "SELECT street_address, city, state, zip_code, active_phone_number "
"FROM people_master "
"INNER JOIN people_address ON people_master.person_id = people_address.person_id "
"INNER JOIN addresses ON people_address.address_id = addresses.address_id "
"WHERE person_name LIKE %s AND end_date IS NULL"
cursor.execute(query, (prefix + '%',))
results = cursor.fetchall()
if results:
print("Search Results:")
for result in results:
street_address, city, state, zip_code, phone_number = result
print("Address:", street_address, city, state, zip_code)
print("Phone Number:", phone_number)
print()
else:
print("No results found.")
def create_new_contact(name, dob, street_address, city, state, zip_code, phone_number):
# Check if the contact name already exists
query = "SELECT person_id FROM people_master WHERE person_name = %s"
cursor.execute(query, (name,))
existing_contact = cursor.fetchone()
if existing_contact:
# Update existing contact's address end date
update_query = "UPDATE people_address SET end_date = NOW() WHERE person_id = %s AND end_date IS NULL"
cursor.execute(update_query, (existing_contact[0],))
# Insert new address record
insert_address_query = "INSERT INTO addresses (street_address, city, state, zip_code) VALUES (%s, %s, %s, %s)"
cursor.execute(insert_address_query, (street_address, city, state, zip_code))
address_id = cursor.lastrowid
# Insert new people_address record
insert_people_address_query = "INSERT INTO people_address (person_id, address_id, start_date) VALUES (%s, %s, NOW())"
cursor.execute(insert_people_address_query, (existing_contact[0], address_id))
# Update existing contact's phone number
update_phone_query = "UPDATE people_master SET active_phone_number = %s WHERE person_id = %s"
cursor.execute(update_phone_query, (phone_number, existing_contact[0]))
cnx.commit()
print("Contact updated successfully.")
else:
# Insert new contact record
insert_contact_query = "INSERT INTO people_master (person_name, person_DOB, active_phone_number) "
"VALUES (%s, %s, %s)"
cursor.execute(insert_contact_query, (name, dob, phone_number))
contact_id = cursor.lastrowid
# Insert new address record
insert_address_query = "INSERT INTO addresses (street_address, city, state, zip_code) "
"VALUES (%s, %s, %s, %s)"
cursor.execute(insert_address_query, (street_address, city, state, zip_code))
address_id = cursor.lastrowid
# Insert new people_address record
insert_people_address_query = "INSERT INTO people_address (person_id, address_id, start_date) "
"VALUES (%s, %s, NOW())"
cursor.execute(insert_people_address_query, (contact_id, address_id))
cnx.commit()
print("Contact created successfully.")
def search_by_age(age_min, age_max):
query = "SELECT person_name, street_address, city, state, zip_code, active_phone_number "
"FROM people_master "
"INNER JOIN people_address ON people_master.person_id = people_address.person_id "
"INNER JOIN addresses ON people_address.address_id = addresses.address_id "
"WHERE YEAR(CURDATE()) - YEAR(person_DOB) BETWEEN %s AND %s AND end_date IS NULL"
cursor.execute(query, (age_min, age_max))
results = cursor.fetchall()
if results:
print("Search Results:")
for result in results:
name, street_address, city, state, zip_code, phone_number = result
print("Name:", name)
print("Address:", street_address, city, state, zip_code)
print("Phone Number:", phone_number)
print()
else:
print("No results found.")
def display_menu():
print("Address Book Tool")
print("1. Search current contact information by last name")
print("2. Search current contact information by name prefix")
print("3. Create new contact")
print("4. Search active contact information by age")
print("5. Quit")
# Main program loop
while True:
display_menu()
option = input("Select an option: ")
if option == '1':
last_name = input("Enter last name: ")
search_by_last_name(last_name)
elif option == '2':
prefix = input("Enter name prefix: ")
search_by_prefix(prefix)
elif option == '3':
name = input("Enter contact name: ")
dob = input("Enter date of birth (YYYY-MM-DD): ")
street_address = input("Enter street address: ")
city = input("Enter city: ")
state = input("Enter state: ")
zip_code = input("Enter ZIP code: ")
phone_number = input("Enter phone number: ")
create_new_contact(name, dob, street_address, city, state, zip_code, phone_number)
elif option == '4':
age_min = int(input("Enter minimum age: "))
age_max = int(input("Enter maximum age: "))
search_by_age(age_min, age_max)
elif option == '5':
break
else:
print("Invalid option. Please try again.")
# Close the database connection
cursor.close()
cnx.close()
Choose any
