0
# Connection Pooling
1

2
Manage pools of database connections for scalable applications. Connection pooling improves performance by reusing connections and provides better resource management for multi-user applications. Supports both synchronous and asynchronous pool operations with extensive configuration options.
3

4
## Capabilities
5

6
### Creating Connection Pools
7

8
Create and configure connection pools with various settings for optimal performance and resource utilization.
9

10
```python { .api }
11
def create_pool(
12
    user=None,
13
    password=None,
14
    dsn=None,
15
    min=1,
16
    max=2,
17
    increment=1,
18
    connectiontype=Connection,
19
    getmode=POOL_GETMODE_WAIT,
20
    homogeneous=True,
21
    timeout=0,
22
    wait_timeout=0,
23
    max_lifetime_session=0,
24
    session_callback=None,
25
    max_sessions_per_shard=0,
26
    soda_metadata_cache=False,
27
    ping_interval=60,
28
    **kwargs
29
) -> ConnectionPool:
30
    """
31
    Create a connection pool.
32
    
33
    Parameters:
34
    - user (str): Username for authentication
35
    - password (str): Password for authentication
36
    - dsn (str): Data source name
37
    - min (int): Minimum number of connections in pool
38
    - max (int): Maximum number of connections in pool
39
    - increment (int): Number of connections to create when pool needs to grow
40
    - connectiontype: Connection class to use (Connection or AsyncConnection)
41
    - getmode (int): Pool get mode (POOL_GETMODE_WAIT, POOL_GETMODE_NOWAIT, etc.)
42
    - homogeneous (bool): Whether all connections use same credentials
43
    - timeout (int): Connection timeout in seconds
44
    - wait_timeout (int): Time to wait for available connection
45
    - max_lifetime_session (int): Maximum session lifetime in seconds
46
    - session_callback: Callback function for new sessions
47
    - ping_interval (int): Interval for connection health checks
48
    
49
    Returns:
50
    ConnectionPool object
51
    """
52

53
def create_pool_async(
54
    user=None,
55
    password=None,
56
    dsn=None,
57
    min=1,
58
    max=2,
59
    increment=1,
60
    **kwargs
61
) -> AsyncConnectionPool:
62
    """
63
    Create an asynchronous connection pool.
64
    
65
    Parameters: Same as create_pool()
66
    
67
    Returns:
68
    AsyncConnectionPool object
69
    """
70

71
def get_pool(name=None) -> ConnectionPool:
72
    """
73
    Get a named connection pool.
74
    
75
    Parameters:
76
    - name (str): Pool name (None for default pool)
77
    
78
    Returns:
79
    ConnectionPool object
80
    """
81
```
82

83
### ConnectionPool Class
84

85
Manage a pool of database connections with automatic scaling and resource management.
86

