0
# Connection Pooling
1
2
Manage connection pools for high-performance applications with automatic connection lifecycle management, configurable pool sizes, and connection reuse strategies.
3
4
## Core Pooling Classes
5
6
### MySQLConnectionPool
7
8
```python { .api }
9
class MySQLConnectionPool:
10
"""
11
Connection pool manager with configurable size and behavior.
12
Manages a pool of database connections for efficient reuse.
13
"""
14
15
def __init__(self,
16
pool_name: Optional[str] = None,
17
pool_size: int = 5,
18
pool_reset_session: bool = True,
19
**kwargs) -> None:
20
"""
21
Initialize connection pool.
22
23
Args:
24
pool_name: Unique identifier for the pool
25
pool_size: Maximum number of connections (default: 5)
26
pool_reset_session: Reset session on connection reuse (default: True)
27
**kwargs: Connection parameters passed to individual connections
28
"""
29
pass
30
31
def get_connection(self) -> 'PooledMySQLConnection':
32
"""
33
Get connection from pool.
34
35
Returns:
36
PooledMySQLConnection instance
37
38
Raises:
39
PoolError: When pool is exhausted and timeout reached
40
"""
41
pass
42
43
def add_connection(self, cnx: Optional['MySQLConnection'] = None) -> 'PooledMySQLConnection':
44
"""Add connection to pool or create new one."""
45
pass
46
47
def set_config(self, **kwargs) -> None:
48
"""Update pool configuration."""
49
pass
50
51
@property
52
def pool_name(self) -> str:
53
"""Pool name identifier."""
54
pass
55
56
@pool_name.setter
57
def pool_name(self, value: str) -> None:
58
"""Set pool name."""
59
pass
60
61
@property
62
def pool_size(self) -> int:
63
"""Maximum pool size."""
64
pass
65
66
@pool_size.setter
67
def pool_size(self, value: int) -> None:
68
"""Set maximum pool size."""
69
pass
70
71
@property
72
def pool_reset_session(self) -> bool:
73
"""Whether to reset session on connection reuse."""
74
pass
75
76
@pool_reset_session.setter
77
def pool_reset_session(self, value: bool) -> None:
78
"""Set session reset behavior."""
79
pass
80
81
def close(self) -> None:
82
"""Close all connections in pool."""
83
pass
84
85
def __del__(self) -> None:
86
"""Cleanup pool on deletion."""
87
pass
88
```
89
90
### PooledMySQLConnection
91
92
```python { .api }
93
class PooledMySQLConnection:
94
"""
95
Pooled connection wrapper that returns connection to pool on close.
96
Provides same interface as MySQLConnection with pool integration.
97
"""
98
99
def __init__(self, pool: MySQLConnectionPool, cnx: 'MySQLConnection') -> None:
100
"""Initialize pooled connection wrapper."""
101
pass
102
103
def close(self) -> None:
104
"""Return connection to pool instead of closing."""
105
pass
106
107
def config(self, **kwargs) -> None:
108
"""Configure underlying connection."""
109
pass
110
111
@property
112
def pool_name(self) -> str:
113
"""Name of the connection pool."""
114
pass
115
116
def __getattr__(self, name: str) -> Any:
117
"""Delegate attribute access to underlying connection."""
118
pass
119
120
def __enter__(self) -> 'PooledMySQLConnection':
121
"""Context manager entry."""
122
pass
123
124
def __exit__(self, exc_type, exc_val, exc_tb) -> None:
125
"""Context manager exit returning connection to pool."""
126
pass
127
```
128
129
## Pooling Functions
130
131
### connect() with Pooling
132
133
```python { .api }
134
def connect(**kwargs) -> Union[MySQLConnection, PooledMySQLConnection]:
135
"""
136
Create database connection with optional pooling.
137
138
When pool parameters are provided, returns PooledMySQLConnection.
139
Otherwise returns standard MySQLConnection.
140
141
Pool Parameters:
142
pool_name: Pool identifier
143
pool_size: Maximum connections in pool
144
pool_reset_session: Reset session variables on reuse
145
pool_timeout: Maximum wait time for available connection
146
147
Returns:
148
PooledMySQLConnection when pooling, MySQLConnection otherwise
149
"""
150
pass
151
152
def generate_pool_name(**kwargs) -> str:
153
"""
154
Generate pool name from connection parameters.
155
156
Creates unique pool name based on host, port, user, and database.
157
158
Returns:
159
Generated pool name string
160
"""
161
pass
162
```
163
164
## Pool Configuration
165
166
### Basic Pool Parameters
167
168
```python { .api }
169
pool_config = {
170
'pool_name': str, # Unique pool identifier
171
'pool_size': int, # Maximum connections (default: 5, max: 32)
172
'pool_reset_session': bool, # Reset session on reuse (default: True)
173
'pool_timeout': int, # Wait timeout in seconds (default: 0 = no timeout)
174
}
175
```
176
177
### Connection Parameters for Pool
178
179
All standard connection parameters can be used with pooling:
180
181
```python { .api }
182
pooled_connection_config = {
183
# Pool-specific
184
'pool_name': 'myapp_pool',
185
'pool_size': 10,
186
'pool_reset_session': True,
187
'pool_timeout': 30,
188
189
# Connection parameters
190
'host': 'localhost',
191
'port': 3306,
192
'user': 'myuser',
193
'password': 'mypassword',
194
'database': 'mydatabase',
195
'charset': 'utf8mb4',
196
'autocommit': False,
197
198
# SSL parameters
199
'ssl_disabled': False,
200
'ssl_verify_cert': True,
201
202
# Performance parameters
203
'use_pure': False, # Use C extension if available
204
'buffered': True,
205
'compress': False,
206
}
207
```
208
209
## Usage Examples
210
211
### Basic Connection Pool
212
213
```python
214
import mysql.connector
215
216
# Create connection pool
217
config = {
218
'host': 'localhost',
219
'user': 'myuser',
220
'password': 'mypassword',
221
'database': 'mydatabase',
222
'pool_name': 'myapp_pool',
223
'pool_size': 5
224
}
225
226
# Get pooled connection
227
connection = mysql.connector.connect(**config)
228
print(f"Connected via pool: {connection.pool_name}")
229
230
cursor = connection.cursor()
231
cursor.execute("SELECT COUNT(*) FROM users")
232
count = cursor.fetchone()[0]
233
print(f"User count: {count}")
234
235
cursor.close()
236
connection.close() # Returns connection to pool
237
```
238
239
### Explicit Pool Management
240
241
```python
242
import mysql.connector
243
244
# Create pool explicitly
245
pool_config = {
246
'host': 'localhost',
247
'user': 'myuser',
248
'password': 'mypassword',
249
'database': 'mydatabase'
250
}
251
252
pool = mysql.connector.pooling.MySQLConnectionPool(
253
pool_name='explicit_pool',
254
pool_size=8,
255
pool_reset_session=True,
256
**pool_config
257
)
258
259
# Get connections from pool
260
try:
261
connection1 = pool.get_connection()
262
connection2 = pool.get_connection()
263
264
# Use connections
265
cursor1 = connection1.cursor()
266
cursor1.execute("SELECT 'Connection 1' as source")
267
result1 = cursor1.fetchone()
268
print(result1)
269
270
cursor2 = connection2.cursor()
271
cursor2.execute("SELECT 'Connection 2' as source")
272
result2 = cursor2.fetchone()
273
print(result2)
274
275
finally:
276
# Return connections to pool
277
cursor1.close()
278
cursor2.close()
279
connection1.close()
280
connection2.close()
281
282
# Close entire pool
283
pool.close()
284
```
285
286
### Pool with Context Managers
287
288
```python
289
import mysql.connector
290
291
config = {
292
'host': 'localhost',
293
'user': 'myuser',
294
'password': 'mypassword',
295
'database': 'mydatabase',
296
'pool_name': 'context_pool',
297
'pool_size': 3
298
}
299
300
# Automatic connection return to pool
301
with mysql.connector.connect(**config) as connection:
302
with connection.cursor(dictionary=True) as cursor:
303
cursor.execute("SELECT id, name FROM users LIMIT 5")
304
users = cursor.fetchall()
305
306
for user in users:
307
print(f"User {user['id']}: {user['name']}")
308
# Cursor automatically closed
309
# Connection automatically returned to pool
310
```
311
312
### Multiple Workers with Shared Pool
313
314
```python
315
import mysql.connector
316
import threading
317
import time
318
319
# Shared pool configuration
320
pool_config = {
321
'host': 'localhost',
322
'user': 'myuser',
323
'password': 'mypassword',
324
'database': 'mydatabase',
325
'pool_name': 'worker_pool',
326
'pool_size': 5,
327
'pool_timeout': 10 # Wait up to 10 seconds for connection
328
}
329
330
def worker_task(worker_id: int):
331
"""Worker function that uses pooled connection."""
332
try:
333
# Get connection from shared pool
334
connection = mysql.connector.connect(**pool_config)
335
336
cursor = connection.cursor()
337
cursor.execute("SELECT SLEEP(%s)", (1,)) # Simulate work
338
cursor.fetchone()
339
340
print(f"Worker {worker_id} completed using pool connection")
341
342
cursor.close()
343
connection.close() # Return to pool
344
345
except mysql.connector.PoolError as err:
346
print(f"Worker {worker_id} failed to get connection: {err}")
347
348
# Create multiple worker threads
349
threads = []
350
for i in range(10): # More workers than pool size
351
thread = threading.Thread(target=worker_task, args=(i,))
352
threads.append(thread)
353
thread.start()
354
355
# Wait for all workers to complete
356
for thread in threads:
357
thread.join()
358
359
print("All workers completed")
360
```
361
362
### Pool with Failover Configuration
363
364
```python
365
import mysql.connector
366
367
# Pool with failover servers
368
config = {
369
'user': 'myuser',
370
'password': 'mypassword',
371
'database': 'mydatabase',
372
'pool_name': 'failover_pool',
373
'pool_size': 5,
374
'failover': [
375
{'host': 'primary.mysql.example.com', 'port': 3306},
376
{'host': 'secondary.mysql.example.com', 'port': 3306},
377
{'host': 'tertiary.mysql.example.com', 'port': 3306}
378
]
379
}
380
381
try:
382
connection = mysql.connector.connect(**config)
383
print(f"Connected to MySQL via pool: {connection.pool_name}")
384
385
cursor = connection.cursor()
386
cursor.execute("SELECT @@hostname as server")
387
server = cursor.fetchone()[0]
388
print(f"Connected to server: {server}")
389
390
cursor.close()
391
connection.close()
392
393
except mysql.connector.Error as err:
394
print(f"Connection failed: {err}")
395
```
396
397
### Pool Monitoring
398
399
```python
400
import mysql.connector
401
import threading
402
403
# Create pool
404
pool = mysql.connector.pooling.MySQLConnectionPool(
405
pool_name='monitored_pool',
406
pool_size=3,
407
host='localhost',
408
user='myuser',
409
password='mypassword',
410
database='mydatabase'
411
)
412
413
def monitor_pool():
414
"""Monitor pool usage."""
415
while True:
416
# Note: These are conceptual - actual implementation may vary
417
print(f"Pool size: {pool.pool_size}")
418
print(f"Pool name: {pool.pool_name}")
419
time.sleep(5)
420
421
# Start monitoring thread
422
monitor_thread = threading.Thread(target=monitor_pool, daemon=True)
423
monitor_thread.start()
424
425
# Use pool connections
426
connections = []
427
try:
428
# Get multiple connections
429
for i in range(3):
430
conn = pool.get_connection()
431
connections.append(conn)
432
print(f"Got connection {i+1}")
433
434
# Try to get one more (should wait or fail based on timeout)
435
try:
436
extra_conn = pool.get_connection()
437
print("Got extra connection")
438
connections.append(extra_conn)
439
except mysql.connector.PoolError as err:
440
print(f"Pool exhausted: {err}")
441
442
finally:
443
# Return all connections
444
for conn in connections:
445
conn.close()
446
pool.close()
447
```
448
449
### Advanced Pool Configuration
450
451
```python
452
import mysql.connector
453
454
# Advanced pool with session reset
455
config = {
456
'host': 'localhost',
457
'user': 'myuser',
458
'password': 'mypassword',
459
'database': 'mydatabase',
460
'pool_name': 'advanced_pool',
461
'pool_size': 10,
462
'pool_reset_session': True, # Reset session variables
463
'pool_timeout': 30, # Wait timeout
464
465
# Connection tuning
466
'connect_timeout': 10,
467
'read_timeout': 30,
468
'write_timeout': 30,
469
'charset': 'utf8mb4',
470
'collation': 'utf8mb4_unicode_ci',
471
'autocommit': False,
472
'buffered': True,
473
'use_pure': False, # Use C extension
474
475
# SSL configuration
476
'ssl_disabled': False,
477
'ssl_verify_cert': True,
478
'ssl_verify_identity': True,
479
}
480
481
# Use advanced pool
482
connection = mysql.connector.connect(**config)
483
484
# Connection has session reset to default state
485
cursor = connection.cursor()
486
cursor.execute("SELECT @@autocommit, @@sql_mode")
487
settings = cursor.fetchone()
488
print(f"Autocommit: {settings[0]}, SQL Mode: {settings[1]}")
489
490
cursor.close()
491
connection.close()
492
```