Database connection

An API usually exposes data coming from a database. In this section we will look at how we can make our API connect to a database and retrieve data from tables.

We will make our API according to best practices and seperate 'business logic' which is coded in our API endpoints from the database logic. This database logic will be kept in two files called database.py and festival_queries.py, the latter will be put in a folder called queries. So as of now, we will always have three files in our Python project:

• main.py contains our FastAPI endpoints with business logic
• database.py contains our database logic
• festival_queries.py contains the SQL queries used to retrieve information from our database (this file can be named differently in other projects)

The file structure of our project will now be as follows:

📂myproject
├── 📄database.py
├── 📄main.py
└── 📂queries
    └── 📄festival_queries.py

In the examples below, we will connect to our local MySQL instance and retrieve data from databases which are kept locally. In case you don't have MySQL Workbench on your device, follow the installation manual that can be found under Tools > MySQL Workbench. Later on in the course, we will create databases in a cloud environment and connect to those.

For the examples below, we will work with a small database about festivals, containing just one table. Before trying out the examples below, execute the create_festivaldb.sql file in your MySQL Workbench.

Building the database.py file

Create a new Python project and create the files mentioned above with the correct file structure (so the file festival_queries.py goes in a folder called queries).

Our database.py file will always contain the basic structure you can find below. The execute_sql_query function in this file will be used in our main.py file (see further down).

Our code will always start with an import of the mysql.connector library. This library provides us with functionality to connect to MySQL and execute SQL queries.

WARNING

Before we can use the mysql.connector library, we should first install it in our virtual environment using the following command:

pip install mysql-connector-python

TIP

Instead of installing the FastAPI and mysql.connector libraries seperately using two pip install commands, we can install them using one combined command:

pip install "fastapi[all]" mysql-connector-python

execute_sql_query(sql_query) function

The first thing we will do in our code is importing the aforementioned mysql.connector library.

import mysql.connector

def connect_to_database():
    try:
        connection = mysql.connector.connect(host='localhost', user='root', password='1234')
        return connection
    except mysql.connector.Error as error:
        print("Error connecting to database:", error)
        return error

def execute_sql_query(sql_query, query_parameters = None):
    connection = connect_to_database()
    result=''
    try:
        cursor = connection.cursor()
        cursor.execute(sql_query, query_parameters)
        if sql_query.upper().startswith("SELECT"):
            # executed for GET requests
            result = cursor.fetchall()
        else:
            # executed for POST requests
            connection.commit()
            result = True

        cursor.close()

    except mysql.connector.Error as exception:
        print("Error executing SQL query:", exception)
        result = exception


    finally:
        if connection.is_connected():
            connection.close()

        return result

After our import, we will create a function that contains generic code to connect to MySQL and return this connection to the code that was calling our function.

def connect_to_database():
    try:
        connection = mysql.connector.connect(host='localhost', user='root', password='1234')
        return connection
    except mysql.connector.Error as error:
        print("Error connecting to database:", error)
        return error

Within this function, a try-except structure in Python is used for handling exceptions. When something goes wrong in the try section, for example an error being thrown by the connection because of a wrong password, the except section will be executed to handle the exception. In our specific case, this exception will be printed to the console and will then be returned. When nothing goes wrong, only the try section will be executed and the connection will be returned.

The highlighted line in the code above establishes a connection to a MySQL database. The specified parameters (host, user, password) define the address and authentication credentials for accessing the database.

The next function in the database.py file looks as follows:

def execute_sql_query(sql_query, query_parameters = None):
    connection = connect_to_database()
    result=''
    try:
        cursor = connection.cursor()
        cursor.execute(sql_query, query_parameters)
        if sql_query.upper().startswith("SELECT"):
            # executed for GET requests
            result = cursor.fetchall()
        else:
            # executed for POST requests
            connection.commit()
            result = True

        cursor.close()

    except mysql.connector.Error as exception:
        print("Error executing SQL query:", exception)
        result = exception


    finally:
        if connection.is_connected():
            connection.close()

        return result

In the first line of this function, the connect_to_database() function is called to create a database connection. This connection will be stored in the connection variable.

Next, a try-except-finally structure in Python is used for handling exceptions and ensuring that certain code, regardless of whether an exception occurs or not, is executed.

This is how it works:

• Try block: This is the main body where the actual code is executed. Here, you try to execute the SQL query. Next, if the SQL query starts with SELECT, records will be retrieved from the database. In case the query does not start with SELECT, all actions are committed. This means that records that were added with an INSERT query for example will actually be inserted. The moment when the commit() function of our connection is executed, this will happen.

• Except block: If any exception occurs within the try block, it is caught here. In this case, if there is a mysql.connector.Error, the code within this block is executed, which prints an error message. After that, the exception will be put in the result variable.

• Finally block: This block is always executed, regardless of whether an exception occurred or not. Here, the code checks if the connection is still open, and if so, it closes the connection. Finally, it returns the result variable which contains either the result of our query or an exception.

This structure ensures that the connection is closed properly, even if an exception occurs during the execution of the SQL query.