87
```python { .api }
88
class ConnectionPool:
89
    """Synchronous connection pool."""
90
    
91
    # Properties
92
    min: int
93
    max: int
94
    increment: int
95
    opened: int
96
    busy: int
97
    timeout: int
98
    getmode: int
99
    homogeneous: bool
100
    name: str
101
    dsn: str
102
    username: str
103
    wait_timeout: int
104
    max_lifetime_session: int
105
    max_sessions_per_shard: int
106
    soda_metadata_cache: bool
107
    ping_interval: int
108
    
109
    def acquire(
110
        self,
111
        user=None,
112
        password=None,
113
        cclass=None,
114
        purity=PURITY_DEFAULT,
115
        tag=None,
116
        matchanytag=False,
117
        shardingkey=None,
118
        supershardingkey=None
119
    ) -> Connection:
120
        """
121
        Acquire a connection from the pool.
122
        
123
        Parameters:
124
        - user (str): Username (for heterogeneous pools)
125
        - password (str): Password (for heterogeneous pools)
126
        - cclass (str): Connection class for session pooling
127
        - purity (int): Session purity level
128
        - tag (str): Session tag
129
        - matchanytag (bool): Match any tagged session
130
        - shardingkey (list): Sharding key for database sharding
131
        - supershardingkey (list): Super sharding key
132
        
133
        Returns:
134
        Connection object from pool
135
        """
136
    
137
    def release(self, connection, tag=None) -> None:
138
        """
139
        Release a connection back to the pool.
140
        
141
        Parameters:
142
        - connection: Connection object to release
143
        - tag (str): Tag to associate with released session
144
        """
145
    
146
    def close(self, force=False) -> None:
147
        """
148
        Close the connection pool and all connections.
149
        
150
        Parameters:
151
        - force (bool): Force close even with active connections
152
        """
153
    
154
    def drop(self, connection) -> None:
155
        """
156
        Drop a connection from the pool permanently.
157
        
158
        Parameters:
159
        - connection: Connection object to drop
160
        """
161
    
162
    def reconfigure(
163
        self,
164
        min=None,
165
        max=None,
166
        increment=None,
167
        timeout=None,
168
        getmode=None,
169
        wait_timeout=None,
170
        max_lifetime_session=None,
171
        ping_interval=None,
172
        **kwargs
173
    ) -> None:
174
        """
175
        Reconfigure pool parameters.
176
        
177
        Parameters:
178
        - min (int): New minimum connections
179
        - max (int): New maximum connections  
180
        - increment (int): New increment value
181
        - timeout (int): New connection timeout
182
        - getmode (int): New get mode
183
        - wait_timeout (int): New wait timeout
184
        - max_lifetime_session (int): New max session lifetime
185
        - ping_interval (int): New ping interval
186
        """
187
```
188

189
### AsyncConnectionPool Class
190

191
Asynchronous version of ConnectionPool with async/await support.
192

193
```python { .api }
194
class AsyncConnectionPool:
195
    """Asynchronous connection pool."""
196
    
197
    # Properties (same as ConnectionPool)
198
    min: int
199
    max: int
200
    increment: int
201
    opened: int
202
    busy: int
203
    timeout: int
204
    getmode: int
205
    homogeneous: bool
206
    name: str
207
    dsn: str
208
    username: str
209
    wait_timeout: int
210
    max_lifetime_session: int
211
    
212
    async def acquire(
213
        self,
214
        user=None,
215
        password=None,
216
        cclass=None,
217
        purity=PURITY_DEFAULT,
218
        tag=None,
219
        matchanytag=False,
220
        shardingkey=None,
221
        supershardingkey=None
222
    ) -> AsyncConnection:
223
        """
224
        Acquire an async connection from the pool.
225
        
226
        Parameters: Same as ConnectionPool.acquire()
227
        
228
        Returns:
229
        AsyncConnection object from pool
230
        """
231
    
232
    async def release(self, connection, tag=None) -> None:
233
        """
234
        Release an async connection back to the pool.
235
        
236
        Parameters:
237
        - connection: AsyncConnection object to release
238
        - tag (str): Tag to associate with released session
239
        """
240
    
241
    async def close(self, force=False) -> None:
242
        """
243
        Close the async connection pool and all connections.
244
        
245
        Parameters:
246
        - force (bool): Force close even with active connections
247
        """
248
```
249

250
### Pool Parameters
251

252
Configure pool parameters using PoolParams class.
253

254
```python { .api }
255
class PoolParams:
256
    """Pool parameter configuration."""
257
    
258
    user: str
259
    password: str
260
    dsn: str
261
    min: int
262
    max: int
263
    increment: int
264
    connectiontype: type
265
    getmode: int
266
    homogeneous: bool
267
    timeout: int
268
    wait_timeout: int
269
    max_lifetime_session: int
270
    session_callback: callable
271
    max_sessions_per_shard: int
272
    soda_metadata_cache: bool
273
    ping_interval: int
274
    stmtcachesize: int
275
    edition: str
276
    events: bool
277
    externalauth: bool
278
    mode: int
279
    threaded: bool
280
    appcontext: list
281
    encoding: str
282
    nencoding: str
283
    tag: str
284
    matchanytag: bool
285
    config_dir: str
286
    appname: str
287
    disable_oob: bool
288
```
289

