Application development with MariaDB Connector/Python
Setup for Examples
Set up the test database, contacts and accounts tables, and a user account required by the MariaDB Connector/Python code examples in this documentation.
The examples in this MariaDB Connector/Python documentation depend on a database test and tables contacts and accounts.
Create the Schema
Create a test database if one does not exist with the statement:
CREATE DATABASE IF NOT EXISTS test;
Create tables in the test database for testing basic and advanced operations with statements:
Create a user account to test connectivity with the statement:
Ensure that the user account has privileges to access the tables with the statement:
CREATE USER 'db_user'@'192.0.2.1'
IDENTIFIED BY 'db_user_password';
GRANT SELECT, INSERT, UPDATE, DELETE, DROP
ON test.contacts
TO 'db_user'@'192.0.2.1';
GRANT SELECT, INSERT, UPDATE, DELETE, DROP
ON test.accounts
TO 'db_user'@'192.0.2.1';
Create the User
License
MariaDB Connector/Python is licensed under the GNU LGPL v2.1; the accompanying documentation is covered by the Creative Commons Attribution 3.0 license.
You are free, to : - Share, copy and redistribute the material in any medium or format
Adapt, remix, transform, and build upon the material for any purpose, even commercially.
under the following terms : - Attribution – You must give appropriate credit, provide a link to the license, and indicate if changes were made. You may do so in any reasonable manner, but not in any way that suggests the licensor endorses you or your use.
No additional restrictions —- You may not apply legal terms or technological measures that legally restrict others from doing anything the license permits.
Connector/Python
Complete Connector/Python guide: DB API 2.0 (PEP-249) implementation, MariaDB/MySQL connectivity, C+Python architecture, and Connector/C transport.
MariaDB Connector/Python
MariaDB Connector/Python enables python programs to access MariaDB and MySQL databases, using an API which is compliant with the Python DB API 2.0 (PEP-249).
Version 2.0 is currently a Release Candidate (RC); version 1.1 is the latest stable (GA) release. Because 2.0 is not yet GA, install it with the --pre flag (for example pip install --pre mariadb); a plain pip install mariadb installs the latest stable release (1.1). See the page for details. Do not use non-stable (non-GA) releases in production.
Overview of MariaDB Connector/Python: a PEP-249-compliant DB API 2.0 driver supporting sync and async operations, available as pure Python, a C extension, or pre-compiled binary wheels.
Install MariaDB Connector/Python via pip with pure Python, C extension, or binary wheel options; connection pooling requires the separate mariadb[pool] extra.
Basic usage of MariaDB Connector/Python covers connecting, parameterized queries with execute and executemany, and NULL and default value handling with indicators.
Async/await support in MariaDB Connector/Python 2.0 enables non-blocking database operations via asyncConnect, AsyncCursor, and create_async_pool for asyncio-based Python applications.
MariaDB Connector/Python 2.0 connection pooling supports sync and async pools via create_pool and create_async_pool, with configurable size, health checks, and context managers.
Migration from MariaDB Connector/Python 1.1 to 2.0 covers renamed parameters, removed auto-reconnect, updated pooling, URI connections, async/await support, and a migration checklist.
Application development with MariaDB Connector/Python.
MariaDB Connector/Python API reference covers the module, connection class, cursor class, async/await support, connection pooling, and constants used in Python database applications.
MariaDB Connector/Python is licensed under the GNU LGPL v2.1; the accompanying documentation is covered by the Creative Commons Attribution 3.0 license.
MariaDB Connector/Python bug reports are filed in the Jira CONPY project. Effective reports include version details, a short reproducing script, and table definitions where relevant.
Set up the test database, contacts and accounts tables, and a user account required by the MariaDB Connector/Python code examples in this documentation.
MariaDB Connector/Python transactions default to manual commit; the Connection class provides commit and rollback, with async transaction support via asyncConnect in version 2.0.
MariaDB Connector/Python FAQ addresses common installation issues, migration from 1.1 to 2.0, the binary vs text protocol distinction, async setup, and transaction commit requirements.
Basic Usage
Basic usage of MariaDB Connector/Python covers connecting, parameterized queries with execute and executemany, and NULL and default value handling with indicators.
- Connection parameters, methods, and attributes
- Cursor parameters, methods, and attributes
- Pool configuration and usage
The basic usage of MariaDB Connector/Python is similar to other database drivers which implement DB API 2.0 (PEP-249).
Since version 2.0: Connections can be established using URI strings or keyword arguments.
Below is a simple example of a typical use of MariaDB Connector/Python
Alternative - Keyword Arguments:
Output:
Before MariaDB Connector/Python can be used, the MariaDB Connector/Python module must be imported. Once the mariadb module is loaded, a connection to a database server will be established using the method connect().
In order to be able to communicate with the database server in the form of SQL statements, a cursor object must be created first.
The method name cursor may be a little misleading: unlike a cursor in MariaDB that can only read and return data, a cursor in Python can be used for all types of SQL statements.
After creating the table mytest, everything is ready to insert some data: Column values that are to be inserted in the database are identified by place holders, the data is then passed in the form of a tuple as a second parameter.
After creating and populating the table mytest the cursor will be used to retrieve the data.
At the end we free resources and close cursor and connection.
As shown in previous example, passing parameters to SQL statements happens by using placeholders in the statement. By default MariaDB Connector/Python uses a question mark as a placeholder, for compatibility reason also %s placeholders are supported. Passing parameters is supported in methods execute() and executemany() of the cursor class.
By default, the text protocol is used for parameter binding. For binary protocol (prepared statements), set binary=True at the cursor level; since version 2.0binary=True can also be set at the connection level (in connect() or via a URI). Parameter escaping is handled automatically by the connector.
Using Binary Protocol (Prepared Statements):
Often there is a requirement to update, delete or insert multiple records. This could be done be using execute() in a loop, but much more effective is using the executemany() method. The executemany() works similar to execute(), but accepts data as a list of tuples:
When using executemany(), there are a few restrictions:
All tuples must have the same types as in first tuple. E.g. the parameter [(1),(1.0)] or [(1),(None)] are invalid.
Special values like None or column default value needs to be indicated by an indicator.
In certain situations, for example when inserting default values or NULL, special indicators must be used.
Beside the default indicator which inserts the default value of 1.99, the following indicators are supported: : * INDICATOR.IGNORE: Ignores the value (only update commands)
INDICATOR.NULL: Value is NULL
INDICATOR.IGNORE_ROW: Don’t update or insert row
Mixing different parameter styles is not supported and will raise an exception
The Python string operator % must not be used. The execute() method accepts a tuple or list as second parameter.
Placeholders between quotation marks are interpreted as a string.
Parameters for execute() needs to be passed as a tuple. If only one parameter will be passed, tuple needs to contain a comma at the end.
Parameters for executemany() need to be passed as a list of tuples.
Several standard python types are converted into SQL types and returned as Python objects when a statement is executed.
import mariadb
# Establish a connection using URI
with mariadb.connect("mariadb://example_user:GHbe_Su3B8@localhost/test") as conn:
with conn.cursor() as cursor:
# Populate countries table with some data
cursor.execute("INSERT INTO countries(name, country_code, capital) VALUES (?,?,?)",
("Germany", "GER", "Berlin"))
# retrieve data
cursor.execute("SELECT name, country_code, capital FROM countries")
# print content
row = cursor.fetchone()
print(*row, sep=' ')
import mariadb
# connection parameters
conn_params = {
"user": "example_user",
"password": "GHbe_Su3B8",
"host": "localhost",
"database": "test"
}
# Establish a connection
with mariadb.connect(**conn_params) as conn:
with conn.cursor() as cursor:
cursor.execute("INSERT INTO countries(name, country_code, capital) VALUES (?,?,?)",
("Germany", "GER", "Berlin"))
cursor.execute("SELECT name, country_code, capital FROM countries")
row = cursor.fetchone()
print(*row, sep=' ')
Germany GER Berlin
import mariadb
# Establish a connection using URI
with mariadb.connect("mariadb://example_user:GHbe_Su3B8@localhost/test") as conn:
with conn.cursor() as cursor:
sql = "INSERT INTO countries (name, country_code, capital) VALUES (?,?,?)"
data = ("Germany", "GER", "Berlin")
cursor.execute(sql, data)
conn.commit()
# delete last entry
sql = "DELETE FROM countries WHERE country_code=?"
data = ("GER",)
cursor.execute(sql, data)
conn.commit()
import mariadb
# Enable binary protocol at connection level
with mariadb.connect("mariadb://example_user:GHbe_Su3B8@localhost/test?binary=true") as conn:
with conn.cursor() as cursor:
# All queries use binary protocol (prepared statements)
sql = "INSERT INTO countries (name, country_code, capital) VALUES (?,?,?)"
data = ("Germany", "GER", "Berlin")
cursor.execute(sql, data)
conn.commit()
import mariadb
# Establish a connection using URI
with mariadb.connect("mariadb://example_user:GHbe_Su3B8@localhost/test") as connection:
with connection.cursor() as cursor:
sql = "INSERT INTO countries (name, country_code, capital) VALUES (?,?,?)"
data = [("Ireland", "IE", "Dublin"),
("Italy", "IT", "Rome"),
("Malaysia", "MY", "Kuala Lumpur"),
("France", "FR", "Paris"),
("Iceland", "IS", "Reykjavik"),
("Nepal", "NP", "Kathmandu")]
# insert data
cursor.executemany(sql, data)
# Since autocommit is off by default, we need to commit last transaction
connection.commit()
# Instead of 3 letter country-code, we inserted 2 letter country code, so
# let's fix this mistake by updating data
sql = "UPDATE countries SET country_code=? WHERE name=?"
data = [("IRL", "Ireland"),
("ITA", "Italy"),
("MYS", "Malaysia"),
("FRA", "France"),
("ISL", "Iceland"),
("NPL", "Nepal")]
cursor.executemany(sql, data)
# Now let's delete all non European countries
sql = "DELETE FROM countries WHERE name=?"
data = [("Malaysia",), ("Nepal",)]
cursor.executemany(sql, data)
# by default autocommit is off, so we need to commit
# our transactions
connection.commit()
import mariadb
from mariadb.constants import INDICATOR
# Establish a connection using URI
with mariadb.connect("mariadb://example_user:GHbe_Su3B8@localhost/test") as connection:
with connection.cursor() as cursor:
cursor.execute("DROP TABLE IF EXISTS cakes")
cursor.execute("CREATE TABLE cakes(id int, cake varchar(100), price decimal(10,2) default 1.99)")
sql = "INSERT INTO cakes (id, cake, price) VALUES (?,?,?)"
data = [(1, "Cherry Cake", 2.10), (2, "Apple Cake", INDICATOR.DEFAULT)]
cursor.executemany(sql, data)
connection.commit()
Constants are declared in mariadb.constants module.
For using constants of various types, they have to be imported first:
from mariadb.constants import *
CAPABILITY
MariaDB capability flags.
These flags are used to check the capabilities both of a MariaDB server or the client application.
Capability flags are defined in module mariadb.constants.CAPABILIY
Since version 1.1.4
import mariadb
from mariadb.constants import *
# connection parameters
conn_params= {
"user" : "example_user",
"password" : "GHbe_Su3B8",
"host" : "localhost"
}
with mariadb.connect(**conn_params) as connection:
# test if LOAD DATA LOCAL INFILE is supported
if connection.server_capabilities & CAPABILITY.LOCAL_FILES:
print("Server supports LOCAL INFILE")
Output:
MariaDB capability flags.
These flags are used to check the capabilities both of a MariaDB server or the client application.
Capability flags are defined in module mariadb.constants.CLIENT
Since version 1.1.0, deprecated in 1.1.4
Cursor constants are used for server side cursors. Currently only read only cursor is supported.
Cursor constants are defined in module mariadb.constants.CURSOR.
Since version 1.1.0
This is the default setting (no cursor)
Will create a server side read only cursor. The cursor is a forward cursor, which means it is not possible to scroll back.
Using ERR constants instead of error numbers make the code more readable. Error constants are defined in constants.ERR module
Since version 1.1.2
Output:
MariaDB FIELD_FLAG Constants
These constants represent the various field flags. As an addition to the DBAPI 2.0 standard (PEP-249) these flags are returned as eighth element of the cursor description attribute.
Field flags are defined in module mariadb.constants.FIELD_FLAG
Since version 1.1.0
column is defined as not NULL
column is (part of) a primary key
column is (part of) a unique key
column is (part of) a key
column contains a binary object
numeric column is defined as unsigned
column has zerofill attribute
column is a binary
column is defined as enum
column is an auto_increment column
column is defined as time stamp
column is defined as SET
column hasn’t a default value
column will be set to current timestamp on UPDATE
column contains numeric value
column is part of a key
MariaDB FIELD_TYPE Constants
These constants represent the field types supported by MariaDB. The field type is returned as second element of cursor description attribute.
Field types are defined in module mariadb.constants.FIELD_TYPE
column type is TINYINT (1-byte integer)
column type is SMALLINT (2-byte integer)
column type is INT (4-byte integer)
column type is FLOAT (4-byte single precision)
column type is DOUBLE (8-byte double precision)
column type is NULL
column type is TIMESTAMP
column type is BIGINT (8-byte Integer)
column type is MEDIUMINT (3-byte Integer)
column type is DATE
column type is TIME
column type is DATETIME
column type is YEAR
column type is VARCHAR
column type is BIT
column type is JSON
column type is DECIMAL
column type is ENUM
column type is SET
column type is TINYBLOB (max. length of 255 bytes)
column type is MEDIUMBLOB (max. length of 16,777,215 bytes)
column type is LONGBLOB (max. length 4GB bytes)
column type is BLOB (max. length of 65.535 bytes)
column type is VARCHAR (variable length)
column type is CHAR (fixed length)
column type is GEOMETRY
Indicator values are used in executemany() method of cursor class to indicate special values when connected to a MariaDB server 10.2 or newer.
indicates a NULL value
indicates to use default value of column
indicates to ignore value for column for UPDATE statements. If set, the column will not be updated.
indicates not to update the entire row.
For internal use only
For internal use only
The STATUS constants are used to check the server status of the current connection.
Since version 1.1.0
Example:
if (connection.server_status & STATUS.SP_OUT_PARAMS): print("retrieving output parameters from store procedure") ... else: print("retrieving data from stored procedure") ....
Pending transaction
Server operates in autocommit mode
The result from last executed statement contained two or more result sets which can be retrieved by cursors nextset() method.
The last executed statement didn’t use a good index.
The last executed statement didn’t use an index.
The last executed statement opened a server side cursor.
For server side cursors this flag indicates end of a result set.
The current database in use was dropped and there is no default database for the connection anymore.
Indicates that SQL mode NO_BACKSLASH_ESCAPE is active, which means that the backslash character ‘' becomes an ordinary character.
The previously executed statement was slow (and needs to be optimized).
The current result set contains output parameters of a stored procedure.
The session status has been changed.
SQL mode ANSI_QUOTES is active.
Metadata has changed (e.g., table structure modified).
Pending read-only transaction.
MariaDB Extended FIELD_TYPE Constants
These constants represent the extended field types supported by MariaDB. Extended field types provide additional type information beyond standard SQL types.
Extended field types are defined in module mariadb.constants.EXT_FIELD_TYPE
No extended type information (value: 0)
JSON data type (value: 1)
UUID data type (value: 2)
IPv4 address data type (value: 3)
IPv6 address data type (value: 4)
Geometry POINT type (value: 5)
Geometry MULTIPOINT type (value: 6)
Geometry LINESTRING type (value: 7)
Geometry MULTILINESTRING type (value: 8)
Geometry POLYGON type (value: 9)
Geometry MULTIPOLYGON type (value: 10)
Geometry GEOMETRYCOLLECTION type (value: 11)
Example:
Session tracking constants for monitoring session state changes.
Session tracking constants are defined in module mariadb.constants.SESSION_TRACK
Since version 2.0
CLIENT
CURSOR
CURSOR.NONE
CURSOR.READ_ONLY
ERR (Error)
FIELD_FLAG
FIELD_FLAG.NOT_NULL
FIELD_FLAG.PRIMARY_KEY
FIELD_FLAG.UNIQUE_KEY
FIELD_FLAG.MULTIPLE_KEY
FIELD_FLAG.BLOB
FIELD_FLAG.UNSIGNED
FIELD_FLAG.ZEROFILL
FIELD_FLAG.BINARY
FIELD_FLAG.ENUM
FIELD_FLAG.AUTO_INCREMENT
FIELD_FLAG.TIMESTAMP
FIELD_FLAG.SET
FIELD_FLAG.NO_DEFAULT
FIELD_FLAG.ON_UPDATE_NOW
FIELD_FLAG.NUMERIC
FIELD_FLAG.PART_OF_KEY
FIELD_TYPE
FIELD_TYPE.TINY
FIELD_TYPE.SHORT
FIELD_TYPE.LONG
FIELD_TYPE.FLOAT
FIELD_TYPE.DOUBLE
FIELD_TYPE.NULL
FIELD_TYPE.TIMESTAMP
FIELD_TYPE.LONGLONG
FIELD_TYPE.INT24
FIELD_TYPE.DATE
FIELD_TYPE.TIME
FIELD_TYPE.DATETIME
FIELD_TYPE.YEAR
FIELD_TYPE.VARCHAR
FIELD_TYPE.BIT
FIELD_TYPE.JSON
FIELD_TYPE.NEWDECIMAL
FIELD_TYPE.ENUM
FIELD_TYPE.SET
FIELD_TYPE.TINY_BLOB
FIELD_TYPE.MEDIUM_BLOB
FIELD_TYPE.LONG_BLOB
FIELD_TYPE.BLOB
FIELD_TYPE.VAR_STRING
FIELD_TYPE.STRING
FIELD_TYPE.GEOMETRY
INDICATORS
INDICATOR.NULL
INDICATOR.DEFAULT
INDICATOR.IGNORE
INDICATOR.IGNORE_ROW
INFO
TPC_STATE
STATUS
STATUS.IN_TRANS
STATUS.AUTOCOMMIT
STATUS.MORE_RESULTS_EXIST
STATUS.QUERY_NO_GOOD_INDEX_USED
STATUS.QUERY_NO_INDEX_USED
STATUS.CURSOR_EXISTS
STATUS.LAST_ROW_SENT
STATUS.DB_DROPPED
STATUS.NO_BACKSLASH_ESCAPES
STATUS.QUERY_WAS_SLOW
STATUS.PS_OUT_PARAMS
STATUS.SESSION_STATE_CHANGED
STATUS.ANSI_QUOTES
STATUS.METADATA_CHANGED
STATUS.IN_TRANS_READONLY
EXT_FIELD_TYPE
EXT_FIELD_TYPE.NONE
EXT_FIELD_TYPE.JSON
EXT_FIELD_TYPE.UUID
EXT_FIELD_TYPE.INET4
EXT_FIELD_TYPE.INET6
EXT_FIELD_TYPE.POINT
EXT_FIELD_TYPE.MULTIPOINT
EXT_FIELD_TYPE.LINESTRING
EXT_FIELD_TYPE.MULTILINESTRING
EXT_FIELD_TYPE.POLYGON
EXT_FIELD_TYPE.MULTIPOLYGON
EXT_FIELD_TYPE.GEOMETRYCOLLECTION
SESSION_TRACK
Server supports LOCAL INFILE
import mariadb
from mariadb.constants import *
# connection parameters
conn_params= {
"user" : "example_user",
"password" : "wrong_password",
"host" : "localhost"
}
# try to establish a connection
try:
connection= mariadb.connect(**conn_params)
except mariadb.OperationalError as Err:
if Err.errno == ERR.ER_ACCESS_DENIED_ERROR:
print("Access denied. Wrong password!")
Access denied. Wrong password!
cursor.callproc("my_storedprocedure", (1,"foo"))
import mariadb
from mariadb.constants import EXT_FIELD_TYPE
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
cursor.execute("SELECT id, data, location FROM test_table")
metadata = cursor.metadata
if metadata:
for i, ext_type in enumerate(metadata['ext_type_or_format']):
if ext_type == EXT_FIELD_TYPE.JSON:
print(f"Column {i} is JSON type")
elif ext_type == EXT_FIELD_TYPE.UUID:
print(f"Column {i} is UUID type")
elif ext_type == EXT_FIELD_TYPE.POINT:
print(f"Column {i} is POINT geometry type")
cursor.close()
conn.close()
The ConnectionPool class
MariaDB Connector/Python ConnectionPool manages a fixed-size pool with methods to add, retrieve, and close connections, plus attributes reporting pool size and connection state.
class ConnectionPool(*args, **kwargs)
Class defining a pool of database connections
MariaDB Connector/Python supports simple connection pooling. A connection pool holds a number of open connections and handles thread safety when providing connections to threads.
The size of a connection pool is configurable at creation time, but cannot be changed afterward. The maximum size of a connection pool is limited to 64 connections.
Keyword Arguments:
`pool_name` (str) - Name of connection pool
`pool_size` (int) - Size of pool. The Maximum allowed number is 64. Default to 5
`pool_reset_connection` (bool) - Will reset the connection before returning it to the pool. Default to True.
`pool_validation_interval` (int) - Specifies the validation interval in milliseconds after which the status of a connection requested from the pool is checked. A value of 0 means that the status will always be checked. Default to 500 (Added in version 1.1.6)
**kwargs - Optional additional connection arguments, as described in mariadb.connect() method.
Adds a connection object to the connection pool.
In case that the pool doesn’t have a free slot or is not configured, a PoolError exception will be raised.
Closes connection pool and all connections.
Returns a connection from the connection pool or raises a PoolError exception if a connection is not available.
Sets the connection configuration for the connection pool. For valid connection arguments, check the mariadb.connect() method.
Note: This method doesn’t create connections in the pool. To fill the pool, one has to use add_connection() ḿethod.
Returns the number of connections in connection pool.
Since version 1.1.0
Returns the maximum size for connection pools.
Returns the size of the connection pool.
Returns the name of the connection pool.
API Reference
MariaDB Connector/Python API reference covers the module, connection class, cursor class, async/await support, connection pooling, and constants used in Python database applications.
The MariaDB Connector/Python module provides connect, asyncConnect, create_pool, and create_async_pool constructors, DB API 2.0 type objects, and the exception hierarchy.
MariaDB Connector/Python Connection class reference covers parameters, methods for commit and rollback, and read-only attributes for server version and TLS state.
MariaDB Connector/Python Cursor class documents parameters, execute and fetch methods, and attributes such as rowcount, description, and sp_outparams for stored procedures.
MariaDB Connector/Python ConnectionPool manages a fixed-size pool with methods to add, retrieve, and close connections, plus attributes reporting pool size and connection state.
Install MariaDB Connector/Python via pip with pure Python, C extension, or binary wheel options; connection pooling requires the separate mariadb[pool] extra.
API Reference
Connection API - Connection parameters, methods, and attributes
Cursor API - Cursor parameters, methods, and attributes
MariaDB server versions from version 10.3 or MySQL server versions from version 5.7.
MariaDB client library (MariaDB Connector/C) from version 3.3.1 (optional - only required for C extension).
Version 1.1 is the current GA release. A plain pip install installs the latest stable version:
To pin to a specific 1.1 release:
Version 1.1:
Always installs the C extension
Requires MariaDB Connector/C to be pre-installed
Does not support pure Python or binary wheels
For version 1.1 documentation, see the .
MariaDB Connector/Python 2.0 offers three installation options:
Works everywhere, no compiler or C dependencies required:
This is the default installation method for 2.0. The pure Python implementation:
Works on all Python interpreters (CPython, PyPy, etc.)
Requires no compilation or system dependencies
Provides good performance for most use cases
For maximum performance, install the C extension:
The C extension:
Delivers 2-12× better performance on data-heavy workloads
Requires MariaDB Connector/C to be pre-installed on your system
Requires a C compiler for building from source
Pre-compiled wheels with no local C connector required:
Binary wheels:
Include the C extension pre-compiled
MariaDB Connector/C is bundled - no separate installation needed
No compiler required
Connection pooling is now a separate optional package:
Or combine with binary wheels:
Option 1: Binary wheels (recommended)
Option 2: Pure Python
Option 3: C extension from source
First install MariaDB Connector/C. MSI installers for both 32-bit and 64-bit operating systems are available from .
Then install the C extension:
On success, you should see a message at the end "Successfully installed mariadb-2.0.0rc2".
The pure Python implementation has minimal requirements:
No compiler or system dependencies required.
To build the C extension from source, you will need:
C compiler (gcc, clang, or MSVC)
Python development files (Usually installed with package python3-dev). Minimum supported version is Python 3.10 (version 1.1 requires Python 3.8).
MariaDB Connector/C libraries and header files (version 3.3.1 or later)
On POSIX systems, ensure the PATH environment variable contains the directory with the mariadb_config utility.
Installing the C extension from source:
Installing with pooling support:
For troubleshooting, check the .
If you have installed the sources, after successful build you can run the test suite from the source directory.
You can configure the connection parameters by using the following environment variables
TEST_DB_USER (default root)
TEST_DB_PASSWORD
TEST_DB_DATABASE (default ‘testp’)
TEST_DB_HOST (default ‘localhost’)
The MariaDB Connector/Python module
The MariaDB Connector/Python module provides connect, asyncConnect, create_pool, and create_async_pool constructors, DB API 2.0 type objects, and the exception hierarchy.
MariaDB Connector/Python module enables python programs to access MariaDB and MySQL databases, using an API which is compliant with the Python DB API 2.0 (PEP-249).
Creates a MariaDB Connection object.
The first positional argument, when given, is a connection URI string (see Since version 2.0 below). By default, the standard Connection class is used.
Parameter connectionclass specifies a subclass of the standard Connection class. If not specified, the default is used. This optional parameter was added in version 1.1.0.
Since version 2.0: Connection can be established using a URI string or keyword arguments. Keyword arguments override URI values when both are provided.
Connection pooling is included by default
Fully compatible with the C extension API
Provides the same API as the pure Python implementation
Available for common platforms (Windows, Linux, macOS)
Either from MariaDB server package or from MariaDB Connector/C package
The mariadb_config program from MariaDB Connector/C (must be in your PATH)
For POSIX systems: TLS libraries (GnuTLS or OpenSSL)
Python's "packaging" module
TEST_DB_PORT (default 3306)
Installation Options
Version 2.0 is currently a Release Candidate (RC); version 1.1 is the latest stable (GA) release.
Because 2.0 is not yet GA, a plain pip install mariadb installs the latest stable release (1.1). To install the 2.0 release candidate you must pass the --pre flag, for example pip install --pre mariadb. Do not use non-stable (non-GA) releases in production.
Installing Version 1.1 (Stable / GA)
Installing Version 2.0 (Release Candidate)
Version 2.0 is a Release Candidate. The --pre flag is required so that pip will select the pre-release; without it, pip installs the latest GA release (1.1).
converter - Conversion dictionary mapping FIELD_TYPE values to conversion functions
For the full list of accepted parameters — including SSL/TLS options, timeouts, prepared-statement caching, configuration file loading, the result format options (dictionary, named_tuple, native_object), and the parameters that only apply to the C extension — see The connection class.
reconnect (connection parameter) - Removed. Automatic reconnection is no longer supported; use connection pools or call conn.reconnect() manually.
cursor_type (cursor option) - Removed in the pure-Python implementation; use buffered=False instead. The C extension still accepts it.
prepared (cursor option) - Deprecated in favor of binary=True. It still works but emits a DeprecationWarning.
Creates an asynchronous MariaDB Connection object for use with async/await. As with connect(), the first positional argument, when given, is a connection URI string.
Constructs an object capable of holding a binary value.
Constructs an object holding a date value.
Constructs an object holding a date value from the given ticks value (number of seconds since the epoch). For more information see the documentation of the standard Python time module.
Constructs an object holding a time value.
Constructs an object holding a time value from the given ticks value (number of seconds since the epoch). For more information see the documentation of the standard Python time module.
Constructs an object holding a datetime value.
Constructs an object holding a datetime value from the given ticks value (number of seconds since the epoch). For more information see the documentation of the standard Python time module.
String constant stating the supported DB API level. The value for mariadb is 2.0.
Integer constant stating the level of thread safety. In version 2.0 the value is 3, meaning threads may share the module, connections, and cursors. In version 1.1 the value is 1, meaning threads may share the module but not connections.
String constant stating the type of parameter marker. For mariadb the value is qmark. For compatibility reasons mariadb also supports the format and pyformat paramstyles with the limitation that they can’t be mixed inside a SQL statement.
String constant stating the version of the used MariaDB Connector/C library.
Since version 1.1.0
Returns a version as an integer. In version 2.0 this is the version of MariaDB Connector/Python itself, in the format: MAJOR_VERSION * 10000 + MINOR_VERSION * 100 + PATCH_VERSION. In version 1.1 it is the version of the MariaDB Connector/C library in use, in the format: MAJOR_VERSION * 10000 + MINOR_VERSION * 1000 + PATCH_VERSION.
Since version 1.1.0 Returns a version as a tuple. In version 2.0 this is the version of MariaDB Connector/Python itself and may include a release-stage suffix (for example (2, 0, 0, 'rc2')). In version 1.1 it is the version of the MariaDB Connector/C library, in the format: (MAJOR_VERSION, MINOR_VERSION, PATCH_VERSION)
Compliant to DB API 2.0 MariaDB Connector/C provides information about errors through the following exceptions:
Exception raised for errors that are due to problems with the processed data like division by zero, numeric value out of range, etc.
Exception raised for errors that are related to the database
Exception raised for errors that are related to the database interface rather than the database itself
Exception raised for important warnings like data truncations while inserting, etc
Exception raised for errors related to ConnectionPool class.
Exception raised for errors that are related to the database’s operation and not necessarily under the control of the programmer.
Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails
Exception raised when the database encounters an internal error, e.g. the cursor is not valid anymore
Exception raised for programming errors, e.g. table not found or already exists, syntax error in the SQL statement
Exception raised in case a method or database API was used which is not supported by the database
MariaDB Connector/Python type objects are immutable sets for type settings and defined in DBAPI 2.0 (PEP-249).
Example:
Output:
This type object is used to describe columns in a database that are string-based (e.g. CHAR1).
This type object is used to describe (long) binary columns in a database (e.g. LONG, RAW, BLOBs).
This type object is used to describe numeric columns in a database.
This type object is used to describe date/time columns in a database.
This type object is not supported in MariaDB Connector/Python and represents an empty set.
import mariadb
# Simple URI
conn = mariadb.connect("mariadb://user:password@localhost:3306/mydb")
# URI with query parameters
conn = mariadb.connect("mariadb://user:password@localhost/mydb?autocommit=true&binary=true")
# Keyword arguments override URI values
conn = mariadb.connect("mariadb://user:password@localhost/mydb", database="otherdb")
import mariadb
# URI connection (recommended)
with mariadb.connect("mariadb://example_user:GHbe_Su3B8@localhost/test") as connection:
print(connection.character_set)
# Keyword arguments (still supported)
with mariadb.connect(user="example_user", host="localhost", database="test", password="GHbe_Su3B8") as connection:
print(connection.character_set)
# Binary protocol enabled at connection level
with mariadb.connect("mariadb://localhost/test?binary=true") as connection:
cursor = connection.cursor() # Uses binary protocol by default
cursor.execute("SELECT * FROM users WHERE id = ?", (1,))
utf8mb4
import asyncio
import mariadb
async def main():
# URI connection
conn = await mariadb.asyncConnect("mariadb://user:password@localhost/mydb")
# Or with keyword arguments
conn = await mariadb.asyncConnect(
host="localhost",
user="user",
password="password",
database="mydb"
)
cursor = await conn.cursor()
await cursor.execute("SELECT * FROM users WHERE id = ?", (1,))
row = await cursor.fetchone()
await cursor.close()
await conn.close()
asyncio.run(main())
pip install --pre mariadb[pool]
import mariadb
pool = mariadb.create_pool(
host="localhost",
user="user",
password="password",
database="mydb",
min_size=5,
max_size=20
)
with pool.acquire() as conn:
with conn.cursor() as cursor:
cursor.execute("SELECT 1")
import asyncio
import mariadb
async def main():
pool = await mariadb.create_async_pool(
host="localhost",
user="user",
password="password",
database="mydb",
min_size=10,
max_size=50
)
async with await pool.acquire() as conn:
async with conn.cursor() as cursor:
await cursor.execute("SELECT 1")
await pool.close()
asyncio.run(main())
MariaDB Connector/Python 2.0 migration covers renamed parameters, removed auto-reconnect, updated pooling, URI connections, async/await support, and a migration checklist.
This guide helps you migrate your applications from MariaDB Connector/Python 1.1 to version 2.0.0.
Version 2.0 is currently a Release Candidate (RC); version 1.1 is the latest stable (GA) release. Until 2.0 reaches GA, install it with the --pre flag (for example pip install --pre mariadb); a plain pip install mariadb installs the latest stable release (1.1). Do not use non-stable (non-GA) releases in production.
API Reference
Connection API - Connection parameters, methods, and attributes
Cursor API - Cursor parameters, methods, and attributes
- Pool configuration and usage
Version 2.0 is a major rewrite that introduces significant improvements and breaking changes:
Flexible distribution options: Pure Python, C extension, and pre-compiled binary wheels
Native async/await support: First-class asynchronous API
URI connection strings: Standard mariadb:// connection syntax
This always installed the C extension and required MariaDB Connector/C to be pre-installed.
Version 2.0 is still a Release Candidate, so the --pre flag is required; without it, pip installs the latest GA release (1.1).
Pure Python (default, works everywhere):
C extension (maximum performance):
Requires MariaDB Connector/C to be pre-installed on your system.
Pre-compiled binary wheels (no local C connector required):
MariaDB Connector/C is bundled - no separate installation needed.
With connection pooling:
Pure Python is now default: No compiler or MariaDB Connector/C required
Connection pooling is optional: Must explicitly install mariadb[pool]
Binary wheels available: Pre-compiled for common platforms with MariaDB Connector/C bundled
Version 1.1:
Version 2.0:
Why removed: Auto-reconnect silently hid failures, caused unpredictable behavior with lost session state, uncommitted transactions, and broken transaction isolation.
Migration: Use connection pools instead. For manual reconnection, call conn.reconnect() explicitly.
Version 1.1:
Version 2.0:
Migration: Replace cursor_type=CURSOR.READ_ONLY with buffered=False.
Note: cursor_type is removed only in the pure-Python implementation. The C extension still accepts it.
The binary cursor option already existed in version 1.1 alongside prepared. In version 2.0, prepared is deprecated in favor of binary; it still works but emits a DeprecationWarning.
Version 1.1 (either option):
Version 2.0 (use binary):
Migration: Replace prepared=True with binary=True.
Version 1.1: Automatically promoted certain parameter types (bytes, datetime) to binary protocol, even when not requested.
Version 2.0:
Text protocol by default: Predictable, debuggable
Explicit binary=True required: No automatic promotion
Dict parameters always use text protocol: Named parameter substitution
Version 1.1 (automatic promotion):
Version 2.0 (explicit control):
Migration: If you relied on automatic binary protocol, explicitly set binary=True.
Version 1.1:
Version 2.0:
Migration:
Install mariadb[pool]
Use create_pool() instead of ConnectionPool()
Note: pool_size
Version 1.1:
Version 2.0:
Migration: The C extension still supports plugin_dir. Pure Python does not load native authentication plugins from disk.
Version 2.0 introduces standard URI syntax:
Migration: Consider using URI strings for cleaner configuration, especially with environment variables:
New in 2.0 - Native asynchronous API:
Migration: For async applications (FastAPI, Starlette, etc.), use the async API instead of wrapping synchronous calls in thread pools.
Version 2.0 allows setting binary protocol at connection level:
Migration: For applications that mostly use prepared statements, set binary=True at connection level.
New in 2.0 - Shared statement cache (opt-in):
Benefits:
First execution pays PREPARE cost
Subsequent executions reuse prepared statement
2-4× performance improvement for repeated queries
Migration: No changes needed - caching is automatic. Consider increasing prep_stmt_cache_size for applications with many distinct queries.
Before:
After:
Before:
After:
Version 2.0 introduces automatic prepared statement caching. For best performance:
Use binary protocol for hot paths:
Increase cache size for many distinct queries:
Reuse the same SQL statements - the cache benefits repeated executions
Connection-level: When most queries are parameterized and repeated
Per-cursor: For specific hot queries in mixed workloads
Text protocol: For ad-hoc queries, SHOW commands, administrative queries
For web applications with high concurrency (FastAPI, Starlette):
Version 1.1: Python 3.8 and later
Version 2.0: Python 3.10 and later
Both versions support:
MariaDB Server 10.3+
MySQL Server 5.7+
The mariadb.connect() API with keyword arguments remains largely backward compatible. Most 1.1 code will work with minimal changes after removing deprecated parameters.
Full type hints: Complete mypy and pyright compatibility
Improved performance: Faster parameter binding and prepared statement caching
Unified protocol control: Explicit binary vs text protocol selection
C extension requires pre-installation: MariaDB Connector/C must be installed separately when building from source with mariadb[c]
# First install pooling support (--pre is required while 2.0 is an RC)
pip install --pre mariadb[pool]
import mariadb
# Synchronous pool
pool = mariadb.create_pool(
host="localhost",
user="root",
password="secret",
min_size=5,
max_size=10
)
# Asynchronous pool (new in 2.0)
pool = await mariadb.create_async_pool(
host="localhost",
user="root",
password="secret",
min_size=5,
max_size=10
)
import os
DATABASE_URL = os.getenv("DATABASE_URL")
conn = mariadb.connect(DATABASE_URL)
import asyncio
import mariadb
async def main():
# Single connection
async with await mariadb.asyncConnect(
"mariadb://user:pass@host/db"
) as conn:
async with conn.cursor() as cursor:
await cursor.execute("SELECT * FROM users WHERE id = ?", (1,))
row = await cursor.fetchone()
print(row)
# Connection pool
pool = await mariadb.create_async_pool(
"mariadb://user:pass@host/db",
min_size=5,
max_size=20
)
async with await pool.acquire() as conn:
async with conn.cursor() as cursor:
await cursor.execute("SELECT 1")
result = await cursor.fetchone()
await pool.close()
asyncio.run(main())
# All cursors default to binary protocol
conn = mariadb.connect(
"mariadb://localhost/mydb?binary=true"
)
# Or with keyword argument
conn = mariadb.connect(
host="localhost",
database="mydb",
binary=True
)
# All cursors now use binary protocol by default
cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE id = ?", (1,))
# The shared prepared-statement cache is disabled by default; enable it explicitly
conn = mariadb.connect("mariadb://localhost/mydb?cache_prep_stmts=true")
# Configure the cache size (applies when the cache is enabled)
conn = mariadb.connect(
"mariadb://localhost/mydb?cache_prep_stmts=true&prep_stmt_cache_size=500"
)
# Choose your installation option (--pre is required while 2.0 is an RC)
pip install --pre mariadb[binary,pool]
# Instead of thread pool wrapping
pool = await mariadb.create_async_pool(
"mariadb://localhost/mydb",
min_size=10,
max_size=50
)
Async/Await Support
Async/await support in MariaDB Connector/Python 2.0 enables non-blocking database operations via asyncConnect, AsyncCursor, and create_async_pool for asyncio-based Python applications.
MariaDB Connector/Python 2.0 introduces native async/await support for asynchronous database operations. This enables efficient database access in async applications like FastAPI, Starlette, and other asyncio-based frameworks.
API Reference
Connection API - Connection parameters, methods, and attributes
Cursor API - Cursor parameters, methods, and attributes
- Pool configuration and usage
The async API provides:
Native asyncio integration: No thread pool wrapping required
Async connections: asyncConnect() function and AsyncConnection class
Async cursors: AsyncCursor
Both the pure Python and C extension implementations support async operations.
Async connections support the same parameters as synchronous connections:
Connection pools are essential for web applications handling multiple concurrent requests.
Returns rows as dictionaries instead of tuples:
Returns rows as named tuples:
Fetches rows on demand instead of buffering entire result set:
Use binary protocol for better performance with prepared statements:
Always use connection pools in production:
Enable for repeated queries (enabled by default):
Use executemany() for bulk inserts:
The async implementation provides:
No GIL contention: Pure Python async uses native asyncio I/O
Efficient concurrency: Handle thousands of concurrent connections
Lower latency: No thread pool overhead
Note: Async excels in high-concurrency scenarios, not single-threaded throughput.
Always use connection pools in production applications
Use context managers for automatic resource cleanup
Enable binary protocol for repeated parameterized queries
class with async methods
Async connection pools: create_async_pool() for connection pooling
Context manager support: Async with statements for resource management
Same API surface: Familiar interface matching the synchronous API
Better resource usage: Event loop scheduling vs thread context switching
Handle errors properly with try/except blocks
Close pools during application shutdown
Configure pool sizes based on your workload
Use prepared statement caching for better performance
Avoid creating connections per request - use pools instead
async with await mariadb.asyncConnect("mariadb://localhost/mydb") as conn:
async with conn.cursor() as cursor:
# Simple query
await cursor.execute("SELECT COUNT(*) FROM users")
count = await cursor.fetchone()
print(f"Total users: {count[0]}")
# Parameterized query
await cursor.execute(
"SELECT name, email FROM users WHERE id = ?",
(user_id,)
)
user = await cursor.fetchone()
async with conn.cursor() as cursor:
await cursor.execute("SELECT * FROM users")
# Fetch one row
row = await cursor.fetchone()
# Fetch multiple rows
rows = await cursor.fetchmany(10)
# Fetch all remaining rows
all_rows = await cursor.fetchall()
# Iterate over results
await cursor.execute("SELECT * FROM users")
async for row in cursor:
print(row)
async with conn.cursor() as cursor:
# Single insert
await cursor.execute(
"INSERT INTO users (name, email) VALUES (?, ?)",
("Alice", "alice@example.com")
)
await conn.commit()
# Get last inserted ID
print(f"Inserted user ID: {cursor.lastrowid}")
async with conn.cursor() as cursor:
data = [
("Alice", "alice@example.com"),
("Bob", "bob@example.com"),
("Charlie", "charlie@example.com")
]
await cursor.executemany(
"INSERT INTO users (name, email) VALUES (?, ?)",
data
)
await conn.commit()
print(f"Inserted {cursor.rowcount} rows")
async with await mariadb.asyncConnect("mariadb://localhost/mydb") as conn:
try:
async with conn.cursor() as cursor:
# Start transaction (autocommit is False by default)
await cursor.execute(
"UPDATE accounts SET balance = balance - ? WHERE id = ?",
(100, 1)
)
await cursor.execute(
"UPDATE accounts SET balance = balance + ? WHERE id = ?",
(100, 2)
)
# Commit transaction
await conn.commit()
except mariadb.Error as e:
# Rollback on error
await conn.rollback()
print(f"Transaction failed: {e}")
raise
import asyncio
import mariadb
async def main():
# Create pool
pool = await mariadb.create_async_pool(
host="localhost",
user="user",
password="password",
database="mydb",
min_size=5, # Minimum connections
max_size=20, # Maximum connections
ping_threshold=0.25 # Ping if idle > 250ms
)
# Use pool
async with await pool.acquire() as conn:
async with conn.cursor() as cursor:
await cursor.execute("SELECT * FROM users WHERE id = ?", (1,))
row = await cursor.fetchone()
print(row)
# Close pool when done
await pool.close()
asyncio.run(main())
pool = await mariadb.create_async_pool(
"mariadb://user:password@localhost/mydb",
min_size=10,
max_size=50
)
pool = await mariadb.create_async_pool(
host="localhost",
user="user",
password="password",
database="mydb",
min_size=5, # Minimum pool size
max_size=20, # Maximum pool size
ping_threshold=0.25, # Ping connections idle > 250ms
binary=True, # Use binary protocol by default
autocommit=False # Transaction mode
)
from fastapi import FastAPI, HTTPException
from contextlib import asynccontextmanager
import mariadb
# Global pool variable
pool = None
@asynccontextmanager
async def lifespan(app: FastAPI):
# Startup: Create pool
global pool
pool = await mariadb.create_async_pool(
"mariadb://user:password@localhost/mydb",
min_size=10,
max_size=50
)
yield
# Shutdown: Close pool
await pool.close()
app = FastAPI(lifespan=lifespan)
@app.get("/users/{user_id}")
async def get_user(user_id: int):
async with await pool.acquire() as conn:
async with conn.cursor(dictionary=True) as cursor:
await cursor.execute(
"SELECT id, name, email FROM users WHERE id = ?",
(user_id,)
)
user = await cursor.fetchone()
if user is None:
raise HTTPException(status_code=404, detail="User not found")
return user
@app.post("/users")
async def create_user(name: str, email: str):
async with await pool.acquire() as conn:
async with conn.cursor() as cursor:
try:
await cursor.execute(
"INSERT INTO users (name, email) VALUES (?, ?)",
(name, email)
)
await conn.commit()
return {
"id": cursor.lastrowid,
"name": name,
"email": email
}
except mariadb.IntegrityError:
await conn.rollback()
raise HTTPException(
status_code=400,
detail="User with this email already exists"
)
import asyncio
import mariadb
async def main():
try:
conn = await mariadb.asyncConnect(
"mariadb://user:password@localhost/mydb"
)
except mariadb.Error as e:
print(f"Connection error: {e}")
return
try:
async with conn.cursor() as cursor:
await cursor.execute("SELECT * FROM users WHERE id = ?", (1,))
row = await cursor.fetchone()
except mariadb.DatabaseError as e:
print(f"Database error: {e}")
except mariadb.ProgrammingError as e:
print(f"Programming error: {e}")
finally:
await conn.close()
asyncio.run(main())
async with conn.cursor(dictionary=True) as cursor:
await cursor.execute("SELECT id, name, email FROM users WHERE id = ?", (1,))
user = await cursor.fetchone()
print(user["name"]) # Access by column name
async with conn.cursor(named_tuple=True) as cursor:
await cursor.execute("SELECT id, name, email FROM users WHERE id = ?", (1,))
user = await cursor.fetchone()
print(user.name) # Access as attribute
async with conn.cursor(buffered=False) as cursor:
await cursor.execute("SELECT * FROM large_table")
# Fetch rows one at a time
async for row in cursor:
process_row(row)
# Only one row in memory at a time
# Connection-level binary protocol
conn = await mariadb.asyncConnect(
"mariadb://localhost/mydb?binary=true"
)
async with conn.cursor() as cursor:
# All queries use binary protocol
await cursor.execute("SELECT * FROM users WHERE id = ?", (1,))
row = await cursor.fetchone()
# Or per-cursor
async with conn.cursor(binary=True) as cursor:
await cursor.execute("SELECT * FROM users WHERE id = ?", (1,))
row = await cursor.fetchone()
# Good: Connection pool
pool = await mariadb.create_async_pool(
"mariadb://localhost/mydb",
min_size=10,
max_size=50
)
# Bad: Creating connections per request
async def handle_request():
conn = await mariadb.asyncConnect("mariadb://localhost/mydb")
# ... use connection
await conn.close()
async def main():
async with await mariadb.asyncConnect("mariadb://localhost/mydb") as conn:
async with conn.cursor() as cursor:
await cursor.execute("SELECT * FROM users WHERE id = ?", (1,))
row = await cursor.fetchone()
asyncio.run(main())
Transactions with MariaDB Connector/Python
MariaDB Connector/Python transactions default to manual commit; the Connection class provides commit and rollback, with async transaction support via asyncConnect in version 2.0.
API Reference
Connection API - Connection parameters, methods, and attributes
Cursor API - Cursor parameters, methods, and attributes
A database transaction is a single unit of logic. A transaction can consist of one or more database operations. Transactions are useful and sometimes essential in several types of data operations. For example, many applications require that a set of SQL statements either complete, or fail, as a single unit.
The common characteristics of transactions are atomicity, consistency, isolation, and durability, what is termed as ACID (atomic, consistent, isolated, durable) transactions. MariaDB transactions are .
You can enable auto-committed transactions using the autocommit connection attribute.
By default, MariaDB Connector/Python disables auto-commit. With auto-commit disabled transactions must be committed explicitly.
You may want to use explicit transactions so that either all statements are committed together or all statements are completely rolled back. For example, explicit transactions are almost always necessary for financial transactions. Otherwise, a situation could occur where, money is removed from the payer's account, but it is not properly moved to the payee's account.
To use explicit transactions, MariaDB's standard transaction related statements can be executed with MariaDB Connector/Python using a cursor:
Additionally, instances of the Connection class can use the commit() and rollback() methods instead.
The following example shows how to update the example table accounts created in . The email data is updated from the format firstnamelastname@example.com to the new format firstname.lastname@example.com. Call the functions to update data in a transaction. Because the updates are made within a transaction, either all contacts' emails are updated to the new format, or none are.
The functions to add and update account data must be defined before being called with regards to their ordering in the script.
The add_account() function adds a new account to the table.
The execute() method is called on the cursor in the add_account()
Confirm the test.accounts table was properly updated by using to execute a statement:
MariaDB Connector/Python disables auto-committing transactions by default, following the PEP-249 DBAPI 2.0 specification.
To auto-commit transactions, enable auto-commit either when initializing a connection or by manually setting the autocommit connection attribute.
To enable auto-commit using connect():
To enable auto-commit using autocommit connection attribute:
With auto-commit enabled, MariaDB Connector/Python commits a transaction after each statement executes.
Version 2.0 introduces native async/await support for transactions:
Overview of MariaDB Connector/Python: a PEP-249-compliant DB API 2.0 driver supporting sync and async operations, available as pure Python, a C extension, or pre-compiled binary wheels.
MariaDB Connector/Python enables python programs to access MariaDB and MySQL databases, using an API which is compliant with the Python DB API 2.0 (PEP-249).
Version 2.0 offers flexible distribution options:
Pure Python - Works on all platforms, no compiler required
C extension
Bug Reports
MariaDB Connector/Python bug reports are filed in the Jira CONPY project. Effective reports include version details, a short reproducing script, and table definitions where relevant.
If you think that you have found a bug in MariaDB Software, please report it at and file it under Project CONPY (abbreviation for Connector/Python).
Always search the bug database first. Especially if you are using an older version of MariaDB Connector/Python it could be reported already by someone else or it was already fixed in a more recent version.
We need to know what you did, what happened and what you wanted to happen. A report stating that method xyz() hangs, will not allow us to provide you with an advice or fix, since we just don’t know what the method is doing. Beside versions, a good bug report contains a short script which reproduces the problem. Sometimes it is also necessary to provide the definition (and data) of used tables.
MariaDB Connector/Python interacts with two other components: The database server and MariaDB Connector/C. The latter one is responsible for client/server communication. An error does not necessarily have to exist in Connector / Python; it can also be an error in the database server or in Connector/C. In this case, we will reclassify the bug (MDEV or CONC).
Use copy and paste instead. Screenshots create a lot more data volume and are often difficult to read on mobile devices. Typing program code from a screenshot is also an unnecessary effort.
Scripts which are longer than 10 lines often contain code which is not relevant to the problem and increases the time to figure out the real problem. So try to keep it simple and focus on the real problem.
The sane applies for database related components like tables, views, and stored procedures. Avoid table definitions with hundreds of columns if the problem can be reproduced with only 4 columns.
If you have encountered two or more bugs which are not related, please file an issue for each of them.
If your application crashes, please also provide if possible a backtrace and output of the exception.
The update_account_amount() function updates the amount in an account associated with the given email.
The execute() method is called on the cursor in the update_account_amount() method, which executes an statement to update a row in the table.
In each of these functions, the query string is the first value specified to the execute() method.
In each of these functions, the new values for the row, and the values for the where clause if present, are specified to the execute() method using a tuple.
In each of these functions, the values in the tuple are substituted for the question marks (?) in the query string.
# Module Import
import mariadb
import sys
# Adds account
def add_account(cur, first_name, last_name, email, amount):
"""Adds the given account to the accounts table"""
cur.execute("INSERT INTO test.accounts(first_name, last_name, email, amount) VALUES (?, ?, ?, ?)",
(first_name, last_name, email, amount))
# Update Last Name
def update_account_amount(cur, email, change):
"""Updates amount of an account in the table"""
cur.execute("UPDATE test.accounts SET amount=(amount-?) WHERE email=?",
(change, email))
# Instantiate Connection (Version 2.0 - URI connection)
try:
conn = mariadb.connect("mariadb://db_user:USER_PASSWORD@192.0.2.1:3306/test")
cur = conn.cursor()
new_account_fname = "John"
new_account_lname = "Rockefeller"
new_account_email = "john.rockefeller@example.com"
new_account_amount = 418000000000.00
add_account(cur,
new_account_fname,
new_account_lname,
new_account_email,
new_account_amount)
new_account_change = 1000000.00
update_account_amount(cur,
new_account_email,
new_account_change)
conn.commit()
conn.close()
except Exception as e:
print(f"Error committing transaction: {e}")
conn.rollback()
- Maximum performance (2-12× faster on data-heavy workloads)
Pre-compiled wheels - No MariaDB Connector/C installation needed
All implementations support both synchronous and asynchronous operations.
MariaDB Connector/Python connects to MariaDB and MySQL database servers. Individual server-side features may require a minimum server version; those requirements are noted with the feature.
The following MariaDB Connector/Python release series are currently supported:
Version 2.0 (currently 2.0.0rc2) is a Release Candidate and is not yet a supported release series.
Requirement
1.1 (GA)
2.0 (RC)
Python
CPython 3.9 through 3.14
CPython 3.10 or later
MariaDB Connector/C
3.3.1 or later, always required
3.3.1 or later, and only for the c and binary extras — the pure Python build requires none
Connector/Python reports both its own version and the version of the underlying MariaDB Connector/C:
Installation — version 1.1 (stable / GA):
A plain pip3 install installs the latest stable release (1.1). It always installs the C extension and requires MariaDB Connector/C to be pre-installed; connection pooling is included by default.
Installation — version 2.0 (Release Candidate):
Version 2.0 is a pre-release, so the --pre flag is required — without it, pip installs the latest GA release (1.1).
import mariadb
print(mariadb.__version__) # connector version, e.g. '1.1.14'
print(mariadb.__version_info__) # same, as a tuple: (1, 1, 14)
print(mariadb.client_version) # MariaDB Connector/C version, numeric
print(mariadb.client_version_info) # MariaDB Connector/C version, as a tuple
# Latest stable release (1.1)
$ pip3 install mariadb
# Pin to a specific 1.1 release
$ pip3 install mariadb==1.1.14
# Pure Python (default)
$ pip3 install --pre mariadb
# C extension for maximum performance
$ pip3 install --pre mariadb[c]
# Pre-compiled binary wheels
$ pip3 install --pre mariadb[binary]
# With connection pooling
$ pip3 install --pre mariadb[binary,pool]
Version 1.1 is the latest stable (GA) release; version 2.0 is currently a Release Candidate (RC). Choose the version that fits your needs below. Do not use non-stable (non-GA) releases in production.
Supported Versions
Server Compatibility
Supported Release Series
Requirements
Checking Your Installed Version
Installation
Links
The cursor class
MariaDB Connector/Python Cursor class documents parameters, execute and fetch methods, and attributes such as rowcount, description, and sp_outparams for stored procedures.
MariaDB Connector/Python Cursor Object
Cursor Parameters
Cursors are created using the connection.cursor() method and accept the following optional parameters:
Result Format Parameters
buffered (bool) - Buffer all results immediately in memory. When True (default), all rows are fetched and stored in memory. When False, results are streamed from the server, reducing memory usage for large result sets. Default: True
named_tuple (bool) - Return rows as named tuples instead of regular tuples. Allows accessing columns by name (e.g., row.column_name). Default: False
dictionary (bool) - Return rows as dictionaries instead of tuples. Allows accessing columns by name (e.g., row['column_name']). Default: False
native_object (bool) - Return native Python objects for certain database types. Default: False. Available since version 2.0; passing it to cursor() in version 1.1 raises a TypeError.
binary (bool) - Use binary protocol (prepared statements) for this cursor. Overrides the connection-level binary setting. When True, uses COM_STMT_PREPARE + COM_STMT_EXECUTE for better performance with repeated queries. Default: Inherits from connection
Basic cursor:
Unbuffered cursor for large result sets:
Dictionary cursor:
Named tuple cursor:
Binary protocol cursor:
Combined parameters:
Executes a stored procedure sp. The data sequence must contain an entry for each parameter the procedure expects.
Input/Output or Output parameters have to be retrieved by .fetch methods, the .sp_outparams attribute indicates if the result set contains output parameters.
Arguments: : - sp: Name of stored procedure.
data: Optional sequence containing data for placeholder : substitution.
Example:
Prepare and execute a SQL statement.
Parameters may be provided as sequence or mapping and will be bound to variables in the operation. Variables are specified as question marks (paramstyle ='qmark'), however for compatibility reasons MariaDB Connector/Python also supports the 'format' and 'pyformat' paramstyles with the restriction, that different paramstyles can't be mixed within a statement.
A reference to the operation will be retained by the cursor.
Since version 2.0: If the cursor was created with binary=True, the statement uses the MariaDB binary protocol (prepared statements). With prepared statement caching enabled (default), the first execution prepares the statement and subsequent executions reuse the cached prepared statement for better performance.
By default execute() method generates a buffered result unless the optional parameter buffered was set to False or the cursor was generated as an unbuffered cursor.
Protocol Selection (Version 2.0):
Text protocol (default): Standard SQL execution, predictable behavior
Dict parameters: Always use text protocol for named parameter substitution
Prepare a database operation (INSERT,UPDATE,REPLACE or DELETE statement) and execute it against all parameter found in sequence.
Exactly behaves like .execute() but accepts a list of tuples, where each tuple represents data of a row within a table. .executemany() only supports DML (insert, update, delete) statements.
If the SQL statement contains a RETURNING clause, executemany() returns a result set containing the values for columns listed in the RETURNING clause.
Example:
The following example will insert 3 rows:
To insert special values like NULL or a column default, you need to specify indicators:
INDICATOR.NULL is used for NULL values
INDICATOR.IGNORE is used to skip update of a column.
INDICATOR.DEFAULT is used for a default value (insert/update)
All values for a column must have the same data type.
Indicators can only be used when connecting to a MariaDB Server 10.2 or newer. MySQL servers don’t support this feature.
Fetch all remaining rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples).
An exception will be raised if the previous call to execute() didn't produce a result set or execute() wasn't called before.
Example:
Fetch the next set of rows of a query result, returning a sequence of sequences (e.g. a list of tuples). An empty sequence is returned when no more rows are available.
The number of rows to fetch per call is specified by the parameter. If it is not given, the cursor's arraysize determines the number of rows to be fetched. The method should try to fetch as many rows as indicated by the size parameter. If this is not possible due to the specified number of rows not being available, fewer rows may be returned.
An exception will be raised if the previous call to execute() didn't produce a result set or execute() wasn't called before.
Example:
Fetch the next row of a query result set, returning a single sequence, or None if no more data is available.
An exception will be raised if the previous call to execute() didn't produce a result set or execute() wasn't called before.
Example:
Version 1.1 only. Return the next row from the currently executed SQL statement using the same semantics as .fetchone(). In version 2.0 this method was removed; iterate the cursor directly instead (for row in cursor:).
Will make the cursor skip to the next available result set, discarding any remaining rows from the current set.
Scroll the cursor in the result set to a new position according to mode.
If mode is “relative” (default), value is taken as offset to the current position in the result set, if set to absolute, value states an absolute target position.
Required by PEP-249. Does nothing in MariaDB Connector/Python
Required by PEP-249. Does nothing in MariaDB Connector/Python
(read/write)
The number of rows to fetch at a time with .fetchmany().
This read/write attribute defaults to 1 meaning to fetch a single row at a time.
Example:
(read-only)
Controls whether result sets are buffered in memory or streamed from the server.
Buffered (True, default):
All result rows are immediately fetched and stored in client memory
The entire result set is transferred at once
Connection is freed immediately after execute()
Unbuffered (False):
Results are streamed row-by-row from the server
Only the current row is kept in memory
Connection remains blocked until all rows are fetched
Example:
Best Practices:
Close the cursor and free resources. After closing, the cursor cannot be used anymore.
Example:
(read-only)
Returns the reference to the connection object on which the cursor was created.
Example:
(read-only)
This read-only attribute is a sequence of 11-item tuples. Each tuple contains information describing one result column:
name - Column name
type_code - Column type code
display_size - Display size
This attribute will be None for operations that do not return rows or if the cursor has not had an operation invoked via the .execute*() method yet.
Example:
Checking BLOB vs TEXT fields:
Returns the ID generated by a query on a table with a column having the AUTO_INCREMENT attribute or the value for the last usage of LAST_INSERT_ID().
If the last query wasn’t an INSERT or UPDATE statement or if the modified table does not have a column with the AUTO_INCREMENT attribute and LAST_INSERT_ID was not used, the returned value will be None
(read-only)
Similar to the description property, this property returns a dictionary with complete metadata for all columns in the result set.
Each dictionary key contains a list of values, one for each column in the result set.
Dictionary Keys:
catalog - Catalog name (always 'def')
schema - Current schema/database name
field - Column alias name, or original column name if no alias
Indicates if the current result set contains OUT or INOUT parameters from a previously executed stored procedure.
When calling a stored procedure with OUT or INOUT parameters using callproc() or execute(), the output parameters are returned as a separate result set. This attribute is True when the current result set contains these output parameters, and False otherwise.
Example:
Example with Multiple Result Sets:
Using with Binary Protocol:
(read-only)
Returns the number of rows that the last execute*() method produced (for DQL statements like SELECT) or affected (for DML statements like UPDATE, INSERT, DELETE).
Return Values:
Positive number - Number of rows returned (SELECT) or affected (INSERT/UPDATE/DELETE)
-1 - No execute*() has been performed, or rowcount cannot be determined
0 - Statement executed but no rows were affected/returned
Important Notes:
For unbuffered cursors, the exact row count is only available after all rows have been fetched
For buffered cursors, the row count is immediately available after execute()
For INSERT/UPDATE/DELETE, the row count is always immediately available
Examples:
Unbuffered Cursor:
Buffered Cursor:
DML Statements (INSERT/UPDATE/DELETE):
Batch Operations with executemany():
Practical Use Case - Verify Operation Success:
(read-only)
Version 1.1 only. Returns the last SQL statement that was executed by the cursor. This attribute is not available in version 2.0.
Example:
(read-only)
Returns the number of warnings from the last executed statement, or zero if there are no warnings.
Note: Detailed warning messages can be retrieved using the connection.show_warnings() method.
Example:
(read-only)
Returns the current 0-based index of the cursor in the result set, or None if no result set is available.
This property tracks the position within the current result set as rows are fetched.
Example:
(read-only)
Returns the number of columns in the current result set, or 0 if there is no result set.
Example:
(read-only)
Returns True if the cursor is closed, False otherwise.
A cursor is considered closed if either the cursor itself was closed or the parent connection was closed.
Example:
Connection closure also closes cursors:
INDICATOR.ROW is used to skip update/insert of the entire row.
Multiple cursors can be active on the same connection
Higher memory usage for large result sets
Better for small to medium result sets
Only one unbuffered cursor can be active per connection
Lower memory usage - ideal for large result sets
Must fetch all rows before executing another query on the same connection
internal_size - Internal size
precision - Precision
scale - Scale
null_ok - Whether NULL values are allowed
field_flags - Field flags (extension to PEP-249)
table_name - Table name (extension to PEP-249)
original_column_name - Original column name (extension to PEP-249)
original_table_name - Original table name (extension to PEP-249)
org_field - Original column name
table - Table alias name, or original table name if no alias
org_table - Original table name
type - Column type (values from mariadb.constants.FIELD_TYPE)
charset - Numeric character set (collation) ID of the column
length - Maximum length of the column
max_length - Maximum length of the column (in version 2.0 this mirrors length)
decimals - Number of decimals for numeric types
flags - Field flags (values from mariadb.constants.FIELD_FLAG)
ext_type_or_format - Extended data type (values from mariadb.constants.EXT_FIELD_TYPE)
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
cursor.execute("SELECT id, name FROM users")
for row in cursor:
print(f"ID: {row[0]}, Name: {row[1]}")
cursor.close()
conn.close()
# Stream results to reduce memory usage
cursor = conn.cursor(buffered=False)
cursor.execute("SELECT * FROM large_table")
for row in cursor:
process_row(row) # Process one row at a time
cursor.close()
# Access columns by name
cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT id, name, email FROM users WHERE id = ?", (123,))
user = cursor.fetchone()
print(f"Name: {user['name']}, Email: {user['email']}")
cursor.close()
# Access columns as attributes
cursor = conn.cursor(named_tuple=True)
cursor.execute("SELECT id, name, email FROM users WHERE id = ?", (123,))
user = cursor.fetchone()
print(f"Name: {user.name}, Email: {user.email}")
cursor.close()
# Use prepared statements for this cursor
cursor = conn.cursor(binary=True)
# First execution prepares the statement
cursor.execute("SELECT * FROM users WHERE id = ?", (1,))
# Subsequent executions reuse the prepared statement
cursor.execute("SELECT * FROM users WHERE id = ?", (2,))
cursor.close()
# Dictionary cursor with streaming results
cursor = conn.cursor(dictionary=True, buffered=False)
cursor.execute("SELECT * FROM large_table")
for row in cursor:
print(f"Processing: {row['name']}")
cursor.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
cursor.execute("SELECT id, name, email FROM users")
# Fetch all rows at once
rows = cursor.fetchall()
for row in rows:
print(f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}")
print(f"Total rows: {len(rows)}")
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
cursor.execute("SELECT id, name FROM users ORDER BY id")
# Fetch rows in batches of 10
while True:
rows = cursor.fetchmany(10)
if not rows:
break
print(f"Processing batch of {len(rows)} rows")
for row in rows:
print(f" ID: {row[0]}, Name: {row[1]}")
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
cursor.execute("SELECT id, name FROM users WHERE id = ?", (1,))
# Fetch single row
row = cursor.fetchone()
if row:
print(f"User found: ID={row[0]}, Name={row[1]}")
else:
print("User not found")
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
# Set arraysize for batch processing
cursor.arraysize = 100
cursor.execute("SELECT * FROM large_table")
# fetchmany() will now fetch 100 rows at a time by default
while True:
rows = cursor.fetchmany()
if not rows:
break
print(f"Processing {len(rows)} rows")
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
# Unbuffered cursor (default) - streams results, low memory usage
cursor1 = conn.cursor(buffered=False)
print(f"Buffered: {cursor1.buffered}") # Output: False
cursor1.execute("SELECT * FROM large_table") # 1 million rows
# Rows are streamed one at a time, not all loaded into memory
for row in cursor1:
process_row(row) # Memory efficient
cursor1.close()
# Buffered cursor - fetches all results immediately into memory
cursor2 = conn.cursor(buffered=True)
print(f"Buffered: {cursor2.buffered}") # Output: True
cursor2.execute("SELECT * FROM small_table") # 100 rows
rows = cursor2.fetchall() # All rows loaded into memory at once
# Connection is now free for other operations
cursor2.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
# Use unbuffered for large result sets to avoid memory issues
cursor = conn.cursor(buffered=False)
cursor.execute("SELECT * FROM huge_table") # Millions of rows
# Process rows one at a time without loading all into memory
for row in cursor:
# Each row is fetched on demand
process_large_row(row)
cursor.close()
# Use buffered for small result sets when you need connection freedom
cursor = conn.cursor(buffered=True)
cursor.execute("SELECT * FROM config WHERE active = 1") # Few rows
config = cursor.fetchall() # Safe to load all into memory
cursor.close()
# Can now use connection for other operations immediately
cursor2 = conn.cursor()
cursor2.execute("SELECT COUNT(*) FROM users")
count = cursor2.fetchone()[0]
cursor2.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
# Using context manager (recommended - auto-closes)
with conn.cursor() as cursor:
cursor.execute("SELECT COUNT(*) FROM users")
count = cursor.fetchone()[0]
print(f"Total users: {count}")
# Cursor automatically closed here
# Manual close
cursor = conn.cursor()
try:
cursor.execute("SELECT * FROM users LIMIT 5")
rows = cursor.fetchall()
finally:
cursor.close() # Always close in finally block
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
cursor.execute("SELECT id, name, email, created_at FROM users LIMIT 1")
# Get column information
if cursor.description:
print("Column Information:")
for i, col in enumerate(cursor.description):
print(f"\nColumn {i}:")
print(f" Name: {col[0]}")
print(f" Type: {col[1]}")
print(f" Nullable: {col[6]}")
print(f" Table: {col[8]}")
print(f" Original Name: {col[9]}")
# Example output:
# Column 0:
# Name: id
# Type: 3
# Nullable: 0
# Table: users
# Original Name: id
cursor.close()
conn.close()
import mariadb
from mariadb.constants import FIELD_TYPE, FIELD_FLAG
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
cursor.execute("SELECT content FROM documents LIMIT 1")
if cursor.description[0][1] == FIELD_TYPE.BLOB:
if cursor.description[0][7] & FIELD_FLAG.BINARY:
print("Column is BLOB")
else:
print("Column is TEXT")
cursor.close()
conn.close()
import mariadb
from mariadb.constants import FIELD_TYPE, FIELD_FLAG, EXT_FIELD_TYPE
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
cursor.execute("""
SELECT
id,
name AS user_name,
email,
created_at
FROM users
LIMIT 1
""")
# Get complete metadata
metadata = cursor.metadata
if metadata:
print("Complete Column Metadata:")
print(f"Number of columns: {len(metadata['field'])}\n")
for i in range(len(metadata['field'])):
print(f"Column {i}:")
print(f" Field (alias): {metadata['field'][i]}")
print(f" Original field: {metadata['org_field'][i]}")
print(f" Table (alias): {metadata['table'][i]}")
print(f" Original table: {metadata['org_table'][i]}")
print(f" Schema: {metadata['schema'][i]}")
print(f" Type: {metadata['type'][i]}")
print(f" Charset: {metadata['charset'][i]}")
print(f" Length: {metadata['length'][i]}")
print(f" Max length: {metadata['max_length'][i]}")
print(f" Decimals: {metadata['decimals'][i]}")
print(f" Flags: {metadata['flags'][i]}")
print()
# Example output:
# Column 0:
# Field (alias): id
# Original field: id
# Table (alias): users
# Original table: users
# Schema: mydb
# Type: 3
# Charset: 63
# Length: 11
# Max length: 11
# Decimals: 0
# Flags: 16899
cursor.close()
conn.close()
import mariadb
from mariadb.constants import FIELD_TYPE, EXT_FIELD_TYPE
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
# Create table with extended types
cursor.execute("""
CREATE TEMPORARY TABLE test_types (
data JSON,
user_id UUID,
ip_addr INET4,
location POINT
)
""")
cursor.execute("SELECT data, user_id, ip_addr, location FROM test_types")
metadata = cursor.metadata
# Check extended types
for i, field_name in enumerate(metadata['field']):
ext_type = metadata['ext_type_or_format'][i]
base_type = metadata['type'][i]
print(f"{field_name}:")
print(f" Base type: {base_type}")
if ext_type == EXT_FIELD_TYPE.JSON:
print(f" Extended type: JSON")
elif ext_type == EXT_FIELD_TYPE.UUID:
print(f" Extended type: UUID")
elif ext_type == EXT_FIELD_TYPE.INET4:
print(f" Extended type: INET4")
elif ext_type == EXT_FIELD_TYPE.POINT:
print(f" Extended type: POINT (Geometry)")
print()
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
# Create a stored procedure with OUT parameter
cursor.execute("DROP PROCEDURE IF EXISTS calculate_total")
cursor.execute("""
CREATE PROCEDURE calculate_total(
IN user_id INT,
OUT total_amount DECIMAL(10,2)
)
BEGIN
SELECT SUM(amount) INTO total_amount
FROM orders
WHERE user_id = user_id;
END
""")
# Call the procedure with OUT parameter
cursor.callproc("calculate_total", (123, 0))
# First check if current result set contains output parameters
print(f"Has output params: {cursor.sp_outparams}") # Output: True
# Fetch the output parameter value
result = cursor.fetchone()
total = result[0]
print(f"Total amount: {total}")
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
# Create procedure that returns data AND has OUT parameter
cursor.execute("DROP PROCEDURE IF EXISTS get_user_stats")
cursor.execute("""
CREATE PROCEDURE get_user_stats(
IN user_id INT,
OUT order_count INT
)
BEGIN
-- First result set: user details
SELECT id, name, email FROM users WHERE id = user_id;
-- Set OUT parameter
SELECT COUNT(*) INTO order_count
FROM orders
WHERE user_id = user_id;
END
""")
# Call the procedure
cursor.callproc("get_user_stats", (123, 0))
# First result set: user details
print(f"Has output params: {cursor.sp_outparams}") # Output: False
user = cursor.fetchone()
print(f"User: {user}")
# Move to next result set (OUT parameters)
cursor.nextset()
print(f"Has output params: {cursor.sp_outparams}") # Output: True
out_params = cursor.fetchone()
order_count = out_params[0]
print(f"Order count: {order_count}")
cursor.execute("DROP PROCEDURE IF EXISTS get_user_stats")
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor(binary=True)
# Call procedure using CALL statement with binary protocol
cursor.execute("CALL calculate_total(?, ?)", (123, 0))
# Check if result contains output parameters
if cursor.sp_outparams:
result = cursor.fetchone()
print(f"Total: {result[0]}")
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor(buffered=False) # Stream rows from the server
# Execute SELECT
cursor.execute("SELECT * FROM users")
# Rowcount is -1 until all rows are fetched
print(f"Rowcount before fetch: {cursor.rowcount}") # Output: -1
# Fetch all rows
rows = cursor.fetchall()
# Now rowcount is available
print(f"Rowcount after fetch: {cursor.rowcount}") # Output: 150 (actual count)
print(f"Rows fetched: {len(rows)}") # Output: 150
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor(buffered=True)
# Execute SELECT
cursor.execute("SELECT * FROM users WHERE active = 1")
# Rowcount is immediately available for buffered cursors
print(f"Rowcount: {cursor.rowcount}") # Output: 42 (immediately)
# Fetch the rows
rows = cursor.fetchall()
print(f"Rows fetched: {len(rows)}") # Output: 42
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
# INSERT - rowcount shows affected rows
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)",
("John Doe", "john@example.com"))
print(f"Rows inserted: {cursor.rowcount}") # Output: 1
# UPDATE - rowcount shows affected rows
cursor.execute("UPDATE users SET active = 1 WHERE created_at < NOW()")
print(f"Rows updated: {cursor.rowcount}") # Output: 25
# DELETE - rowcount shows affected rows
cursor.execute("DELETE FROM users WHERE active = 0")
print(f"Rows deleted: {cursor.rowcount}") # Output: 10
# UPDATE with no matching rows
cursor.execute("UPDATE users SET active = 1 WHERE id = 99999")
print(f"Rows updated: {cursor.rowcount}") # Output: 0 (no rows matched)
conn.commit()
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
# Update a specific user
user_id = 123
cursor.execute("UPDATE users SET last_login = NOW() WHERE id = ?", (user_id,))
if cursor.rowcount == 0:
print(f"Warning: User {user_id} not found or not updated")
elif cursor.rowcount == 1:
print(f"User {user_id} updated successfully")
conn.commit()
else:
print(f"Error: Multiple rows affected ({cursor.rowcount})")
conn.rollback()
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
# Execute a query
cursor.execute("SELECT * FROM users WHERE id = ?", (123,))
# Get the executed statement
print(f"Last statement: {cursor.statement}")
# Output: Last statement: SELECT * FROM users WHERE id = ?
# Execute another query
cursor.execute("UPDATE users SET last_login = NOW() WHERE id = ?", (123,))
print(f"Last statement: {cursor.statement}")
# Output: Last statement: UPDATE users SET last_login = NOW() WHERE id = ?
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
# Execute statement that may generate warnings
cursor.execute("SET session sql_mode=''")
cursor.execute("CREATE TEMPORARY TABLE test_warn (a tinyint)")
cursor.execute("INSERT INTO test_warn VALUES (300)") # Out of range
# Check warning count from cursor
if cursor.warnings > 0:
print(f"Number of warnings: {cursor.warnings}")
# Get detailed warnings from connection
warnings = conn.show_warnings()
for level, code, message in warnings:
print(f"{level} ({code}): {message}")
# Output: Warning (1264): Out of range value for column 'a' at row 1
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
cursor.execute("SELECT id, name FROM users ORDER BY id LIMIT 5")
print(f"Initial rownumber: {cursor.rownumber}") # Output: 0
# Fetch rows one by one
row1 = cursor.fetchone()
print(f"After 1st fetch: {cursor.rownumber}") # Output: 1
row2 = cursor.fetchone()
print(f"After 2nd fetch: {cursor.rownumber}") # Output: 2
# Fetch remaining rows
remaining = cursor.fetchall()
print(f"After fetchall: {cursor.rownumber}") # Output: 5
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
# Before executing any query
print(f"Field count: {cursor.field_count}") # Output: 0
# Execute SELECT with 3 columns
cursor.execute("SELECT id, name, email FROM users LIMIT 1")
print(f"Field count: {cursor.field_count}") # Output: 3
# Execute SELECT with all columns
cursor.execute("SELECT * FROM users LIMIT 1")
print(f"Field count: {cursor.field_count}") # Output: (number of columns in users table)
# Execute non-SELECT statement
cursor.execute("UPDATE users SET active = 1 WHERE id = 1")
print(f"Field count: {cursor.field_count}") # Output: 0 (no result set)
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
print(f"Cursor closed: {cursor.closed}") # Output: False
# Execute a query
cursor.execute("SELECT 1")
print(f"Cursor closed: {cursor.closed}") # Output: False
# Close the cursor
cursor.close()
print(f"Cursor closed: {cursor.closed}") # Output: True
# Trying to use a closed cursor raises an error
try:
cursor.execute("SELECT 2")
except mariadb.ProgrammingError as e:
print(f"Error: {e}") # Output: Error: Cursor is closed
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
print(f"Cursor closed: {cursor.closed}") # Output: False
# Close the connection
conn.close()
# Cursor is now also considered closed
print(f"Cursor closed: {cursor.closed}") # Output: True
The connection class
MariaDB Connector/Python Connection class reference covers parameters, methods for commit and rollback, and read-only attributes for server version and TLS state.
class Connection(*args, **kwargs)
MariaDB Connector/Python Connection Object
Handles the connection to a MariaDB or MySQL database server. It encapsulates a database session.
Connections are created using the method mariadb.connect()
Connection Parameters
The mariadb.connect() function accepts the following parameters:
Basic Connection Parameters
host (str) - Hostname or IP address of the database server. Can be a comma-separated list for failover support (requires libmariadb 3.3 or later when used with the C extension). Default: 'localhost'
port (int) - Port number of the database server. Default: 3306
user, username (str) - Username for authentication. Default: None
password, passwd (str) - Password for authentication. Default: None
database, db (str) - Database (schema) name to use. Default: None
unix_socket (str) - Path to the Unix socket file for local connections. When host is localhost and this parameter is not set, the connector auto-detects the Linux distribution (via /etc/os-release) and uses the same default socket path that libmariadb is compiled with on that distro: /run/mysqld/mysqld.sock on Debian, Ubuntu, Arch, and Alpine; /var/lib/mysql/mysql.sock on Fedora, RHEL, and CentOS; /run/mysql/mysql.sock
protocol - Force a specific transport protocol. Accepted values are a case-insensitive string or an integer:
'DEFAULT' / 0 - connector chooses automatically (Unix socket for localhost when available, otherwise TCP)
connect_timeout (float) - Timeout in seconds for establishing the initial connection to the server. Default: 10.0
socket_timeout (float) - I/O timeout in seconds for socket operations (read and write). Primary timeout parameter for the pure-Python connector. Default: None
ssl, use_ssl (bool) - Enable SSL/TLS encryption. Default: True in version 2.0 (secure by default); False in the version 1.1 C extension
ssl_ca (
default_file (str) - Read connection options from a MariaDB/MySQL option file. Option files are read only if this parameter or default_group is set. Explicit connection arguments take precedence over option-file values. On Windows the file must be an .ini file. Default: None
None
For a description of configuration file handling and accepted settings, see the chapter of the MariaDB Connector/C documentation.
read_only (bool) - Set connection to read-only mode. Default: False
binary (bool) - Use binary protocol (prepared statements) by default. Default: False
max_allowed_packet (int) - Maximum packet size in bytes. Default: 16777216 (16MB)
The connection character set is fixed at utf8mb4 and is not configurable through a connection parameter.
named_tuple (bool) - Return rows as named tuples instead of regular tuples. Default: False
dictionary (bool) - Return rows as dictionaries instead of tuples. Default: False
converter (dict) - Custom type converter dictionary mapping field types to conversion functions. Default: None
Since version 2.0
The connector ships in three packaging variants — pure-Python, a binary wheel, and the C extension — and selects an implementation automatically at import time. Override the choice with the MARIADB_PYTHON_CONNECTOR environment variable:
Value
Implementation
Parameters tagged (C extension only) in the lists above are honored only when the C extension implementation is in use. On the pure-Python path those parameters are accepted but ignored, with the exception of read_timeout and write_timeout which are mapped to socket_timeout.
Basic connection with dictionary parameters:
Connection with SSL/TLS:
Connection with URI (since version 2.0):
Connection with timeouts and compression:
Connection with result format options:
Connection with failover hosts:
Creates a transaction ID object suitable for passing to the .tpc_*() methods of this connection.
Parameters:
format_id: Format id. Default to value 0.
global_transaction_id: Global transaction qualifier, which must be unique. The maximum length of the global transaction id is limited to 64 characters.
branch_qualifier: Branch qualifier which represents a local transaction identifier. The maximum length of the branch qualifier is limited to 64 characters.
Since version 1.0.1.
Example:
Start a new transaction which can be committed by .commit() method, or canceled by .rollback() method.
Since version 1.1.0.
Example:
Commit any pending transaction to the database.
Example:
Changes the user and default database of the current connection
Parameters: : - user: user name
password: password
database: name of default database
In order to successfully change users a valid username and password parameters must be provided and that user must have sufficient permissions to access the desired database. If for any reason authorization fails an exception will be raised and the current user authentication will remain.
Example:
Close the connection now (rather than whenever ._del_() is called).
The connection will be unusable from this point forward; an Error (or subclass) exception will be raised if any operation is attempted with the connection. The same applies to all cursor objects trying to use the connection.
Note that closing a connection without committing the changes first will cause an implicit rollback to be performed.
Example:
Create a new cursor for executing queries.
Parameters:
cursor_class (Optional[type]) - Optional custom cursor class (advanced usage)
**kwargs (Any) - Additional cursor parameters:
named_tuple
Returns:
Cursor object
Raises:
ProgrammingError - If connection is closed
By default, fetch methods return result set values as tuples. Use dictionary=True or named_tuple=True to change the return format.
Removed in version 2.0:
cursor_type - Use buffered=True for buffered results
prepared - Use binary=True instead
cursorclass - No longer supported as a parameter
Examples:
This function is designed to be executed by a user with the SUPER privilege and is used to dump server status information into the log for the MariaDB Server relating to the connection.
Since version 1.1.2.
Example:
Returns a tuple representing the version of the connected server in the following format: (MAJOR_VERSION, MINOR_VERSION, PATCH_VERSION)
Parameters: statement: string
This function is used to create a legal SQL string that you can use in an SQL statement. The given string is encoded to an escaped SQL string.
Since version 1.0.5.
Output:
This function is used to ask the server to kill a database connection specified by the connection_id parameter.
The connection id can be retrieved by SHOW PROCESSLIST SQL command.
Note: This function requires the SUPER or CONNECTION ADMIN privilege.
Example:
Checks if the connection to the database server is still available.
If auto reconnect was set to true, an attempt will be made to reconnect to the database server in case the connection was lost
If the connection is not available an InterfaceError will be raised.
Example:
tries to reconnect to a server in case the connection died due to timeout or other errors. It uses the same credentials which were specified in connect() method.
Example:
Resets the current connection and clears session state and pending results. Open cursors will become invalid and cannot be used anymore.
Example:
Causes the database to roll back to the start of any pending transaction
Closing a connection without committing the changes first will cause an implicit rollback to be performed. Note that rollback() will not work as expected if autocommit mode was set to True or the storage engine does not support transactions.”
Gets the default database for the current connection.
The default database can also be obtained or changed by database attribute.
Since version 1.1.0.
Example:
Shows error, warning and note messages from last executed command.
Example:
Parameter: : xid: xid object which was created by .xid() method of connection : class
Begins a TPC transaction with the given transaction ID xid.
This method should be called outside a transaction (i.e., nothing may have been executed since the last .commit() or .rollback()). Furthermore, it is an error to call .commit() or .rollback() within the TPC transaction. A ProgrammingError is raised if the application calls .commit() or .rollback() during an active TPC transaction.
Example:
Optional parameter:
xid : xid object which was created by .xid() method of connection class.
When called with no arguments, .tpc_commit() commits a TPC transaction previously prepared with .tpc_prepare().
If .tpc_commit() is called prior to .tpc_prepare(), a single phase commit is performed. A transaction manager may choose to do this if only a single resource is participating in the global transaction. When called with a transaction ID xid, the database commits the given transaction. If an invalid transaction ID is provided, a ProgrammingError will be raised. This form should be called outside a transaction, and is intended for use in recovery.
Example (Two-Phase Commit):
Performs the first phase of a transaction started with .tpc_begin(). A ProgrammingError will be raised if this method was called outside a TPC transaction.
After calling .tpc_prepare(), no statements can be executed until .tpc_commit() or .tpc_rollback() have been called.
Example:
Returns a list of pending transaction IDs suitable for use with tpc_commit(xid) or .tpc_rollback(xid).
Example:
Parameter: : xid: xid object which was created by .xid() method of connection : class
Performs the first phase of a transaction started with .tpc_begin(). A ProgrammingError will be raised if this method outside a TPC transaction.
After calling .tpc_prepare(), no statements can be executed until .tpc_commit() or .tpc_rollback() have been called.
Example:
removed in version 2.0
(read/write)
Enable or disable automatic reconnection to the server if the connection is found to have been lost.
When enabled, client tries to reconnect to a database server in case the connection to a database server died due to timeout or other errors.
(read/write)
Toggles autocommit mode on or off for the current database connection.
Autocommit mode only affects operations on transactional table types. Be aware that rollback() will not work if autocommit mode was switched on.
By default, autocommit mode is set to False.
(read-only)
Client character set.
For MariaDB Connector/Python, it is always utf8mb4.
(read-only)
Client capability flags.
Since version 1.1.0.
(read-only)
Client character set collation
(read-only)
Id of current connection
(read/write)
Returns or sets the default database for the current connection. Assigning to this attribute switches the default database (issuing a USE statement).
Since version 1.1.0.
(read-only)
Returns true if the connection is alive.
A ping command will be sent to the server for this purpose, which means this function might fail if there are still non-processed pending result sets.
Since version 1.1.0.
(read-only)
Server capability flags.
Since version 1.1.0.
(read-only)
Extended server capability flags (only for MariaDB database servers).
Since version 1.1.0.
(read-only)
Server version in alphanumerical format (str)
(read-only)
Returns True if the connected server is MariaDB, False if it's MySQL.
This property is useful for detecting server type and implementing server-specific logic.
Example:
(read-only)
Returns the server name.
(read-only)
Database server TCP/IP port. This value will be 0 in case of an unix socket connection.
(read-only)
Return server status flags
Since version 1.1.0.
(read-only)
Returns an integer representing the server version.
The form of the version number is VERSION_MAJOR * 10000 + VERSION_MINOR * 100 + VERSION_PATCH
(read-only)
Returns numeric version of connected database server in tuple format.
(read-only)
Returns the TLS cipher suite in use.
Since version 1.0.5.
(read-only)
Returns the TLS protocol version.
(read-only)
Returns peer certificate information for TLS connections.
Since version 1.1.11.
(read-only)
Returns the unix socket file name.
(read-only)
Returns the username for the current connection or empty string if it can’t be determined, e.g., when using socket authentication.
(read-only)
Returns the number of warnings from the last executed statement., or zero if there are no warnings.
MariaDB Connector/Python FAQ
MariaDB Connector/Python FAQ addresses common installation issues, migration from 1.1 to 2.0, the binary vs text protocol distinction, async setup, and transaction commit requirements.
This is a list of frequently asked questions about MariaDB Connector/Python. Feel free to suggest new entries!
- Connection parameters, methods, and attributes
- Cursor parameters, methods, and attributes
on openSUSE and SLES. On non-Linux platforms (Windows, macOS) or unknown distributions no auto-detection happens and the connection falls back to TCP.
/tmp/mysql.sock
is intentionally not probed (the
/tmp
directory is world-writable, which would allow a non-privileged attacker to plant a fake socket); pass
unix_socket='/tmp/mysql.sock'
explicitly if that path is required. Default:
None
'TCP' / 1 - force TCP/IP even when host is localhost
'SOCKET' / 2 - force Unix socket (requires unix_socket to be set or auto-detected)
Default: 'DEFAULT'
(no timeout; blocking)
query_timeout (int) - Maximum query execution time in seconds (0 means no timeout). Default: 0
read_timeout(C extension only) (float) - Read (receive) timeout in seconds, passed directly to libmariadb. On the pure-Python connector this value is accepted but mapped to socket_timeout. Default: same as socket_timeout
write_timeout(C extension only) (float) - Write (send) timeout in seconds, passed directly to libmariadb. On the pure-Python connector this value is accepted but mapped to socket_timeout. Default: same as socket_timeout
str
) - Path to Certificate Authority (CA) certificate file in PEM format. Default:
None
ssl_cert (str) - Path to client certificate file in PEM format. Default: None
ssl_key (str) - Path to client private key file in PEM format. Default: None
ssl_capath (str) - Path to directory containing CA certificates in PEM format. Default: None
ssl_cipher (str) - List of permitted cipher suites for SSL/TLS. Default: None
ssl_crl (str) - Path to certificate revocation list file. Default: None
ssl_verify_cert (bool) - Enable server certificate verification. Default: True in version 2.0; False in version 1.1
tls_version (str) - TLS version(s) to use (e.g., 'TLSv1.2', 'TLSv1.3', 'TLSv1.2,TLSv1.3'). Automatically enables SSL. Default: None
tls_fp(C extension only) (str) - SHA-256 fingerprint of the expected server certificate, used for certificate pinning. Default: None
tls_fp_list(C extension only) (str) - Path to a file containing a list of accepted server certificate SHA-256 fingerprints. Default: None
(the default) reads no option file.
A path reads only that file.
An empty string ("") reads the default option files instead: /etc, /etc/mysql, $MARIADB_HOME/$MYSQL_HOME, and ~/.my.cnf.
default_group (str) - An additional option-file group to read, on top of the always-read [client], [client-server], and [client-mariadb] groups. Setting it without default_file triggers reading of the default option files. Default: None
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
# Create a transaction ID for distributed transaction
xid = conn.xid(1, "global_tx_12345", "branch_001")
print(f"XID: {xid}") # Output: (1, 'global_tx_12345', 'branch_001')
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
# Start explicit transaction
conn.begin()
cursor = conn.cursor()
cursor.execute("INSERT INTO accounts (name, balance) VALUES (?, ?)", ("Alice", 1000))
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE name = ?", ("Alice",))
# Commit the transaction
conn.commit()
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
try:
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)",
("John Doe", "john@example.com"))
cursor.execute("INSERT INTO logs (action) VALUES (?)",
("User created",))
# Commit both inserts as a single transaction
conn.commit()
print("Transaction committed successfully")
except mariadb.Error as e:
# Rollback on error
conn.rollback()
print(f"Error: {e}")
finally:
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
print(f"Current user: {conn.user}")
print(f"Current database: {conn.database}")
# Switch to a different user and database
try:
conn.change_user("app_user", "app_pass", "app_db")
print(f"Changed to user: {conn.user}")
print(f"Changed to database: {conn.database}")
except mariadb.Error as e:
print(f"Failed to change user: {e}")
finally:
conn.close()
import mariadb
# Using context manager (recommended - auto-closes)
with mariadb.connect("mariadb://user:password@localhost/mydb") as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
users = cursor.fetchall()
cursor.close()
# Connection automatically closed here
# Manual close
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
try:
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM users")
count = cursor.fetchone()[0]
print(f"Total users: {count}")
finally:
conn.close() # Always close in finally block
# Default cursor (unbuffered, text protocol, returns tuples)
cursor = conn.cursor()
# Buffered cursor (stores entire result set in memory)
cursor = conn.cursor(buffered=True)
# Binary protocol cursor (prepared statements)
cursor = conn.cursor(binary=True)
# Dictionary cursor (access columns by name)
cursor = conn.cursor(dictionary=True)
row = cursor.fetchone()
print(row['column_name'])
# Named tuple cursor (access columns as attributes)
cursor = conn.cursor(named_tuple=True)
row = cursor.fetchone()
print(row.column_name)
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
try:
# Dump debug information to server log
# Requires SUPER privilege
conn.dump_debug_info()
print("Debug info dumped to server log")
except mariadb.Error as e:
print(f"Error: {e}")
# Output: Error: Access denied; you need (at least one of) the SUPER privilege(s)
conn.close()
# connection parameters
conn_params= {
"user" : "example_user",
"password" : "GHbe_Su3B8",
"host" : "localhost"
}
with mariadb.connect(**conn_params) as connection:
string = 'This string contains the following special characters: \\,"'
print(connection.escape_string(string))
This string contains the following special characters: \\,\"
import mariadb
import time
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
# Get list of active connections
cursor.execute("SHOW PROCESSLIST")
processes = cursor.fetchall()
print("Active connections:")
for proc in processes:
conn_id, user, host, db, command, time_val, state, info = proc
print(f"ID: {conn_id}, User: {user}, DB: {db}, Command: {command}")
# Kill a specific connection (requires SUPER privilege)
target_connection_id = 123
try:
conn.kill(target_connection_id)
print(f"Connection {target_connection_id} killed successfully")
except mariadb.Error as e:
print(f"Error killing connection: {e}")
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
try:
# Check if connection is alive
conn.ping()
print("Connection is alive")
except mariadb.InterfaceError:
print("Connection lost")
finally:
conn.close()
import mariadb
import time
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
try:
cursor = conn.cursor()
cursor.execute("SELECT 1")
cursor.close()
# Simulate connection timeout or network issue
# In real scenario, connection might be lost
# Reconnect with same credentials
conn.reconnect()
print("Reconnected successfully")
cursor = conn.cursor()
cursor.execute("SELECT 2")
cursor.close()
except mariadb.Error as e:
print(f"Error: {e}")
finally:
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
# Set some session variables
cursor.execute("SET @my_var = 100")
cursor.execute("SELECT @my_var")
print(cursor.fetchone()) # Output: (100,)
# Reset connection - clears session state
conn.reset()
# Previous cursor is now invalid, create new one
cursor = conn.cursor()
cursor.execute("SELECT @my_var")
print(cursor.fetchone()) # Output: (None,) - variable cleared
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
cursor = conn.cursor()
# Initially no warnings
print(conn.show_warnings()) # Output: None
# Generate a warning by inserting value out of range
cursor.execute("SET session sql_mode=''")
cursor.execute("CREATE TEMPORARY TABLE test_warn (a tinyint)")
cursor.execute("INSERT INTO test_warn VALUES (300)") # Value too large for tinyint
# Get warnings
warnings = conn.show_warnings()
if warnings:
for level, code, message in warnings:
print(f"{level} ({code}): {message}")
# Output: Warning (1264): Out of range value for column 'a' at row 1
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
# Create transaction ID
xid = conn.xid(0, "global_tx_001", "branch_001")
# Begin distributed transaction
conn.tpc_begin(xid)
cursor = conn.cursor()
cursor.execute("INSERT INTO orders (product, quantity) VALUES (?, ?)", ("Widget", 10))
cursor.close()
# Single-phase commit (no prepare)
conn.tpc_commit(xid)
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
xid = conn.xid(0, "global_tx_003", "branch_003")
conn.tpc_begin(xid)
cursor = conn.cursor()
cursor.execute("INSERT INTO transactions (amount) VALUES (?)", (100.00,))
cursor.close()
# Prepare the transaction (phase 1 of 2PC)
conn.tpc_prepare()
# At this point, transaction is prepared but not committed
# Can now commit or rollback
conn.tpc_commit()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
# Get list of pending prepared transactions
pending_xids = conn.tpc_recover()
if pending_xids:
print(f"Found {len(pending_xids)} pending transactions")
for xid_data in pending_xids:
print(f"Pending XID: {xid_data}")
# Can commit or rollback these transactions
# xid = conn.xid(*xid_data)
# conn.tpc_commit(xid)
else:
print("No pending transactions")
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
xid = conn.xid(0, "global_tx_004", "branch_004")
conn.tpc_begin(xid)
cursor = conn.cursor()
try:
cursor.execute("UPDATE accounts SET balance = balance - 1000 WHERE id = ?", (1,))
cursor.execute("UPDATE accounts SET balance = balance + 1000 WHERE id = ?", (2,))
# Check for errors
cursor.execute("SELECT balance FROM accounts WHERE id = 1")
balance = cursor.fetchone()[0]
if balance < 0:
# Rollback the distributed transaction
conn.tpc_rollback()
print("Transaction rolled back - insufficient funds")
else:
conn.tpc_prepare()
conn.tpc_commit()
print("Transaction committed")
except mariadb.Error as e:
conn.tpc_rollback()
print(f"Error: {e}")
finally:
cursor.close()
conn.close()
import mariadb
conn = mariadb.connect("mariadb://user:password@localhost/mydb")
if conn.server_mariadb:
print("Connected to MariaDB server")
print(f"Version: {conn.server_info}")
# Use MariaDB-specific features
cursor = conn.cursor()
cursor.execute("SELECT JSON_DETAILED('{\"a\": 1}')")
else:
print("Connected to MySQL server")
print(f"Version: {conn.server_info}")
# Use MySQL-compatible features only
conn.close()
The header files and libraries of the Python development package weren’t properly installed. Use your package manager to install them system-wide:
Alpine (using apk):
Ubuntu/Debian (using apt):
CentOS/RHEL (using yum):
Fedora (using dnf):
MacOSX (using homebrew):
OpenSuse (using zypper):
Note: The python3 development packages of your distribution might not cover all minor versions of python3. If you are using python3.10 you may need to install python3.10-dev.
MariaDB Connector/C is bundled - no separate installation needed
C extension from source - pip install --pre mariadb[c]
Requires MariaDB Connector/C 3.3.1+ to be pre-installed on your system
Maximum performance
For connection pooling, add [pool] to any option:
No, not anymore! This is a major change in version 2.0:
Pure Python (default): No MariaDB Connector/C required
Binary wheels (mariadb[binary]): No separate installation needed - MariaDB Connector/C is bundled inside the wheel
C extension from source (mariadb[c]): Yes, requires MariaDB Connector/C 3.3.1+ to be pre-installed on your system
For most users, pip install --pre mariadb[binary,pool] provides the best experience with no external dependencies (the --pre flag is required while 2.0 is a Release Candidate).
With deprecation of distutils (see PEP-632) version functions of distutils module were replaced in MariaDB Connector/Python 1.1.5 by packaging version functions.
Before you can install MariaDB Connector/Python you have to install the packaging module:
The previously installed version of MariaDB Connector/C is too old and cannot be used for the MariaDB Connector/Python version you are trying to install.
To determine the installed version of MariaDB Connector/C, execute the command:
Check if your distribution can be upgraded to a more recent version of MariaDB Connector/C, which fits the requirements.
If your distribution doesn’t provide a recent version of MariaDB Connector/C, check the MariaDB Connector Download page, which provides latest versions for the major distributions.
If none of the above will work for you, build and install MariaDB Connector/C from source.
The mariadb_config program is used to retrieve configuration information (such as the location of header files and libraries, installed version, etc.) from MariaDB Connector/C.
This error indicates that MariaDB Connector/C, an important dependency for client/server communication that needs to be preinstalled, either was not installed or could not be found.
If MariaDB Connector/C was previously installed, the installation script cannot detect the location of mariadb_config. Locate the directory where mariadb_config was installed and add this directory to your PATH.
If MariaDB Connector/C was not installed and the location of mariadb_config couldn’t be detected, please install MariaDB Connector/C.
Even if the correct version of MariaDB Connector/C was installed, there are multiple mysql.h include files installed on your system, either from libmysql or an older MariaDB Connector/C installation. This can be checked by executing:
Open output.txt in your favourite editor and search for “search starts here” where you can see the include files and paths used for the build.
If your distribution doesn’t provide a recent version of MariaDB Connector/C (required version is 3.3.1) you either can download a version of MariaDB Connector/C from the MariaDB Connector Download page or build the package from source:
No. If an issue was fixed, the fix will be available in the next release via Python’s package manager repository (pypi.org).
To build MariaDB Connector/Python from github sources, checkout latest sources from github:
and build and install it with:
Check if MariaDB server has been started.
Check if the MariaDB server was correctly configured and uses the right socket file:
mysqld --help --verbose | grep socket
If the socket is different and cannot be changed, you can specify the socket in your connection parameters.
Both prepared and binary already existed as separate cursor options in version 1.1. In version 2.0, prepared is deprecated in favor of binary; passing prepared still works but emits a DeprecationWarning:
Version 1.1 (either option):
Version 2.0 (use binary):
Both use the MariaDB binary protocol (prepared statements).
Text protocol (default):
Predictable behavior
Good for ad-hoc queries
No preparation overhead
Binary protocol (binary=True):
Better performance for repeated queries
Automatic prepared statement caching
Recommended for hot paths
Enable at connection level for applications that mostly use parameterized queries:
MariaDB Connector/Python uses MariaDB Connector/C for client-server communication (C extension only). The pure Python implementation supports standard authentication methods. All authentication plugins shipped together with MariaDB Connector/C can be used for user authentication in the C extension.
Executing multiple statements in a single cursor.execute() call is not supported.
Python versions which reached their end of life are not officially supported. While MariaDB Connector/Python might still work with older Python 3.x versions, it doesn’t work with Python version 2.x.
No, there is no mogrify() method. Before a statement is executed, parameter markers other than question marks are rewritten to question marks; the statement and its data are then sent separately to the server, so the parameter values are not substituted into the SQL string on the client side.
Example:
Please note, that there is no need to escape ‘%s’ by ‘%%s’ for the time conversion in DATE_FORMAT() function.
The default paramstyle (see PEP-249) is qmark (question mark) for parameter markers. For compatibility with other drivers MariaDB Connector/Python also supports (and automatically recognizes) the format and pyformat parameter styles.
Mixing different paramstyles within the same query is not supported and will raise an exception.
Default for autocommit in MariaDB Connector/Python is off, which means every transaction must be committed. Uncommitted pending transactions are rolled back automatically when the connection is closed.
data = ("Future", 2000)
statement = """SELECT DATE_FORMAT(creation_time, '%h:%m:%s') as time, topic, amount
FROM mytable WHERE topic=%s and id > %s"""
cursor.execute(statement, data)
print(cursor.statement)
SELECT DATE_FORMAT(creation_time, '%h:%m:%s') as time, topic, amount FROM mytable WHERE topic=? and id > ?
.. code-block:: python
with mariadb.connect(**conn_params) as conn:
with conn.cursor() as cursor:
cursor.execute("CREATE TABLE t1 (id int, name varchar(20))")
# insert
data = [(1, "Andy"), (2, "George"), (3, "Betty")]
cursor.executemany("INSERT INTO t1 VALUES (?,?)", data)
# commit pending transactions
connection.commit()
Installation
Error: “Python.h: No such file or directory”
Which installation option should I choose for version 2.0?
Version 2.0 is currently a Release Candidate (RC); version 1.1 is the latest stable (GA) release. The 2.0 commands below use the --pre flag because pip otherwise installs the latest stable release (1.1). Do not use non-stable (non-GA) releases in production.
Do I need MariaDB Connector/C for version 2.0?
ModuleNotFoundError: No module named ‘packaging’
MariaDB Connector/Python requires MariaDB Connector/C >= 3.3.1, found version 3.1.2
OSError: mariadb_config not found
Error: struct st_mariadb_methods’ has no member named ‘db_execute_generate_request’
Q: My distribution doesn’t provide a recent version of MariaDB Connector/C
Q: Does MariaDB Connector/Python provide pre-releases or snapshot builds which contain recent bug fixes?
Q: How can I build an actual version from github sources?
Connecting
mariadb.OperationalError: Can’t connect to local server through socket ‘/tmp/mysql.sock’
How do I migrate from version 1.1 to 2.0?
What happened to auto_reconnect?
How do I use async/await with version 2.0?
What's the difference between prepared and binary?
Should I use text or binary protocol?
Q: Which authentication methods are supported by MariaDB Connector/Python?
General
Q: How do I execute multiple statements with cursor.execute()?
Q: Does MariaDB Connector/Python work with Python 2.x?
Q: How can I see a transformed statement? Is there a mogrify() method available?
Q: Does MariaDB Connector/Python support paramstyle “pyformat”?
Transactions
Q: Previously inserted records disappeared after my program finished
Maximum performance without compilation
No compiler required
Requires C compiler for building
For custom builds or platforms without binary wheels
Connection Pooling
MariaDB Connector/Python 2.0 connection pooling supports sync and async pools via create_pool and create_async_pool, with configurable size, health checks, and context managers.
Since version 2.0: Connection pooling is now a separate optional package. Install with:
pip install --pre mariadb[pool]
Version 2.0 is currently a Release Candidate (RC), so the --pre flag is required. Version 1.1 (the latest stable/GA release) includes connection pooling by default.
A connection pool is a cache of connections to a database server where connections can be reused for future requests. Since establishing a connection is resource-expensive and time-consuming, especially when used inside a middle tier environment which maintains multiple connections and requires connections to be immediately available on the fly.
Especially for server-side web applications, a connection pool is the standard way to maintain a pool of database connections which are reused across requests.
Version 2.0 introduces:
Synchronous pools with create_pool()
Asynchronous pools with create_async_pool()
Improved API with min_size and max_size parameters
Context manager support with acquire()
The typical way for creating and using a connection pool is:
Create (and configure) a connection pool
Obtain a connection from connection pool using acquire()
Perform database operation(s)
Since version 2.0
Create a synchronous connection pool using create_pool():
Pool Configuration Parameters:
min_size (int) - Minimum number of connections in the pool. Default: same than max_size
max_size (int) - Maximum number of connections in the pool. Default: 10
Connection Release Behavior:
When a connection is returned to the pool (either explicitly or via context manager), the pool automatically handles cleanup:
If reset_connection=True: Calls conn.reset() to clear all session state (session variables, temporary tables, prepared statements) without reconnecting. This ensures a clean state for the next user but adds overhead.
If reset_connection=False (default): Checks if the connection has an active transaction. If a transaction is in progress, it automatically calls conn.rollback() to prevent transaction leakage between pool users.
Best Practices:
Use reset_connection=True if you need guaranteed clean state (e.g., different users sharing a pool with session-specific settings)
Use reset_connection=False (default) for better performance when session state doesn't matter
Always commit or rollback transactions explicitly before releasing connections for clarity
Connection Parameters:
All connection parameters from mariadb.connect() are supported (host, user, password, database, ssl_ca, etc.)
Example - Synchronous Pool:
Example - Connection Parameters:
The pool factories do not accept a connection URI string; pass connection settings as keyword arguments.
Since version 2.0
Create an asynchronous connection pool for async/await applications:
Version 1.1:
Version 2.0:
Return the connection to the pool (automatically with context managers)
max_idle_time (float) - Maximum time (seconds) a connection can be idle before being closed. Default: 600.0 (10 minutes)
max_lifetime (float) - Maximum lifetime (seconds) of a connection before being replaced. Default: 3600.0 (1 hour)
validation_interval (float) - Interval (seconds) between health checks. Default: 30.0
acquire_timeout (float) - Timeout (seconds) when acquiring a connection from the pool. Default: 30.0
enable_health_check (bool) - Enable periodic health checks on pooled connections. Default: True
reset_connection (bool) - Reset connection state when returning to pool (clears session variables, temporary tables, and prepared statements). Default: False
ping_threshold (float) - Ping connection if idle for more than this many seconds (0 = disabled). Default: 0.25
Configuring and using a connection pool
Synchronous Connection Pool
Asynchronous Connection Pool
FastAPI Example
Migration from Version 1.1
import mariadb
# Create pool with configuration
pool = mariadb.create_pool(
host="localhost",
user="example_user",
password="GHbe_Su3B8",
database="test",
min_size=5,
max_size=20,
max_idle_time=600.0,
max_lifetime=3600.0,
ping_threshold=0.25,
enable_health_check=True
)
# Acquire connection from pool
with pool.acquire() as conn:
with conn.cursor() as cursor:
cursor.execute("SELECT COUNT(*) FROM users")
count = cursor.fetchone()[0]
print(f"Total users: {count}")
# Connection automatically returned to pool
pool = mariadb.create_pool(
host="localhost",
user="example_user",
password="GHbe_Su3B8",
database="test",
min_size=10,
max_size=50
)
import asyncio
import mariadb
async def main():
# Create async pool with configuration
pool = await mariadb.create_async_pool(
host="localhost",
user="example_user",
password="GHbe_Su3B8",
database="test",
min_size=5,
max_size=20,
max_idle_time=600.0,
acquire_timeout=30.0,
enable_health_check=True
)
# Acquire connection from pool
async with await pool.acquire() as conn:
async with conn.cursor() as cursor:
await cursor.execute("SELECT COUNT(*) FROM users")
count = (await cursor.fetchone())[0]
print(f"Total users: {count}")
# Close pool when done
await pool.close()
asyncio.run(main())
from fastapi import FastAPI
from contextlib import asynccontextmanager
import mariadb
pool = None
@asynccontextmanager
async def lifespan(app: FastAPI):
global pool
# Startup: Create pool
pool = await mariadb.create_async_pool(
host="localhost",
user="user",
password="password",
database="mydb",
min_size=10,
max_size=50
)
yield
# Shutdown: Close pool
await pool.close()
app = FastAPI(lifespan=lifespan)
@app.get("/users/{user_id}")
async def get_user(user_id: int):
async with await pool.acquire() as conn:
async with conn.cursor(dictionary=True) as cursor:
await cursor.execute(
"SELECT id, name, email FROM users WHERE id = ?",
(user_id,)
)
return await cursor.fetchone()
# Install pooling package first (--pre is required while 2.0 is an RC)
# pip install --pre mariadb[pool]
pool = mariadb.create_pool(
host="localhost",
user="user",
password="password",
min_size=5,
max_size=10
)
with pool.acquire() as conn:
# Use connection
pass