How to Connect Flask to MySQL: Simple Guide with Example

To connect Flask to MySQL, use the flask-mysql-connector package to configure your database connection in your Flask app. Initialize the MySQL client with your database credentials and use it to execute queries inside your routes.

Syntax

Here is the basic syntax to connect Flask to MySQL using flask-mysql-connector:

• app.config: Set your MySQL host, user, password, and database name.
• MySQL(app): Initialize the MySQL client with your Flask app.
• mysql.connection.cursor(): Create a cursor to execute SQL queries.
• cursor.execute(): Run your SQL commands.
• cursor.fetchall(): Fetch query results.

python

from flask import Flask
from flask_mysql_connector import MySQL

app = Flask(__name__)

app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'your_username'
app.config['MYSQL_PASSWORD'] = 'your_password'
app.config['MYSQL_DATABASE'] = 'your_database'

mysql = MySQL(app)

@app.route('/')
def index():
    cursor = mysql.connection.cursor()
    cursor.execute('SELECT * FROM your_table')
    results = cursor.fetchall()
    cursor.close()
    return str(results)

Example

This example shows a simple Flask app connecting to a MySQL database and fetching all rows from a table called users. It returns the results as a string in the browser.

python

from flask import Flask
from flask_mysql_connector import MySQL

app = Flask(__name__)

app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'password123'
app.config['MYSQL_DATABASE'] = 'testdb'

mysql = MySQL(app)

@app.route('/')
def index():
    cursor = mysql.connection.cursor()
    cursor.execute('SELECT id, name FROM users')
    users = cursor.fetchall()
    cursor.close()
    return '<br>'.join([f"User {user[0]}: {user[1]}" for user in users])

if __name__ == '__main__':
    app.run(debug=True)

Output

User 1: Alice<br>User 2: Bob<br>User 3: Carol

Common Pitfalls

• Missing or wrong credentials: Ensure your MySQL username, password, host, and database name are correct.
• Not closing cursor: Always close the cursor after queries to avoid resource leaks.
• MySQL server not running: Make sure your MySQL server is running and accessible.
• Using wrong package: Use flask-mysql-connector or similar modern packages, not deprecated ones like flask-mysqldb.

python

## Wrong way (missing cursor close):
# cursor = mysql.connection.cursor()
# cursor.execute('SELECT * FROM users')
# results = cursor.fetchall()
# return str(results)

## Right way:
# cursor = mysql.connection.cursor()
# cursor.execute('SELECT * FROM users')
# results = cursor.fetchall()
# cursor.close()
# return str(results)

Quick Reference

Remember these key steps to connect Flask to MySQL:

• Install flask-mysql-connector with pip install flask-mysql-connector.
• Configure your database credentials in app.config.
• Initialize MySQL with mysql = MySQL(app).
• Use a cursor to execute queries and always close it.
• Run your Flask app and test database queries in routes.

Key Takeaways

Use flask-mysql-connector to easily connect Flask with MySQL databases.

Always configure your database credentials correctly in app.config.

Create and close cursors properly to manage database resources.

Test your MySQL server connection before running Flask queries.

Avoid deprecated packages and use modern, maintained connectors.