Skip to content
Skills Library
Programming · Learning & explanation

mariadb-connector-python-usage

/mariadb-connector-python-usage

MariaDB-specific behavior of MariaDB Connector/Python (the `mariadb` PyPI module, a PEP-249 / DB API 2.0 driver) for application code — that the default paramstyle is qmark (`?`), not format (`%s`), and styles must not be mixed; that string formatting must never build SQL; that autocommit is off by default so DML needs `conn.commit()`; that a single parameter needs a one-tuple `(v,)`; that the text protocol is default and `binary=True` switches to server-side prepared statements; that cursors are buffered by default; connection pooling via `ConnectionPool` (default size 5, max 64); `callproc()

Repository
ai-plugins ↗
Licence
LGPL
Last repository change
24.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
2
Deprecation

No signals detected. Repository changed on 24.10.2026, without references to retired models or APIs.

Licence

LGPL, otwarta.

What it does and when to use it

MariaDB-specific behavior of MariaDB Connector/Python (the `mariadb` PyPI module, a PEP-249 / DB API 2.0 driver) for application code — that the default paramstyle is qmark (`?`), not format (`%s`), and styles must not be mixed; that string formatting must never build SQL; that autocommit is off by default so DML needs `conn.commit()`; that a single parameter needs a one-tuple `(v,)`; that the text protocol is default and `binary=True` switches to server-side prepared statements; that cursors are buffered by default; connection pooling via `ConnectionPool` (default size 5, max 64); `callproc()

Original author description

MariaDB-specific behavior of MariaDB Connector/Python (the `mariadb` PyPI module, a PEP-249 / DB API 2.0 driver) for application code — that the default paramstyle is qmark (`?`), not format (`%s`), and styles must not be mixed; that string formatting must never build SQL; that autocommit is off by default so DML needs `conn.commit()`; that a single parameter needs a one-tuple `(v,)`; that the text protocol is default and `binary=True` switches to server-side prepared statements; that cursors are buffered by default; connection pooling via `ConnectionPool` (default size 5, max 64); `callproc()

How it works

  1. What LLMs Often Miss
  2. Connection essentials
  3. Transactions
  4. Bulk operations
  5. Error handling
  6. Pooling
  7. See Also
Original SKILL.md headings · 128 lines, 1,205 words
SKILL.md preview
---
name: mariadb-connector-python-usage
description: "MariaDB-specific behavior of MariaDB Connector/Python (the `mariadb` PyPI module, a PEP-249 / DB API 2.0 driver) for application code — that the default paramstyle is qmark (`?`), not format (`%s`), and styles must not be mixed; that string formatting must never build SQL; that autocommit is off by default so DML needs `conn.commit()`; that a single parameter needs a one-tuple `(v,)`; that the text protocol is default and `binary=True` switches to server-side prepared statements; that cursors are buffered by default; connection pooling via `ConnectionPool` (default size 5, max 64); `callproc()` for stored procedures; and the DB API exception hierarchy under `mariadb.Error`. Use when writing or reviewing Python code that talks to MariaDB with the `mariadb` module."
---

# 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](https://peps.python.org/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

| If the agent writes / assumes… | …prefer the MariaDB form |
|---|---|
| `%s` placeholders everywhere (`WHERE id = %s`) | The default **paramstyle is `qmark`** — use `?`: `cursor.execute("SELECT ... WHERE id = ?", (id,))`. `%s` (format) is accepted for compatibility, but **mixing `?` and `%s` in one statement raises**. Prefer `?` |
| Builds SQL with f-strings / `%` / `.format()` / string concat | Never. Pass values as the second argument to `execute()`; the connector escapes them. `cursor.execute("... VALUES (?, ?)", (a, b))` |
| Passes a single parameter as a scalar (`execute(sql, id)`) | Parameters must be a **tuple or list**; a single value needs a one-element tuple: `(id,)` — note the trailing comma. `executemany()` takes a **list of tuples** |
| Assumes autocommit is on; INSERT/UPDATE "disappear" | **`autocommit` is `False` by default.** Call `conn.commit()` after DML, or open the connection with `autocommit=True`. On close without commit, the transaction rolls back |
| Uses a libpq-style DSN (`"host=... dbname=..."`) or a MySQL URL | `connect()` takes **keyword args**: `mariadb.connect(host="localhost", port=3306, user=..., password=..., database=...)`. *Since 2.0* a `mariadb://user:pw@host/db` URI string is also accepted (kwargs override URI values) |
| Imports `MySQLdb`, `pymysql`, or `mysql.connector` | The module is **`mariadb`** (`import mariadb`). API is DB API 2.0 but the module name, C-extension nature, and `?` paramstyle differ from those drivers |
| Expects server-side prepared statements by default | The **text protocol is the default**. Set **`binary=True`** for the binary protocol (server-side prepared statements) — at the cursor (`conn.cursor(binary=True)`), or *since 2.0* at the connection. (`prepared=True` is deprecated in favor of `binary=True`) |
| Expects the driver to auto-reconnect after a dropped connection | **No automatic reconnection.** (The `reconnect` parameter was removed in 2.0.) Use a connection pool, or call `conn.reconnect()` explicitly |
| Streams a huge result set with a default cursor | Cursors are **buffered by default** — the whole result is pulled into memory. For large results use `conn.cursor(buffered=False)` and iterate row by row |
| Loops `execute()` for bulk insert/update | Use **`executemany(sql, list_of_tuples)`** — far fewer round trips. All tuples must have the **same types**; signal NULL / column default with `mariadb.constants.INDICATOR` values, not `None`-as-default |
| Hand-rolls a pool, or opens a new connection per request | Use **`mariadb.ConnectionPool(pool_name="app", pool_size=5)`**; borrow with `pool.get_connection()` and return it by `conn.close()`. `pool_size` defaults to 5, **max 64**; `pool_reset_connection=True` (default) resets each connection before reuse |
| Calls a stored procedure by string-building `CALL ...` with OUT params | Use **`cursor.callproc("proc_name", (in1, in2))`**; read OUT/INOUT results from the returned sequence / a following result set |
| Catches `Exception` or a MySQL driver's error class | Catch **`mariadb.Error`** (the DB API base). Subclasses: `OperationalError`, `IntegrityError`, `ProgrammingError`, `DataError`, `InterfaceError`, `InternalError`, `NotSupportedError`, `PoolError`. `.errno` / `.sqlstate` are on the exception |
| Wraps work in `with mariadb.connect(...) as conn:` and expects a commit on exit | The connection's `__exit__` **closes** the connection — it does not commit. Closing without committing **rolls back**, so uncommitted DML is silently lost. Commit explicitly inside the block |
| Calls `cursor.scroll()` to reposition in a streamed result | `scroll()` raises `ProgrammingError` on an **unbuffered** cursor, and on any cursor with no result set. Repositioning only works on the buffered default |
| Reads `cursor.rowcount` before fetching, or on a statement that produced nothing | `rowcount` is **`-1`** when no `execute*()` has run or the count cannot be determined. Treat `-1` as "unknown", not as zero |

