Connecting to the MySQL database in a Flask app

In the previous parts of this section, we set up the starting files for our Flask app, and created our MySQL database. Now, it's time to connect our app to the database. In this lesson, we will cover how to write a getdb function to connect to the MySQL database and close the connection after each request.

Step 1: Create the getdb function

We are going to write the connection code once in the getdb function and return the database connection anytime it's needed in our routes. To do this, open the db.py file and locate the getdb function. Now, let's connect to the database and assign the connection to g.db so we can return it later.

To connect to the MySQL database, use the mysql.connector.connect function with the following configuration values:

• Host: current_app.config['DB_HOST']
• User: current_app.config['DB_USER']
• Password: current_app.config['DB_PASSWORD']
• Database: current_app.config['DB_DATABASE']
• SSL settings: ssl_verify_identity=True, ssl_ca='path/to/SSL/certs/ca-cert.pem'

Here's the code to connect to the MySQL database:

from flask import current_app, g
import mysql.connector


def getdb():
    if 'db' not in g or not g.db.is_connected():
        g.db = mysql.connector.connect(
            host=current_app.config['DB_HOST'],
            user=current_app.config['DB_USER'],
            password=current_app.config['DB_PASSWORD'],
            database=current_app.config['DB_DATABASE'],
            ssl_verify_identity=True,
            ssl_ca='SSL/certs/ca-cert.pem'
        )
    return g.db

This code will first check if the database connection exists in g and if it's connected. If it doesn't exist or it's not connected, it will create a new connection using the configuration values from the app.

Step 2: Close the database connection after each request

To close the database connection after each request, we will define a function and pass it to the teardown_appcontext decorator. Flask will then automatically execute this code every time a request ends.

Here's the code to close the database connection:

from flask import current_app, g
import mysql.connector
from flask import current_app


def close_db(e=None):
    db = g.pop('db', None)


    if db is not None and db.is_connected():
        db.close()


current_app.teardown_appcontext(close_db)

This code will first get the database connection from g and then call the db.close() method to close the connection if it's not None and it's connected.

Step 3: Use the getdb function in routes

Now, to use the database connection in our routes, we can simply call the getdb function, which will return the connection.

from flask import Flask
from . import db


app = Flask(__name__)


@app.route('/')
def home():
    connection = db.getdb()
    # Use the connection to interact with the database, e.g., execute queries

In the next lesson, we will write the first SQL query for our app and test if the database connection is working as expected.