Skip to content
Join the waitlist for our new database scaling course
1.5
Queries for the main page
1.7

Using JOIN in SQL queries in Flask

In this lesson, we'll learn how to write a query to generate a list of bookings or reservations in our hotel booking system. We'll also explore how to incorporate multiple joins in the query to gather information from different tables for a more in-depth result.

Setting up the query

To begin, let's head over to the index route in our code. Here, we have an empty list called bookings. This will eventually become the list of dictionaries that represents the results from the database.

First, we need to get the database and create a cursor:

Python
db = get_db()
cursor = db.cursor(dictionary=True)

Now, let's close the cursor underneath the bookings variable:

Python
cursor.close()

With everything set up, it's time to write the actual query.

Python
cursor.execute("""
SELECT
booking.reference_number,
guest.name,
booking.check_in,
booking.check_out,
room_type.description,
booking.airport_pickup_time,
booking.breakfast
FROM
booking
JOIN
guest ON booking.guest_id = guest.id
JOIN
room_type ON booking.room_type_id = room_type.id;
""")

In the multi-line string provided above, notice that we first specify the columns needed to complete our list (which include reference_number, guest_name, check_in and check_out dates, room_type.description, airport_pickup_time, and breakfast_details), and then we write the JOIN statements.

Here, we are joining two tables: the guest and room_type tables. By joining the tables, we can access additional information about the guests who made a booking as well as the room type they booked.

Returning and displaying the results

Now that the query is in place, let's update the bookings variable below it:

Python
bookings = cursor.fetchall()

The fetchall() function returns a list of dictionaries, which will then be passed to the template.

Let's now check if our list of reservations is generated as expected. Refresh the page in your browser or web-server to see the list of bookings.

Breaking down the template

To understand how the generated list is displayed on the page, let's look at an example in the list template. The template includes a loop that goes through the bookings variable.

Each item in the loop represents a row in the database, which is a dictionary. The template then references the different columns using the item:

Python
{% for item in bookings %}
<tr>
<td>{{ item.reference_number }}</td>
<td>{{ item.name }}</td>
<td>{{ item.check_in }}</td>
<td>{{ item.check_out }}</td>
<td>{{ item.description }}</td>
<td>{% if item.breakfast %}Yes{% else %}No{% endif %}</td>
<td>{{ item.airport_pickup_time or '' }}</td>
</tr>
{% endfor %}

For the breakfast field, we use an if statement to display Yes if the value is true and No if the value is false. For the airport_pickup_time, we display the time if there is a value, and nothing if it's empty.

Wrapping up

And that's it for the index page, or the home page of the hotel booking system! In the next part, we will work on the route that allows us to create new bookings in our system and save them to the database.

About this lesson

Query the database, loop through the data, and display the results.

4:12
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.