290
### Pool Get Modes
291

292
Constants for controlling connection acquisition behavior.
293

294
```python { .api }
295
# Pool Get Mode Constants
296
POOL_GETMODE_WAIT: int        # Wait for available connection
297
POOL_GETMODE_NOWAIT: int      # Return immediately if no connection available
298
POOL_GETMODE_FORCEGET: int    # Create new connection beyond max limit
299
POOL_GETMODE_TIMEDWAIT: int   # Wait with timeout
300
```
301

302
## Usage Examples
303

304
### Basic Connection Pool
305

306
```python
307
import oracledb
308

309
# Create a basic connection pool
310
pool = oracledb.create_pool(
311
    user="hr",
312
    password="password", 
313
    dsn="localhost/xepdb1",
314
    min=2,
315
    max=10,
316
    increment=2
317
)
318

319
print(f"Pool created with {pool.opened} connections")
320

321
# Acquire connection from pool
322
connection = pool.acquire()
323
print(f"Pool now has {pool.busy} busy connections")
324

325
with connection.cursor() as cursor:
326
    cursor.execute("SELECT COUNT(*) FROM employees")
327
    count = cursor.fetchone()[0]
328
    print(f"Employee count: {count}")
329

330
# Release connection back to pool
331
pool.release(connection)
332
print(f"Pool now has {pool.busy} busy connections")
333

334
# Close the pool
335
pool.close()
336
```
337

338
### Advanced Pool Configuration
339

340
```python
341
import oracledb
342

343
def session_callback(connection, requested_tag):
344
    """Callback function called when session is returned from pool."""
345
    print(f"Session callback called with tag: {requested_tag}")
346
    
347
    # Set session state based on tag
348
    if requested_tag == "REPORTING":
349
        with connection.cursor() as cursor:
350
            cursor.execute("ALTER SESSION SET optimizer_mode = FIRST_ROWS")
351
    elif requested_tag == "BATCH":
352
        with connection.cursor() as cursor:
353
            cursor.execute("ALTER SESSION SET optimizer_mode = ALL_ROWS")
354

355
# Create pool with advanced configuration
356
pool = oracledb.create_pool(
357
    user="hr",
358
    password="password",
359
    dsn="localhost/xepdb1",
360
    min=5,
361
    max=20,
362
    increment=3,
363
    getmode=oracledb.POOL_GETMODE_WAIT,
364
    timeout=300,  # 5 minutes
365
    wait_timeout=30,  # 30 seconds wait for connection
366
    max_lifetime_session=3600,  # 1 hour max session life
367
    session_callback=session_callback,
368
    ping_interval=60,  # Ping every 60 seconds
369
    homogeneous=True
370
)
371

372
# Acquire connection with specific session requirements
373
connection = pool.acquire(
374
    cclass="REPORTING",
375
    purity=oracledb.PURITY_SELF,
376
    tag="REPORTING"
377
)
378

379
# Use connection for reporting queries
380
with connection.cursor() as cursor:
381
    cursor.execute("SELECT department_name, COUNT(*) FROM departments d JOIN employees e ON d.department_id = e.department_id GROUP BY department_name")
382
    for row in cursor:
383
        print(f"Department {row[0]}: {row[1]} employees")
384

385
# Release with tag for future reuse
386
pool.release(connection, tag="REPORTING")
387

388
pool.close()
389
```
390

391
### Heterogeneous Pool
392

