In this lesson, we are going to create a route to add new bookings in our booking system. Our goal is to fill out a form, submit it, and have all the data saved to the database. We'll work on the following parts:
- Connect to the database and create a cursor
- Determine if the guest email exists and update the name if needed
- Create a new guest if the email doesn't exist
- Get the ID of the room type selected
- Insert the booking information into the database
Let's begin by connecting to our database. First, import the get_db function from our project. Then, create a cursor that returns dictionary results:
from project_name.db import get_db
db = get_db()
cursor = db.cursor(dictionary=True)
Remember to close the cursor at the end of your code:
cursor.close()
Next, we'll write a query to determine if the email already exists in the system. If it does, we'll update the guest's name and retrieve their ID.
cursor.execute("SELECT id, name FROM guest WHERE email = %s", [request.form['email']])
guest = cursor.fetchone()
if guest:
guest_id = guest['id']
if request.form['name'] != guest['name']:
cursor.execute(
"UPDATE guest SET name = %s WHERE id = %s",
[request.form['name'], guest_id]
)
If the email doesn't exist, we'll create a new guest and get their ID.
else:
cursor.execute(
"INSERT INTO guest (name, email) VALUES (%s, %s)",
[request.form['name'], request.form['email']]
)
guest_id = cursor.lastrowid
Before inserting the booking information into the database, we need to get the room type ID based on the selected room type.
cursor.execute("SELECT id FROM room_type WHERE code = %s", [request.form['room_type']])
room_type = cursor.fetchone()
Finally, insert the booking information into the booking table and commit the changes to the database.
cursor.execute("""
INSERT INTO booking
(check_in, check_out, guest_id, reference_number, breakfast, airport_pickup_time, room_type_id)
VALUES (%s, %s, %s, %s, %s, %s, %s)
""",
[
request.form['check_in'],
request.form['check_out'],
guest_id,
reference_number,
bool(request.form.get('breakfast')),
request.form['airport_pickup_time'],
room_type['id']
]
)
db.commit()
Now when you submit the form with the required information, the new booking will be added to the database, and you'll be able to see the booking details in your system.
In this lesson, we've created a route to insert new bookings into our booking system. We connected to the database, checked if a guest email exists and updated the name, created a new guest when the email doesn't exist, got the ID for the selected room type, and inserted booking information into the database. In future lessons, we'll explore how to display detailed information for each booking in our system.