Mariadb Connector Python Usage

MariaDB Connector/Python

Last updated: 2026-08-10

MariaDB Connector/Python is the official Python driver for MariaDB — the mariadb module on PyPI, implementing the Python DB API 2.0 (PEP-249). This skill covers the connector-specific behavior and the traps that bite generated application code. For getting the module installed and configured in the first place — install variants, prerequisites, option files, TLS setup — see mariadb-connector-python-install.

Default context: Assume the 1.1 stable line unless the user states otherwise. Behavior shared with older 1.x releases is shown without annotation; features added in the newer 2.0 line (mariadb:// URI connection strings, async asyncConnect/create_async_pool, a pure-Python implementation) are marked since 2.0. Connector versions are independent of the MariaDB server version.

What LLMs Often Miss

Connection essentials

import mariadb

# Keyword-argument connection (works on all supported versions)
conn = mariadb.connect(
    host="localhost", port=3306,
    user="app", password="secret",
    database="appdb",
    autocommit=False,          # the default; shown for clarity
)

# since 2.0 — URI connection string
conn = mariadb.connect("mariadb://app:secret@localhost:3306/appdb")

with conn.cursor() as cur:
    cur.execute("INSERT INTO t (name, qty) VALUES (?, ?)", ("widget", 5))
    conn.commit()                      # autocommit is off by default

    cur.execute("SELECT id, name FROM t WHERE qty > ?", (0,))   # note (0,)
    for row in cur:                    # buffered by default
        print(row)

Common connect() keywords: host (default localhost; a comma-separated list gives simple failover), port (default 3306), user/username, password/passwd, database/db, unix_socket, autocommit, and the SSL/TLS options. Result-shape options include dictionary=True (rows as dicts) and named_tuple=True. Some options apply only to the C extension — see the connection-class reference.

Transactions

Autocommit is off, so every statement runs inside a transaction whether or not one was opened explicitly:

try:
    with conn.cursor() as cur:
        cur.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (100, 1))
        cur.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (100, 2))
    conn.commit()
except mariadb.Error:
    conn.rollback()
    raise

conn.begin() starts a transaction explicitly, which matters when autocommit=True was set at connect time and one particular unit of work needs to be atomic. Isolation level is a server-side setting — issue SET TRANSACTION ISOLATION LEVEL … or set it on the server; there is no connector-level parameter for it.

Bulk operations

rows = [("widget", 5), ("gadget", 3), ("doohickey", 8)]
with conn.cursor() as cur:
    cur.executemany("INSERT INTO t (name, qty) VALUES (?, ?)", rows)
conn.commit()

Every tuple must have the same shape and the same types — the connector binds the batch once and reuses that binding. To mean "use the column default" or "NULL" for one element of one row, use the values in mariadb.constants.INDICATOR rather than None, which binds a literal NULL.

Error handling

try:
    cur.execute("INSERT INTO t (id, name) VALUES (?, ?)", (1, "widget"))
except mariadb.IntegrityError as e:
    print(e.errno, e.sqlstate, e)      # 1062, '23000', "Duplicate entry ..."
except mariadb.OperationalError:
    # connection-level problem: server gone, timeout, authentication
    raise

errno carries the MariaDB error number and sqlstate the SQLSTATE, which is what to branch on — the message text is not stable.

Pooling

import mariadb

pool = mariadb.ConnectionPool(
    pool_name="app", pool_size=8,      # max 64; pool_reset_connection defaults to True
    host="localhost", user="app", password="secret", database="appdb",
)

conn = pool.get_connection()
try:
    with conn.cursor() as cur:
        cur.execute("SELECT 1")
        cur.fetchone()
finally:
    conn.close()                        # returns the connection to the pool (does not really close it)

See Also

• mariadb-connector-python-install — installing and configuring the module: install variants, prerequisites, option files, TLS
• mariadb-connector-c-usage — the underlying C client library this module wraps on the C-extension build
• mariadb-transactions / mariadb-set-transaction — the server-side semantics behind conn.commit() / conn.rollback() and isolation levels
• mariadb-prepare — server-side prepared statements, what binary=True uses under the hood
• Canonical reference on mariadb.com/docs, consult for edge cases not covered here: https://mariadb.com/docs/connectors/mariadb-connector-python

This page is: Copyright © 2026 MariaDB. All rights reserved.