MySQL for Python Developers
Sequence
Connecting to the PlanetScale database
Sequence

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:

Python
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:

Python
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.

Python
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.

About this lesson

In this lesson, you'll learn how to connect the Flask app to your MySQL PlanetScale database.

03:27
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.