Since version 2.0: Connection pooling is now a separate optional package. Install with:
pip install mariadb[pool]
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()
Configuring and using a connection pool
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)
Synchronous Connection Pool
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 - URI Connection:
Asynchronous Connection Pool
Since version 2.0
Create an asynchronous connection pool for async/await applications:
FastAPI Example
Migration from Version 1.1
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
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(
"mariadb://example_user:GHbe_Su3B8@localhost/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(
"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,)
)
return await cursor.fetchone()