How to Use MySQL Database with Python

I’ve written a lot of Python code that talks to MySQL — everything from small ETL scripts to Django apps serving millions of requests. Python’s simplicity combined with MySQL’s reliability is one of my favorite combos for backend work and data engineering alike. In this guide, I’m going to walk you through connecting Python to MySQL the right way, from your very first SELECT statement to the kind of connection pooling and transaction handling I use in production systems.

Why Python and MySQL Work So Well Together

Python’s DB-API 2.0 specification standardizes how database drivers behave, so once you learn the pattern for MySQL, it transfers to Postgres, SQLite, and others. I lean on MySQL specifically when I need:

• Strong relational integrity (foreign keys, constraints)
• Mature tooling for reporting and analytics
• ACID transactions for anything involving money or inventory
• Wide hosting support (AWS RDS, Google Cloud SQL, Azure Database for MySQL)

MySQL Architecture Refresher

Before I write a single line of Python, I like to remind myself how MySQL processes a query, because it directly informs how I write efficient code on the client side.

graph LR
    A[Python Client] --> B[MySQL Connector]
    B --> C[Server: Connection Handler]
    C --> D[Parser]
    D --> E[Optimizer]
    E --> F[Storage Engine - InnoDB]
    F --> G[(Data Files / Buffer Pool)]

The InnoDB buffer pool caches data pages in memory, which is why repeated queries against the same rows are often much faster the second time. As a Python developer, I care about this because poorly written queries — like ones that force full table scans — bypass the benefit of that cache and hammer disk I/O.

Installing a MySQL Driver for Python

I usually pick between two drivers:

For most projects I reach for mysql-connector-python because it’s officially maintained and installs cleanly everywhere:

pip install mysql-connector-python

Connecting to MySQL

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(
        host='localhost',
        user='shop_app',
        password='StrongPassword123!',
        database='shop_db'
    )
    if connection.is_connected():
        db_info = connection.get_server_info()
        print(f"Connected to MySQL Server version {db_info}")
except Error as e:
    print(f"Error connecting to MySQL: {e}")

Output:

Connected to MySQL Server version 8.0.36

I always wrap connection logic in a try/except block because network hiccups and credential issues are common enough that I don’t want an unhandled exception crashing my whole script.

Running Queries

SELECT

cursor = connection.cursor(dictionary=True)
cursor.execute("SELECT id, name, price FROM products WHERE stock > %s", (0,))
rows = cursor.fetchall()
for row in rows:
    print(row)
cursor.close()

I pass dictionary=True to the cursor so results come back as dictionaries ({'id': 1, 'name': 'Mouse', 'price': 19.99}) instead of plain tuples — much easier to work with in application code.

INSERT

cursor = connection.cursor()
insert_query = "INSERT INTO products (name, price, stock) VALUES (%s, %s, %s)"
cursor.execute(insert_query, ('Mechanical Keyboard', 79.99, 40))
connection.commit()
print("Inserted row id:", cursor.lastrowid)
cursor.close()

Notice the %s placeholders — this is Python’s parameter substitution style for MySQL connector, and just like in Node.js, I never build queries with f-strings or .format() when user input is involved. That’s how SQL injection vulnerabilities happen.

UPDATE and DELETE

cursor = connection.cursor()
cursor.execute("UPDATE products SET stock = stock - %s WHERE id = %s", (1, 15))
connection.commit()
print("Rows affected:", cursor.rowcount)

cursor.execute("DELETE FROM products WHERE stock = 0")
connection.commit()
cursor.close()

Connection Pooling in Python

For any application handling more than a handful of requests, I set up a connection pool instead of connecting fresh each time.

from mysql.connector import pooling

connection_pool = pooling.MySQLConnectionPool(
    pool_name="shop_pool",
    pool_size=10,
    host='localhost',
    user='shop_app',
    password='StrongPassword123!',
    database='shop_db'
)

def get_products():
    conn = connection_pool.get_connection()
    try:
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT id, name, price FROM products LIMIT 20")
        return cursor.fetchall()
    finally:
        cursor.close()
        conn.close()  # returns the connection to the pool, doesn't actually close it

A subtlety I always point out to newer developers: calling .close() on a pooled connection doesn’t terminate it — it returns the connection back to the pool for reuse. That behavior is different from a non-pooled connection, and it trips people up.

sequenceDiagram
    participant App as Python App
    participant Pool as MySQLConnectionPool
    participant DB as MySQL Server

    App->>Pool: get_connection()
    Pool-->>App: connection object
    App->>DB: execute query
    DB-->>App: results
    App->>Pool: connection.close()
    Pool->>Pool: return connection to pool (not terminated)

Transactions in Python

Here’s how I handle a multi-step operation that must be atomic — say, transferring stock between two warehouses:

def transfer_stock(product_id, from_warehouse, to_warehouse, qty):
    conn = connection_pool.get_connection()
    try:
        conn.start_transaction()
        cursor = conn.cursor()

        cursor.execute(
            "SELECT quantity FROM warehouse_stock WHERE product_id=%s AND warehouse_id=%s FOR UPDATE",
            (product_id, from_warehouse)
        )
        current_qty = cursor.fetchone()[0]
        if current_qty < qty:
            raise ValueError("Not enough stock to transfer")

        cursor.execute(
            "UPDATE warehouse_stock SET quantity = quantity - %s WHERE product_id=%s AND warehouse_id=%s",
            (qty, product_id, from_warehouse)
        )
        cursor.execute(
            "UPDATE warehouse_stock SET quantity = quantity + %s WHERE product_id=%s AND warehouse_id=%s",
            (qty, product_id, to_warehouse)
        )

        conn.commit()
        print("Transfer successful")
    except Exception as e:
        conn.rollback()
        print(f"Transfer failed, rolled back: {e}")
    finally:
        cursor.close()
        conn.close()

