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.
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:
- SSL settings:
Here's the code to connect to the MySQL database:
from flask import current_app, gimport mysql.connectordef 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.
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, gimport mysql.connectorfrom flask import current_appdef 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.
Now, to use the database connection in our routes, we can simply call the
getdb function, which will return the connection.
from flask import Flaskfrom . import dbapp = 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.