Connecting a Python Flask Application to a MySQL Database

When a web application built with Flask needs persistent data storage, MySQL is the most logical choice. Unlike PHP, where we establish a connection directly, in Python we typically use a library such as mysql-connector-python.

Installing Required Tools

Before starting, we must install the MySQL driver in the Python environment. We do this with a simple command in the terminal:

pip install mysql-connector-python flask

Code Example for Reading from Database

The example below shows how Flask opens a connection to the database, retrieves data, and sends it for processing:

import mysql.connector
from flask import Flask

app = Flask(__name__)

def get_data():
    connection = mysql.connector.connect(
        host="localhost",
        user="username",
        password="password",
        database="my_blog"
    )
    cursor = connection.cursor(dictionary=True)
    cursor.execute("SELECT title, content FROM posts")
    results = cursor.fetchall()
    connection.close()
    return results

@app.route("/blog")
def show_blog():
    posts = get_data()
    # Here we would send data to the HTML template
    return f"Number of posts in database: {len(posts)}"

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

Advantages of Using Python for Backend

• Data Processing: Python allows the use of libraries like Pandas for advanced statistics on data from the database.
• Automation: Easy creation of scripts that check the database at specific times and send reports.
• Security: Flask allows the use of ORM tools (e.g., SQLAlchemy), which further protect the database against attacks.

Using Python on the web does not mean we have to give up classic HTML. Flask allows us to maintain full control over the visual part, while Python in the background handles the logic.