0
# Connection Pooling
1
2
Manage multiple database connections efficiently with connection pooling. Pools maintain a collection of reusable connections, reducing the overhead of creating and destroying connections for each database operation.
3
4
## Capabilities
5
6
### Pool Creation
7
8
Create a connection pool with configurable minimum and maximum connection limits.
9
10
```python { .api }
11
def create_pool(
12
minsize: int = 1,
13
maxsize: int = 10,
14
echo: bool = False,
15
pool_recycle: int = -1,
16
loop = None,
17
**kwargs
18
) -> _PoolContextManager:
19
"""
20
Create a connection pool.
21
22
Parameters:
23
- minsize: Minimum number of connections in pool
24
- maxsize: Maximum number of connections in pool (0 = unlimited)
25
- echo: Enable query logging for all connections
26
- pool_recycle: Seconds after which to recreate connections (-1 = disabled)
27
- loop: Event loop to use
28
- **kwargs: Connection parameters (same as connect() function)
29
30
Returns:
31
Pool context manager
32
"""
33
```
34
35
### Pool Management
36
37
The Pool class manages connection lifecycle, acquisition, and release operations.
38
39
```python { .api }
40
class Pool:
41
@property
42
def minsize(self) -> int:
43
"""Minimum pool size."""
44
45
@property
46
def maxsize(self) -> int:
47
"""Maximum pool size."""
48
49
@property
50
def size(self) -> int:
51
"""Current total number of connections."""
52
53
@property
54
def freesize(self) -> int:
55
"""Number of free connections available."""
56
57
@property
58
def closed(self) -> bool:
59
"""Whether the pool is closed."""
60
61
def acquire(self) -> Connection:
62
"""
63
Acquire a connection from the pool.
64
65
Returns:
66
Connection context manager
67
"""
68
69
def release(self, conn: Connection) -> None:
70
"""
71
Return a connection to the pool.
72
73
Parameters:
74
- conn: Connection to release back to pool
75
"""
76
77
async def clear(self) -> None:
78
"""
79
Close all free connections in the pool.
80
"""
81
82
def close(self) -> None:
83
"""
84
Close the pool. Mark all connections for closure when returned.
85
"""
86
87
def terminate(self) -> None:
88
"""
89
Terminate the pool immediately, closing all connections.
90
"""
91
92
async def wait_closed(self) -> None:
93
"""
94
Wait for the pool to be completely closed.
95
"""
96
```
97
98
## Usage Examples
99
100
### Basic Pool Usage
101
102
```python
103
import asyncio
104
import aiomysql
105
106
async def pool_example():
107
# Create connection pool
108
pool = await aiomysql.create_pool(
109
host='localhost',
110
port=3306,
111
minsize=1,
112
maxsize=5,
113
user='myuser',
114
password='mypass',
115
db='mydatabase'
116
)
117
118
# Acquire connection from pool
119
async with pool.acquire() as conn:
120
async with conn.cursor() as cur:
121
await cur.execute("SELECT COUNT(*) FROM users")
122
count = await cur.fetchone()
123
print(f"Total users: {count[0]}")
124
125
# Connection automatically returned to pool
126
127
# Close pool when done
128
pool.close()
129
await pool.wait_closed()
130
131
asyncio.run(pool_example())
132
```
133
134
### Pool with Connection Recycling
135
136
```python
137
async def recycling_pool():
138
# Create pool with connection recycling
139
pool = await aiomysql.create_pool(
140
host='localhost',
141
user='myuser',
142
password='mypass',
143
db='mydatabase',
144
minsize=2,
145
maxsize=10,
146
pool_recycle=3600, # Recreate connections every hour
147
echo=True # Enable query logging
148
)
149
150
# Use pool for multiple operations
151
for i in range(5):
152
async with pool.acquire() as conn:
153
async with conn.cursor() as cur:
154
await cur.execute("SELECT SLEEP(1)")
155
print(f"Operation {i+1} completed")
156
157
print(f"Pool size: {pool.size}, Free: {pool.freesize}")
158
159
# Cleanup
160
pool.close()
161
await pool.wait_closed()
162
```
163
164
### Concurrent Pool Operations
165
166
```python
167
async def worker(pool, worker_id):
168
"""Worker function that uses pool connections."""
169
for i in range(3):
170
async with pool.acquire() as conn:
171
async with conn.cursor() as cur:
172
await cur.execute("SELECT %s, %s", (worker_id, i))
173
result = await cur.fetchone()
174
print(f"Worker {worker_id}, iteration {i}: {result}")
175
176
# Simulate work
177
await asyncio.sleep(0.1)
178
179
async def concurrent_example():
180
# Create pool
181
pool = await aiomysql.create_pool(
182
host='localhost',
183
user='myuser',
184
password='mypass',
185
db='mydatabase',
186
minsize=2,
187
maxsize=5
188
)
189
190
# Run multiple workers concurrently
191
tasks = [worker(pool, i) for i in range(4)]
192
await asyncio.gather(*tasks)
193
194
print(f"Final pool stats - Size: {pool.size}, Free: {pool.freesize}")
195
196
# Cleanup
197
pool.close()
198
await pool.wait_closed()
199
200
asyncio.run(concurrent_example())
201
```
202
203
### Context Manager Usage
204
205
```python
206
async def context_manager_example():
207
# Pool can be used as async context manager
208
async with aiomysql.create_pool(
209
host='localhost',
210
user='myuser',
211
password='mypass',
212
db='mydatabase',
213
minsize=1,
214
maxsize=3
215
) as pool:
216
217
# Multiple operations using the pool
218
async with pool.acquire() as conn1:
219
async with conn1.cursor() as cur:
220
await cur.execute("INSERT INTO logs (message) VALUES ('Start')")
221
222
async with pool.acquire() as conn2:
223
async with conn2.cursor() as cur:
224
await cur.execute("INSERT INTO logs (message) VALUES ('Middle')")
225
226
async with pool.acquire() as conn3:
227
async with conn3.cursor() as cur:
228
await cur.execute("INSERT INTO logs (message) VALUES ('End')")
229
230
# Pool automatically closed when exiting context
231
print("Pool operations completed and pool closed")
232
```
233
234
### Error Handling with Pools
235
236
```python
237
async def error_handling_example():
238
pool = await aiomysql.create_pool(
239
host='localhost',
240
user='myuser',
241
password='mypass',
242
db='mydatabase',
243
minsize=1,
244
maxsize=3
245
)
246
247
try:
248
async with pool.acquire() as conn:
249
async with conn.cursor() as cur:
250
# This will cause an error
251
await cur.execute("SELECT * FROM nonexistent_table")
252
253
except aiomysql.ProgrammingError as e:
254
print(f"SQL error: {e}")
255
# Connection is still returned to pool even after error
256
257
except aiomysql.OperationalError as e:
258
print(f"Connection error: {e}")
259
# Pool will handle bad connections automatically
260
261
finally:
262
# Always clean up pool
263
pool.close()
264
await pool.wait_closed()
265
```