Writing our first query in the Flask app

In this lesson, we'll walk through the process of writing our first database query inside the Flask app. This query will fetch the available room types in our hotel management system.

If you remember from the previous lesson, we set up the database connection inside a getDB function. Now, we're going to make use of that function to write and execute our query.

Fetching room types

To demonstrate the process, we'll be working on the getRoomType function inside the routes.py file. The purpose of this function is to make the available room types accessible within every template of our hotel management app.

The context_processor decorator ensures that the result of this function is available in every template. To give you an idea of what it looks like, consider the following example from the single.html file:

{% for type in room_types %}
    <option value="{{ type.code }}"
        {% if type.code == booking.code %} selected {% endif %}>
        {{ type.description }}
    </option>
{% endfor %}

Here, the room_types variable is automatically inserted by the context processor, and then looped through to display the room types as options in a dropdown menu.

Writing the database query

Now, let's get back to routes.py and write the database query inside our getRoomType function.

First, we need to obtain the database connection:

db = getDB()

Then, we want to open a cursor, enabling us to interact with the database:

cursor = db.cursor(dictionary=True)

With the cursor ready, we can now execute our query:

cursor.execute("SELECT code, description FROM room_type")

Replace the existing empty list with the results from the query:

room_types = cursor.fetchall()

The room_types variable will now be a list of dictionaries, as we set dictionary=True when initializing the cursor.

Finally, don't forget to close the cursor after fetching the results:

cursor.close()

The modified getRoomType function will look like this:

@app.context_processor
def getRoomType():
    db = getDB()
    cursor = db.cursor(dictionary=True)
    cursor.execute("SELECT code, description FROM room_type")
    room_types = cursor.fetchall()
    cursor.close()
    return {"room_types": room_types}

Testing the query and database connection

To see if our query and database connection are working as expected, let's start the Flask app by running:

flask run

If there are no errors, open up the app in the browser. An error like this might be displayed if the database connection isn't working:

KeyError: 'DB_USER'

In case the error occurs, something in your configuration isn't set correctly. In this example, the error can be resolved by changing the configuration key from DB_USER to DB_USERNAME.

If the error persists, double-check your configuration settings. Once you refresh the page and everything loads correctly, you'll know that your database connection is working and your query is functioning as intended.

Wrapping up

In this lesson, we demonstrated how to write and execute our first query in a Flask app. In the next tutorial, we'll move forward and create queries for fetching details about the current bookings and reservations within our hotel management system. Stay tuned!