MySQL for Python Developers
Sequence
Inserting guests and bookings
Sequence

Inserting data into a Flask application

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

Connect to the database and create a Cursor

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:

Python
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:

Python
cursor.close()

Check email and update guest name

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.

Python
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]
)

Create a new guest

If the email doesn't exist, we'll create a new guest and get their ID.

Python
else:
cursor.execute(
"INSERT INTO guest (name, email) VALUES (%s, %s)",
[request.form['name'], request.form['email']]
)
guest_id = cursor.lastrowid

Get room_type_id

Before inserting the booking information into the database, we need to get the room type ID based on the selected room type.

Python
cursor.execute("SELECT id FROM room_type WHERE code = %s", [request.form['room_type']])
room_type = cursor.fetchone()

Insert booking information

Finally, insert the booking information into the booking table and commit the changes to the database.

Python
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.

Conclusion

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.

About this lesson

Insert bookings into our hotel management application.

08:48
Closed captioned

Meet your instructor, Anthony Herbert

Software Developer

I'm a software developer and educator focused on helping others build their dream software projects.

Feedback or questions? Reach out to our team at education@planetscale.com.

By submitting your email, you agree to the processing of your personal information by PlanetScale as described in the Privacy Policy.