import mysql.
connector
from mysql.connector import errorcode
from tabulate import tabulate
# MySQL connection config - update user and password as needed
DB_CONFIG = {
'host': 'localhost',
'user': 'root', # Change if you created a different MySQL user
'password': '', # Put your MySQL password here
}
DB_NAME = 'car_showroom'
OWNER_PASSWORD = 'prativyom1' # Updated owner password
def create_database(cursor):
try:
cursor.execute(f"CREATE DATABASE IF NOT EXISTS {DB_NAME} DEFAULT CHARACTER
SET 'utf8'")
except mysql.connector.Error as err:
print(f"Failed creating database: {err}")
exit(1)
def create_tables(cursor):
TABLES = {}
TABLES['cars'] = (
"CREATE TABLE IF NOT EXISTS cars ("
" id INT AUTO_INCREMENT PRIMARY KEY,"
" brand VARCHAR(50) NOT NULL,"
" model VARCHAR(50) NOT NULL,"
" type VARCHAR(50) NOT NULL,"
" price DECIMAL(10,2) NOT NULL,"
" stock INT NOT NULL"
") ENGINE=InnoDB"
)
TABLES['employees'] = (
"CREATE TABLE IF NOT EXISTS employees ("
" id INT AUTO_INCREMENT PRIMARY KEY,"
" name VARCHAR(100) NOT NULL,"
" salary DECIMAL(10,2) NOT NULL"
") ENGINE=InnoDB"
)
TABLES['payments'] = (
"CREATE TABLE IF NOT EXISTS payments ("
" id INT AUTO_INCREMENT PRIMARY KEY,"
" customer_name VARCHAR(100) NOT NULL,"
" car_id INT NOT NULL,"
" amount_paid DECIMAL(10,2) NOT NULL,"
" emi_left INT NOT NULL,"
" FOREIGN KEY (car_id) REFERENCES cars(id) ON DELETE CASCADE"
") ENGINE=InnoDB"
)
for table_name in TABLES:
table_description = TABLES[table_name]
try:
cursor.execute(table_description)
except mysql.connector.Error as err:
print(f"Error creating table {table_name}: {err}")
exit(1)
def insert_sample_data(cursor):
cursor.execute("SELECT COUNT(*) FROM cars")
if cursor.fetchone()[0] == 0:
cars_data = [
('Toyota', 'Corolla', 'Sedan', 20000.00, 10),
('Honda', 'Civic', 'Sedan', 22000.00, 8),
('Ford', 'F-150', 'Truck', 30000.00, 5),
('Tesla', 'Model 3', 'Electric', 35000.00, 7),
('BMW', 'X5', 'SUV', 50000.00, 3),
]
cursor.executemany(
"INSERT INTO cars (brand, model, type, price, stock) VALUES (%s, %s,
%s, %s, %s)",
cars_data
)
cursor.execute("SELECT COUNT(*) FROM employees")
if cursor.fetchone()[0] == 0:
employees_data = [
('Alice Johnson', 4000.00),
('Bob Smith', 3500.00),
('Charlie Lee', 3000.00),
]
cursor.executemany(
"INSERT INTO employees (name, salary) VALUES (%s, %s)",
employees_data
)
cursor.execute("SELECT COUNT(*) FROM payments")
if cursor.fetchone()[0] == 0:
payments_data = [
('John Doe', 1, 10000.00, 12),
('Jane Roe', 4, 20000.00, 6),
('Mark Twain', 2, 15000.00, 0),
]
cursor.executemany(
"INSERT INTO payments (customer_name, car_id, amount_paid, emi_left)
VALUES (%s, %s, %s, %s)",
payments_data
)
def owner_menu(cursor, cnx):
while True:
print("\n--- Owner Menu ---")
print("1. Add a car")
print("2. Update car stock")
print("3. View all payments")
print("4. View or update employee salaries")
print("5. Logout")
choice = input("Enter choice: ").strip()
if choice == '1':
add_car(cursor, cnx)
elif choice == '2':
update_car_stock(cursor, cnx)
elif choice == '3':
view_all_payments(cursor)
elif choice == '4':
view_update_employee_salaries(cursor, cnx)
elif choice == '5':
break
else:
print("Invalid choice. Try again.")
def add_car(cursor, cnx):
print("\nAdd a new car:")
brand = input("Brand: ").strip()
model = input("Model: ").strip()
car_type = input("Type: ").strip()
try:
price = float(input("Price: ").strip())
stock = int(input("Stock: ").strip())
except ValueError:
print("Invalid price or stock. Operation cancelled.")
return
try:
cursor.execute(
"INSERT INTO cars (brand, model, type, price, stock) VALUES (%s, %s,
%s, %s, %s)",
(brand, model, car_type, price, stock)
)
cnx.commit()
print("Car added successfully.")
except mysql.connector.Error as err:
print(f"Error adding car: {err}")
def update_car_stock(cursor, cnx):
print("\nUpdate car stock:")
cursor.execute("SELECT id, brand, model, stock FROM cars")
cars = cursor.fetchall()
if not cars:
print("No cars found.")
return
print(tabulate(cars, headers=['ID', 'Brand', 'Model', 'Stock'],
tablefmt='psql'))
try:
car_id = int(input("Enter car ID to update stock: ").strip())
new_stock = int(input("Enter new stock value: ").strip())
except ValueError:
print("Invalid input. Operation cancelled.")
return
cursor.execute("SELECT id FROM cars WHERE id = %s", (car_id,))
if cursor.fetchone() is None:
print("Car ID not found.")
return
try:
cursor.execute("UPDATE cars SET stock = %s WHERE id = %s", (new_stock,
car_id))
cnx.commit()
print("Stock updated successfully.")
except mysql.connector.Error as err:
print(f"Error updating stock: {err}")
def view_all_payments(cursor):
print("\nAll payments:")
query = (
"SELECT payments.id, payments.customer_name, cars.brand, cars.model,
payments.amount_paid, payments.emi_left "
"FROM payments JOIN cars ON payments.car_id = cars.id"
)
cursor.execute(query)
rows = cursor.fetchall()
if rows:
print(tabulate(rows, headers=['Payment ID', 'Customer', 'Car Brand', 'Car
Model', 'Amount Paid', 'EMI Left'], tablefmt='psql'))
else:
print("No payments found.")
def view_update_employee_salaries(cursor, cnx):
while True:
print("\nEmployee Salaries:")
cursor.execute("SELECT id, name, salary FROM employees")
employees = cursor.fetchall()
print(tabulate(employees, headers=['ID', 'Name', 'Salary'],
tablefmt='psql'))
print("\nOptions:")
print("1. Update an employee salary")
print("2. Back to Owner Menu")
choice = input("Enter choice: ").strip()
if choice == '1':
try:
emp_id = int(input("Enter employee ID to update salary: ").strip())
new_salary = float(input("Enter new salary: ").strip())
except ValueError:
print("Invalid input. Try again.")
continue
cursor.execute("SELECT id FROM employees WHERE id = %s", (emp_id,))
if cursor.fetchone() is None:
print("Employee ID not found.")
continue
try:
cursor.execute("UPDATE employees SET salary = %s WHERE id = %s",
(new_salary, emp_id))
cnx.commit()
print("Salary updated successfully.")
except mysql.connector.Error as err:
print(f"Error updating salary: {err}")
elif choice == '2':
break
else:
print("Invalid choice. Try again.")
def employee_menu(cursor):
while True:
print("\n--- Employee Menu ---")
print("1. View customer payments")
print("2. View employee salaries")
print("3. Logout")
choice = input("Enter choice: ").strip()
if choice == '1':
view_all_payments(cursor)
elif choice == '2':
view_employee_salaries(cursor)
elif choice == '3':
break
else:
print("Invalid choice. Try again.")
def view_employee_salaries(cursor):
print("\nEmployee Salaries:")
cursor.execute("SELECT id, name, salary FROM employees")
employees = cursor.fetchall()
if employees:
print(tabulate(employees, headers=['ID', 'Name', 'Salary'],
tablefmt='psql'))
else:
print("No employees found.")
def customer_menu(cursor):
while True:
print("\n--- Customer Menu ---")
print("1. Search cars by brand, budget, and type")
print("2. Logout")
choice = input("Enter choice: ").strip()
if choice == '1':
search_cars(cursor)
elif choice == '2':
break
else:
print("Invalid choice. Try again.")
def search_cars(cursor):
print("\nSearch for cars:")
brand = input("Enter brand (or leave blank to ignore): ").strip()
budget_input = input("Enter max budget (or leave blank to ignore): ").strip()
car_type = input("Enter car type (or leave blank to ignore): ").strip()
query = "SELECT id, brand, model, type, price, stock FROM cars WHERE stock > 0"
params = []
if brand:
query += " AND brand LIKE %s"
params.append(f"%{brand}%")
if budget_input:
try:
budget = float(budget_input)
query += " AND price <= %s"
params.append(budget)
except ValueError:
print("Invalid budget input. Ignoring budget filter.")
if car_type:
query += " AND type LIKE %s"
params.append(f"%{car_type}%")
cursor.execute(query, tuple(params))
cars = cursor.fetchall()
if cars:
print(tabulate(cars, headers=['ID', 'Brand', 'Model', 'Type', 'Price',
'Stock'], tablefmt='psql'))
else:
print("No matching cars found.")
def main():
try:
cnx = mysql.connector.connect(**DB_CONFIG)
cursor = cnx.cursor()
except mysql.connector.Error as err:
print(f"Error connecting to MySQL: {err}")
return
create_database(cursor)
cnx.database = DB_NAME
create_tables(cursor)
insert_sample_data(cursor)
cnx.commit()
while True:
print("\n=== Car Showroom Management System ===")
print("Select your role:")
print("1. Owner")
print("2. Employee")
print("3. Customer")
print("4. Exit")
role_choice = input("Enter choice: ").strip()
if role_choice == '1':
password = input("Enter owner password: ").strip()
if password == OWNER_PASSWORD:
owner_menu(cursor, cnx)
else:
print("Incorrect password.")
elif role_choice == '2':
employee_menu(cursor)
elif role_choice == '3':
customer_menu(cursor)
elif role_choice == '4':
print("Exiting program. Goodbye!")
break
else:
print("Invalid choice. Try again.")
cursor.close()
cnx.close()
if __name__ == '__main__':
main()