try block

Let's break down the code and discuss how it works.

1. Cursor Creation:

cursor = connection.cursor()

A cursor is a control structure that enables traversal and manipulation of the result set obtained from a database query. In this case, a cursor is created to facilitate interaction with the database. It serves as an interface for executing SQL queries and managing the outcome.

2. SQL Query Execution:

cursor.execute(sql_query, query_parameters)

This line executes a predefined SQL query using the cursor. The sql_query variable holds the query, and execute() instructs the database to perform the associated operation. The query_parameters parameter can hold additional parameters that should be passed to the query. The Dynamic queries section of this course will give more information about this.

3. Record Retrieval:

result = cursor.fetchall()

The fetchall() method retrieves all the records returned by the executed SQL query. The variable result stores this data for further processing or analysis in the Python environment.

except block

Our except block merely prints the exception that is thrown. This code is only executed in case there is an error coming from MySQL.

finally block

The finally block is always executed, even when an error occurred and the except block was triggered/executed. Our finally block contains the following lines of code:

if connection.is_connected():
    connection.close()

Let's break this code down as well and look at the lines.

1. Connection Status Check:

if connection.is_connected():

Before closing a connection, it is essential to verify whether the connection is still active and established. This if statement checks if the connection to the MySQL database is currently open and available for interaction.

2. Connection Closure:

connection.close()

If the connection is confirmed to be open, this line proceeds to close the connection. Closing the connection is analogous to hanging up the phone after completing a conversation. It's a best practice to release resources when they are no longer needed.

SQL queries

We will keep our SQL queries in a separate folder called queries. Within that folder, we can keep our predefined SQL queries in Python files and keep queries seperated from each other based on the table that's being queried for example, or based on the functionality they're used for. In this example, we will have only one file called festival_queries.py within the queries folder, for simplicity.

Let's put two queries in our festival_queries.py file to start with:

festival_name_query = "SELECT name FROM festivaldb.festival;"

festival_name_and_province_query = "SELECT name, province FROM festivaldb.festival;"

Give the variables that hold your queries a logical name. It is normal that the names of these variables will be pretty long, certainly in comparison with 'normal' variables.

Note that the SQL query specifies the columns that should be retrieved instead of working with the * wildcard which retrieves all columns. Specifying the columns you want to retrieve has several advantages over using the * wildcard:

• Less columns get retrieved, so less data should pass over the network. This will make our query faster.
• We know exactly which columns will be retrieved. This makes our result predictable and makes it easier to get the right data from the right column.
• If a table contains sensitive or personally identifiable information (PII), retrieving only the necessary columns is a security best practice.

Using our database connection in main.py

Let's now create our main.py file and add some code in it. Add the following code to the file:

from fastapi import FastAPI
import database
from queries import festival_queries as queries

app = FastAPI()

@app.get("/festivals")
def get_all_festivals():
    query = queries.festival_name_query
    festivals = database.execute_sql_query(query)
    if isinstance(festivals, Exception):
        return festivals, 500
    festivals_to_return = []
    for festival in festivals:
        festivals_to_return.append(festival[0])
    return {'festivals': festivals_to_return}

The second line in this file (highlighted in the code above) imports the database.py file. By importing this file, we can now use the functions we created in it in our main.py file. The same happens with our SQL queries: we import the festival_queries.py file from the queries folder (third line in the code above) so we can use the variables in which our queries are stored in our main.py file. Note that because of the as queries part behind the import, we can address the imported file simply as queries.

Let's break down the code in the get_all_festivals() function.

query = queries.festival_name_query

The line shown above will retrieve the SQL query stored in the festival_name_query variable of the festival_queries.py file and put it in the variable named query so we can use the latter in our get_all_festivals() function.

festivals = database.execute_sql_query(query)

The line of code above will call the execute_sql_query() function in the database.py file and pass the SQL query (query variable) to this function. The result that is returned by that function is stored in the festivals variable. The content of the festivals variable will be a list of tuples or, in case MySQL threw an exception, the exception that was thrown.

if isinstance(festivals, Exception):
    return festivals, 500

These two lines are meant for error handling. In case an exception was given back by the execute_sql_query() function earlier on and the festivals variable contains that exception, an error 500 will be thrown.

festivals_to_return = []

With this line, we create an empty list which we will use later.

for festival in festivals:
    festivals_to_return.append(festival[0])

With the two lines above, we will iterate over the content of the festivals variable. Because festivals will contain a list, the content of festival will be one value of the list everytime we iterate over the festivals list. In the line that follows, we will take the first element of the festival and add it to the festivals_to_return list. The first element will be the name of the festival.

TIP

How do we know that the first element (so the element with index 0) is the name? Easy! It's the first (and in this case only) column we defined in our SQL statement:

SELECT name FROM festivaldb.festival;

return {'festivals': festivals_to_return}

At the end of our get_all_festivals() function, we return a dictionary containing one key (festivals) with festivals_to_return as value. So the value of festivals will be a list containing names of festivals.

Try it out yourself! If you address the /festivals endpoint, you should get the following result: