If you’re running a restaurant, having an efficient billing system is crucial to keeping your customers happy and your business running smoothly. In this tutorial, we’ll show you how to build a simple restaurant billing system using Python and MySQL.
Before we get started, make sure you have Python and MySQL installed on your computer. You’ll also need to create a new MySQL database for this project.
Step 1: Import the necessary modules
To get started, we’ll need to import the mysql.connector module, which allows Python to communicate with a MySQL database.
import mysql.connector
Step 2: Connect to the MySQL database
Next, we’ll establish a connection to the MySQL database using the connect() function. Replace "yourusername" and "yourpassword" with your own MySQL username and password, respectively.
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="restaurant"
)
Step 3: Create a cursor object
Once we’ve connected to the database, we’ll create a cursor object using the cursor() method. This allows us to execute SQL commands.
mycursor = mydb.cursor()
Step 4: Create a menu table
Next, we’ll create a table called “menu” for menu items using the execute() method. This table will have three columns: item_id, item_name, and item_price.
mycursor.execute("CREATE TABLE menu (item_id INT AUTO_INCREMENT PRIMARY KEY, item_name VARCHAR(255), item_price DECIMAL(10, 2))")
Step 5: Add menu items to the table
Now that we have our menu table, we’ll add some menu items to it using the executemany() method. The menu_items list contains tuples of menu item names and prices.
menu_items = [
("Burger", 10.99),
("Fries", 3.99),
("Salad", 8.99),
("Pizza", 12.99),
("Soda", 1.99)
]
mycursor.executemany("INSERT INTO menu (item_name, item_price) VALUES (%s, %s)", menu_items)
Step 6: Create an orders table
Next, we’ll create a table called “orders” for storing orders using the execute() method. This table will have four columns: order_id, item_name, item_price, and quantity.
mycursor.execute("CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY, item_name VARCHAR(255), item_price DECIMAL(10, 2), quantity INT)")
Step 7: Prompt the user for their order
Now that we have our menu and orders tables set up, we can prompt the user to enter the items they want to order and their quantities using a while loop. For each item entered by the user, we’ll check if it exists on the menu using a SELECT command with a WHERE clause. If the item is found, we’ll calculate the total price for the order and insert the order into the orders table using the execute() method.
while True:
item = input("Enter item name (type 'done' to finish ordering): ")
if item == "done":
break
quantity = int(input("Enter quantity: "))# check if item exists on menu
mycursor.execute("SELECT item_name, item_price FROM menu WHERE item_name = %s", (item,))
result = mycursor.fetchone()
if result is None:
print(f"{item} is not on the menu. Please enter a valid item.")
else:
item_name = result[0]
item_price = result[1]
total_price = item_price * quantity
print(f"{item_name} x {quantity} = ${total_price:.2f}")
# insert order into orders table
order = (item_name, item_price, quantity)
mycursor.execute("INSERT INTO orders (item_name, item_price, quantity) VALUES (%s, %s, %s)", order)
mydb.commit()
Step 8: Calculate and display the total order price
Once the user has finished entering their order, we can calculate the total order price using the SUM() function in a SELECT command. We’ll then use the fetchone() method to retrieve the result and print it out.
mycursor.execute("SELECT SUM(item_price * quantity) FROM orders")
result = mycursor.fetchone()
total_price = result[0]
print(f"Your total order price is: ${total_price:.2f}")
Step 9: Close the database connection
Finally, we’ll close the database connection using the close() method.
mydb.close()
And that’s it! You now have a simple restaurant billing system that allows you to enter menu items and quantities for an order, calculates the total order price, and stores the order in a MySQL database.
In a real-world restaurant billing system, you would likely need additional functionality such as support for multiple tables, the ability to split bills, and the ability to print receipts. But this project provides a good starting point for building a more complex system.
If you’d like to improve this project further, you could try adding a login system for employees, adding support for different currencies, or implementing a graphical user interface (GUI) using a library like Tkinter or PyQt.
I hope you found this tutorial helpful!
Complete source code for simple restaurant billing system using python and MySQL
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="123456",
database="restaurant_bill"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE menu (item_id INT AUTO_INCREMENT PRIMARY KEY, item_name VARCHAR(255), item_price DECIMAL(10, 2))")
menu_items = [
("Burger", 10.99),
("Fries", 3.99),
("Salad", 8.99),
("Pizza", 12.99),
("Soda", 1.99)
]
mycursor.executemany("INSERT INTO menu (item_name, item_price) VALUES (%s, %s)", menu_items)
mycursor.execute("CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY, item_name VARCHAR(255), item_price DECIMAL(10, 2), quantity INT)")
while True:
item = input("Enter item name (type 'done' to finish ordering): ")
if item == "done":
break
quantity = int(input("Enter quantity: "))
# check if item exists on menu
mycursor.execute("SELECT item_name, item_price FROM menu WHERE item_name = %s", (item,))
result = mycursor.fetchone()
if result is None:
print(f"{item} is not on the menu. Please enter a valid item.")
else:
item_name = result[0]
item_price = result[1]
total_price = item_price * quantity
print(f"{item_name} x {quantity} = ${total_price:.2f}")
# insert order into orders table
order = (item_name, item_price, quantity)
mycursor.execute("INSERT INTO orders (item_name, item_price, quantity) VALUES (%s, %s, %s)", order)
mydb.commit()
mycursor.execute("SELECT SUM(item_price * quantity) FROM orders")
result = mycursor.fetchone()
total_price = result[0]
print(f"Your total order price is: ${total_price:.2f}")
mydb.close()
Output:
Conclusion
In this mini project, we built a simple restaurant billing system using Python and MySQL. We implemented features for entering menu items and quantity orders and calculating total price of orders. We also built a simple command line interface to tie everything together. While this restaurant billing system is quite simple, it demonstrates the power of Python in building real-world applications.