## Connection essentials

```python
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:

```python
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

```python
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

```python
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

```python
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>

<sub>_This page is: Copyright © 2026 MariaDB. All rights reserved._</sub>

Versions

The database has no other skills named mariadb-connector-python-usage.

Identical content appears in 4 other GitHub folders. We show one copy with the highest score.

Database snapshot: 13.11.2026 · package version 65d0e0e81b17 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100mariadb-connector-c-usage
    MariaDB-specific behavior of MariaDB Connector/C (the `libmariadb` client library implementing the MariaDB client-server protocol in C) for application code — that the public API is still `mysql_`-prefixed (`mysql_init`, `mysql_real_connect`, `mysql_query`, ...) with only a few…
  • 100mariadb-connector-j-usage
    MariaDB-specific behavior of MariaDB Connector/J (the `org.mariadb.jdbc:mariadb-java-client` JDBC driver) for application code — that JDBC 4 auto-registers the driver so `Class.forName` is unneeded; that the URL scheme is `jdbc:mariadb://host:port/db?opts` (`jdbc:mysql:` needs…
  • 100connect-from-python
    Connects a Python application to Azure SQL Database, choosing between Microsoft's first-party mssql-python driver and the incumbent pyodbc, and covering driver installation, the connection string each one wants, connection pooling, and Microsoft Entra ID including token-based…
  • 100mysql-patterns
    MySQL and MariaDB schema, query, indexing, transaction, replication, and connection-pool patterns for production backends. Use when designing MySQL or MariaDB schemas and indexes, or when a query, transaction, or replica lags.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.