Connecting from applications: drivers and pooling

Use prepared statements and a connection pool correctly, set the session settings that silently change your data, and retry only the errors that are worth retrying.

Drivers and connection settings

Every client speaks the same wire protocol, so drivers differ in ergonomics rather than in capability. What matters is that parameters are bound rather than interpolated, and that connection settings are explicit rather than inherited.

import mysql.connector

conn = mysql.connector.connect(
    host="10.0.0.10", port=3306,
    user="app", password="...", database="app",
    charset="utf8mb4",
    connection_timeout=5,
    time_zone="+00:00",          # do not inherit the server default
)

cur = conn.cursor(dictionary=True)
cur.execute("SELECT id, total FROM orders WHERE customer_id = %s", (42,))
rows = cur.fetchall()
cur.close()        # close the cursor before returning the connection

• MySQL drivers use %s placeholders; ? belongs to other engines. Never build SQL by formatting strings — bound parameters are what prevent injection and let the driver reuse a prepared statement.
• Set the session time zone explicitly. TIMESTAMP columns are converted using it, so an application default and a reporting session can disagree about the same row.
• The connection character set decides which collation is used for comparisons; a mismatch against the column forces a conversion and can silently disable an index.

Connection pooling

from mysql.connector import pooling

pool = pooling.MySQLConnectionPool(
    pool_name="app", pool_size=10,
    pool_reset_session=True,      # no session state leaks between checkouts
    **settings
)

def recent_orders(customer_id):
    conn = pool.get_connection()
    try:
        cur = conn.cursor(dictionary=True)
        cur.execute(
            "SELECT id, total FROM orders WHERE customer_id = %s"
            " ORDER BY placed_at DESC LIMIT 20", (customer_id,))
        return cur.fetchall()
    finally:
        conn.close()              # returns it to the pool, does not disconnect

• A connection costs a TCP handshake, an optional TLS negotiation, authentication and a server thread. Pooling pays that once instead of per request.
• Size the pool against the server, not the application. Two hundred workers with a pool of twenty is four thousand connections against a server whose max_connections is far lower.
• Always return the connection in a finally block or a context manager. A leaked connection eventually exhausts the pool and the application hangs rather than errors.
• pool_reset_session clears temporary tables, user variables and transaction state. Without it, one request can leak settings into the next.
• The server closes idle connections after wait_timeout. The pool must validate a connection before handing it out, or the first query after a quiet period fails.

Errors, retries and shutdown

import mysql.connector as mc
from mysql.connector import errorcode

RETRYABLE = {
    errorcode.CR_SERVER_LOST,
    errorcode.CR_SERVER_GONE_ERROR,
    errorcode.ER_LOCK_DEADLOCK,
    errorcode.ER_LOCK_WAIT_TIMEOUT,
}

def run(operation, attempts=3):
    for attempt in range(attempts):
        try:
            return operation()
        except mc.Error as err:
            last = attempt == attempts - 1
            if err.errno not in RETRYABLE or last:
                raise
            time.sleep(0.2 * (2 ** attempt))

• Separate transient failures from permanent ones, then retry only the former: a deadlock or a dropped connection may succeed on a second attempt, a syntax error never will.
• Retry the whole transaction, not the single statement inside it. A half-applied transaction is not a state the application knows how to continue from.
• Retrying is only safe when the operation is idempotent. A unique key plus an upsert makes a retried insert harmless.

FAQ

Related

Writing data safely: DML and transactions User management, privileges and security

Last refreshed 2026-09-18.