Accessing a MySQL Database in Flask

Installing the Flask MySQL Library

Before you can access a MySQL database from within your Flask program, you will need to install the Flask-MySQLdb library with pip. To do so, use the following command either in a venv virtual environment (recommended) or as root (not-so recommended):

pip install flask-mysqldb

Configuring the MySQL Database in Flask

At the top of your Flask program, you will need to import the mysqldb library:

from flask_mysqldb import MySQL

Then, you will need to set your MySQL parameters:

app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'db_username_here'
app.config['MYSQL_PASSWORD'] = 'db_password_here'
app.config['MYSQL_DB'] = 'database_name_here'

Of course, I am assuming that your MySQL service is on the same machine as your Flask program. If not, you will need to change localhost to your database service host name.

Accessing the MySQL Database in Flask

To perform queries in your Flask program, you need to set up the database connection first:

cur = mysql.connection.cursor()

Then, you can perform your SQL queries. For example, below I am querying a user/password table called users in a database. If there is a match with the variables userName and password in the users table, then a userID will be returned:

cur.execute("SELECT user_id FROM users WHERE user_name = %s AND user_pass = %s", [userName, password])
row = cur.fetchone()
if row:
    userID = row[0]
else:
    print("Username and/or password incorrect.")

Note the square brackets around the Python variable names userName and password to prevent sql code injection.