The FOR UPDATE lock ensures no other transaction can read and modify that same row until this one finishes — critical when multiple warehouse transfers might run concurrently.

Using SQLAlchemy for Larger Applications

When a project grows past a certain size, I usually introduce SQLAlchemy as an ORM/toolkit layer on top of PyMySQL or mysqlclient:

pip install sqlalchemy pymysql

from sqlalchemy import create_engine, text

engine = create_engine(
    "mysql+pymysql://shop_app:StrongPassword123!@localhost/shop_db",
    pool_size=10,
    max_overflow=5,
    pool_recycle=3600
)

with engine.connect() as conn:
    result = conn.execute(text("SELECT id, name FROM products WHERE price > :min_price"), {"min_price": 50})
    for row in result:
        print(row.id, row.name)

pool_recycle=3600 is something I set deliberately — it forces SQLAlchemy to discard connections older than an hour, which avoids the classic “MySQL server has gone away” error caused by the server’s wait_timeout closing idle connections the pool doesn’t know about.

Indexing and Query Performance From Python

I regularly run EXPLAIN directly from a Python script during development to sanity-check a query before shipping it:

cursor.execute("EXPLAIN SELECT id, name FROM products WHERE stock > 0 ORDER BY price DESC")
for row in cursor.fetchall():
    print(row)

If I see a full table scan (type: ALL) on a table with real production volume, I add an index:

CREATE INDEX idx_stock_price ON products (stock, price);

Composite indexes like this one are ordered — stock first, then price — because my WHERE clause filters on stock and my ORDER BY uses price. Getting the column order right in a composite index is one of those details that separates a query that returns in 2ms from one that takes 2 seconds on a large table.

Security Best Practices

• Parameterized queries only — never string-format user input into SQL.
• Environment-based configuration — I load credentials via os.environ or a .env file with python-dotenv, never hardcoded.
• Least-privilege DB users — the Python service account only gets the grants it actually needs.
• SSL/TLS for remote connections:

connection = mysql.connector.connect(
    host='db.example.com',
    user='shop_app',
    password='StrongPassword123!',
    database='shop_db',
    ssl_ca='/path/to/ca.pem',
    ssl_disabled=False
)

• Escape/validate input at the application boundary even though parameterization handles most injection risk — it’s defense in depth.

Performance Optimization Tips

1. Batch inserts with executemany instead of looping single inserts:

data = [('Item A', 10.5, 100), ('Item B', 22.0, 50)]
cursor.executemany(
    "INSERT INTO products (name, price, stock) VALUES (%s, %s, %s)", data
)
connection.commit()

2. Fetch only what you need — use LIMIT, and avoid SELECT * in production paths.
3. Use server-side cursors for very large result sets so you’re not loading millions of rows into memory at once:

cursor = connection.cursor(dictionary=True, buffered=False)

4. Reuse connections via pooling rather than opening new ones per request.
5. Watch the buffer pool size (innodb_buffer_pool_size) on the server — as a rule of thumb I set it to 60–70% of available RAM on a dedicated database server.

Real-World Scenario: A Data Pipeline

I built a nightly ETL job in Python that pulled data from an external API, transformed it, and loaded roughly 500,000 rows into MySQL. Using executemany with batches of 1,000 rows, wrapped in a single transaction per batch, cut load time from around 40 minutes (row-by-row inserts) down to under 3 minutes. That’s the kind of difference batching makes at scale.

Troubleshooting Common Issues

Frequently Asked Questions

Should I use an ORM like Django ORM or SQLAlchemy instead of raw queries? For application development with lots of CRUD, yes — it saves time and adds safety by default. For data engineering and heavy analytical queries, I usually go back to raw SQL for full control over the execution plan.

Is mysql-connector-python slower than mysqlclient? Slightly, since mysqlclient is a C extension. For most web applications the difference isn’t the bottleneck — your queries and indexing matter far more.

How do I handle reconnects after a dropped connection? I check connection.is_connected() before reuse, or rely on a pool with pool_recycle to avoid stale connections in the first place.

Can Python handle concurrent database access safely? Yes, through connection pooling and MySQL’s own transaction isolation — Python’s GIL doesn’t affect this since the actual work happens on the MySQL server.

Interview Questions on This Topic

1. What’s the difference between a buffered and unbuffered cursor in Python’s MySQL connector?
2. Why does calling .close() on a pooled connection not actually terminate it?
3. Explain how FOR UPDATE prevents race conditions during a stock transfer.
4. What’s the benefit of executemany over looping single INSERT statements?
5. How would you diagnose a “MySQL server has gone away” error in a long-running Python service?

Key Takeaways

• Always parameterize queries — never string-format user input.
• Use connection pooling for any real application; understand that pooled .close() returns, not terminates.
• Wrap multi-step writes in transactions with explicit row locks when concurrency matters.
• Batch bulk operations with executemany for major performance gains.
• Validate query plans with EXPLAIN and index deliberately based on your actual WHERE/ORDER BY clauses.

References

• MySQL 8.0 Reference Manual: https://dev.mysql.com/doc/refman/8.0/en/
• MySQL Connector/Python Developer Guide: https://dev.mysql.com/doc/connector-python/en/
• SQLAlchemy Documentation: https://docs.sqlalchemy.org/