Categories: Python Flask
Tags:

Connecting Flask to a MySQL Database — A Beginner’s Complete Guide

Web development can feel overwhelming when you’re just starting out, especially when you need to connect a beautiful frontend to a real database. But here’s the good news — Flask, the lightweight Python web framework, makes it incredibly simple. Combined with MySQL, one of the world’s most popular relational databases, you can build powerful, data-driven web applications in just a few lines of code.

In this tutorial, we’ll walk you through creating a basic Flask application that connects to the famous Northwind sample database. Northwind is a classic dataset used by developers worldwide to practice SQL queries, and it contains tables like Products, Customers, Orders, and more. By the end of this guide, you’ll have a fully working Flask app with a /products route that fetches live data from MySQL and displays it in a clean, styled HTML table.

We’ll cover everything step by step — from installing the required packages (flask and mysql-connector-python), configuring your database connection, writing the Python code, and finally rendering the results on a web page. You’ll also learn best practices like using connection helpers, handling errors gracefully, and styling your output with basic CSS.

Whether you’re a student, a beginner developer, or someone exploring backend development for the first time, this tutorial is designed to be simple, practical, and easy to follow. No advanced concepts, no confusing jargon — just clean code and clear explanations.

So grab your favorite code editor, fire up your MySQL server, and let’s build something awesome together. By the time you finish, you’ll have a solid foundation to expand into more complex Flask applications with full CRUD operations, search filters, authentication, and beyond. Let’s get started!

Follow the video tutorial and then use these commands as shown in the video:

Adding a /products Route to Your Flask App

You need a MySQL connector library. Install it first:

pip install flask mysql-connector-python

Updated app.py

from flask import Flask
import mysql.connector
from mysql.connector import Error

app = Flask(__name__)

# ---------- Database config ----------
DB_CONFIG = {
    "host": "localhost",
    "user": "root",          # <-- change to your MySQL username
    "password": "",          # <-- change to your MySQL password
    "database": "northwind"
}


def get_products():
    """Fetch all rows from the products table."""
    connection = mysql.connector.connect(**DB_CONFIG)
    try:
        cursor = connection.cursor(dictionary=True)   # rows as dicts
        cursor.execute("SELECT ProductID, ProductName, SupplierID, CategoryID, Unit, Price FROM products")
        rows = cursor.fetchall()
        return rows
    finally:
        cursor.close()
        connection.close()


# ---------- Routes ----------
@app.route("/")
def home():
    return """
    <html>
    <head>
        <title>My First Flask App</title>
    </head>
    <body>
        <h1>Hello World! ye Meri app hai</h1>
        <p>Flask is running successfully.</p>
        <p>👉 Go to <a href="/products">/products</a> to see the product list.</p>
    </body>
    </html>
    """


@app.route("/products")
def products():
    try:
        rows = get_products()
    except Error as e:
        return f"<h2>Database error:</h2><pre>{e}</pre>", 500

    # Build HTML table rows
    table_rows = ""
    for r in rows:
        table_rows += f"""
        <tr>
            <td>{r['ProductID']}</td>
            <td>{r['ProductName']}</td>
            <td>{r['SupplierID']}</td>
            <td>{r['CategoryID']}</td>
            <td>{r['Unit']}</td>
            <td>{r['Price']}</td>
        </tr>
        """

    return f"""
    <html>
    <head>
        <title>Products - Northwind</title>
        <style>
            body  {{ font-family: Arial, sans-serif; margin: 30px; }}
            table {{ border-collapse: collapse; width: 100%; }}
            th, td {{ border: 1px solid #ddd; padding: 8px; text-align: left; }}
            th    {{ background: #4CAF50; color: white; }}
            tr:nth-child(even) {{ background: #f2f2f2; }}
        </style>
    </head>
    <body>
        <h1>🛒 Products from Northwind</h1>
        <p>Total products: <b>{len(rows)}</b></p>
        <table>
            <tr>
                <th>ID</th>
                <th>Product Name</th>
                <th>Supplier</th>
                <th>Category</th>
                <th>Unit</th>
                <th>Price</th>
            </tr>
            {table_rows}
        </table>
        <p><a href="/">← Back home</a></p>
    </body>
    </html>
    """


if __name__ == "__main__":
    app.run(host="0.0.0.0", port=5000, debug=True)

Run it

python app.py

Then open: http://localhost:5000/products

Important notes

  1. Change user and password in DB_CONFIG to match your MySQL credentials.
  2. The Northwind schema uses table name Products (capital P) on some installs and products on others. If you get a “Table doesn’t exist” error, try:SHOW TABLES; in MySQL to see the exact name, then adjust the SQL query.
  3. dictionary=True makes rows come back as dictionaries (r['ProductName']) instead of tuples — much cleaner.
  4. try/finally ensures the connection closes even if the query fails.

Optional: Using a single connection pool (better for production)

For repeated queries, reuse connections:

from mysql.connector import pooling

pool = pooling.MySQLConnectionPool(
    pool_name="flask_pool",
    pool_size=5,
    **DB_CONFIG
)

def get_products():
    conn = pool.get_connection()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM products")
    rows = cursor.fetchall()
    cursor.close()
    conn.close()  # returns to pool
    return rows