1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
|
#------------------------------------------------------------------------------
# Copyright (c) 2017, 2021, Oracle and/or its affiliates. All rights reserved.
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# connection_pool.py
# This script demonstrates the use of connection pooling. Pools can
# significantly reduce connection times for long running applications that
# repeatedly open and close connections. Internal features help protect against
# dead connections, and also aid use of Oracle Database features such as FAN
# and Application Continuity.
# The script uses threading to show multiple users of the pool. One thread
# performs a database sleep while another performs a query. A more typical
# application might be a web service that handles requests from multiple users.
# Note only one operation (such as an execute or fetch) can take place at a time
# on each connection.
#
# Also see session_callback.py.
#
#------------------------------------------------------------------------------
import threading
import cx_Oracle as oracledb
import sample_env
# Create a Connection Pool
pool = oracledb.SessionPool(user=sample_env.get_main_user(),
password=sample_env.get_main_password(),
dsn=sample_env.get_connect_string(), min=2, max=5,
increment=1)
def the_long_query():
with pool.acquire() as conn:
cursor = conn.cursor()
cursor.arraysize = 25000
print("the_long_query(): beginning execute...")
cursor.execute("""
select *
from
TestNumbers
cross join TestNumbers
cross join TestNumbers
cross join TestNumbers
cross join TestNumbers
cross join TestNumbers""")
print("the_long_query(): done execute...")
while True:
rows = cursor.fetchmany()
if not rows:
break
print("the_long_query(): fetched", len(rows), "rows...")
print("the_long_query(): all done!")
def do_a_lock():
with pool.acquire() as conn:
# dbms_session.sleep() replaces dbms_lock.sleep()
# from Oracle Database 18c
sleep_proc_name = "dbms_session.sleep" \
if int(conn.version.split(".")[0]) >= 18 \
else "dbms_lock.sleep"
cursor = conn.cursor()
print("do_a_lock(): beginning execute...")
cursor.callproc(sleep_proc_name, (5,))
print("do_a_lock(): done execute...")
thread1 = threading.Thread(target=the_long_query)
thread1.start()
thread2 = threading.Thread(target=do_a_lock)
thread2.start()
thread1.join()
thread2.join()
print("All done!")
|