393
```python
394
import oracledb
395

396
# Create heterogeneous pool (different users can connect)
397
pool = oracledb.create_pool(
398
    dsn="localhost/xepdb1",
399
    min=3,
400
    max=15,
401
    increment=2,
402
    homogeneous=False  # Allow different users
403
)
404

405
# Different users can acquire connections
406
hr_connection = pool.acquire(user="hr", password="hr_password")
407
sales_connection = pool.acquire(user="sales", password="sales_password")
408

409
# Use connections with different privileges
410
with hr_connection.cursor() as cursor:
411
    cursor.execute("SELECT COUNT(*) FROM employees")
412
    hr_count = cursor.fetchone()[0]
413
    print(f"HR can see {hr_count} employees")
414

415
with sales_connection.cursor() as cursor:
416
    cursor.execute("SELECT COUNT(*) FROM customers")  
417
    sales_count = cursor.fetchone()[0]
418
    print(f"Sales can see {sales_count} customers")
419

420
# Release connections
421
pool.release(hr_connection)
422
pool.release(sales_connection)
423

424
pool.close()
425
```
426

427
### Async Connection Pool
428

429
```python
430
import asyncio
431
import oracledb
432

433
async def main():
434
    # Create async connection pool
435
    pool = await oracledb.create_pool_async(
436
        user="hr",
437
        password="password",
438
        dsn="localhost/xepdb1",
439
        min=3,
440
        max=12,
441
        increment=3
442
    )
443
    
444
    print(f"Async pool created with {pool.opened} connections")
445
    
446
    # Acquire async connection
447
    connection = await pool.acquire()
448
    
449
    async with connection.cursor() as cursor:
450
        await cursor.execute("SELECT employee_id, first_name FROM employees WHERE ROWNUM <= 5")
451
        async for row in cursor:
452
            print(f"Employee {row[0]}: {row[1]}")
453
    
454
    # Release connection
455
    await pool.release(connection)
456
    
457
    # Close pool
458
    await pool.close()
459

460
asyncio.run(main())
461
```
462

463
### Pool Monitoring and Management
464

465
```python
466
import oracledb
467
import time
468

469
# Create pool with monitoring
470
pool = oracledb.create_pool(
471
    user="hr",
472
    password="password",
473
    dsn="localhost/xepdb1",
474
    min=2,
475
    max=8,
476
    increment=2
477
)
478

479
def monitor_pool():
480
    """Monitor pool statistics."""
481
    print(f"Pool Stats:")
482
    print(f"  Opened: {pool.opened}")
483
    print(f"  Busy: {pool.busy}")
484
    print(f"  Available: {pool.opened - pool.busy}")
485
    print(f"  Min: {pool.min}, Max: {pool.max}")
486
    print()
487

488
# Initial stats
489
monitor_pool()
490

491
# Acquire multiple connections to see pool growth
492
connections = []
493
for i in range(5):
494
    conn = pool.acquire()
495
    connections.append(conn)
496
    print(f"Acquired connection {i+1}")
497
    monitor_pool()
498

499
# Release connections
500
for i, conn in enumerate(connections):
501
    pool.release(conn)
502
    print(f"Released connection {i+1}")
503
    monitor_pool()
504

505
# Reconfigure pool
506
print("Reconfiguring pool...")
507
pool.reconfigure(min=4, max=12, increment=3)
508
monitor_pool()
509

510
pool.close()
511
```
512

513
### Context Manager Usage
514

515
```python
516
import oracledb
517

518
# Using pool and connections with context managers
519
pool = oracledb.create_pool(
520
    user="hr",
521
    password="password",
522
    dsn="localhost/xepdb1",
523
    min=2,
524
    max=8
525
)
526

527
# Context manager automatically handles acquire/release
528
with pool.acquire() as connection:
529
    with connection.cursor() as cursor:
530
        cursor.execute("SELECT department_id, department_name FROM departments")
531
        for row in cursor:
532
            print(f"Department {row[0]}: {row[1]}")
533
    # Connection is automatically released here
534

535
# Pool remains open for reuse
536
print(f"Pool still has {pool.opened} connections")
537

538
pool.close()
539
```