Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 20686a1

Browse filesBrowse files
anthony-tuiningaanthony-tuininga
committed
Various documentation and samples improvements.
1 parent 633371d commit 20686a1
Copy full SHA for 20686a1
Expand file treeCollapse file tree

13 files changed

+229
-55
lines changed

‎README.md

Copy file name to clipboardExpand all lines: README.md
+2-1Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,8 @@ cx_Oracle 8 has been tested with Python versions 3.5 through 3.8. You can use
1111
cx_Oracle with Oracle 11.2, 12c, 18c and 19c client libraries. Oracle's
1212
standard client-server version interoperability allows connection to both older
1313
and newer databases. For example Oracle 19c client libraries can connect to
14-
Oracle Database 11.2.
14+
Oracle Database 11.2. Older versions of cx_Oracle may work with older
15+
versions of Python.
1516

1617
## Installation
1718

‎doc/src/api_manual/connection.rst

Copy file name to clipboardExpand all lines: doc/src/api_manual/connection.rst
+2Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -441,6 +441,8 @@ Connection Object
441441
None, the default behavior will take place for all columns fetched from
442442
cursors.
443443

444+
See :ref:`outputtypehandlers`.
445+
444446
.. note::
445447

446448
This attribute is an extension to the DB API definition.

‎doc/src/api_manual/cursor.rst

Copy file name to clipboardExpand all lines: doc/src/api_manual/cursor.rst
+2Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -473,6 +473,8 @@ Cursor Object
473473
would normally be returned, and the result of the method is returned
474474
instead.
475475

476+
See :ref:`rowfactories`.
477+
476478
.. note::
477479

478480
The DB API definition does not define this attribute.

‎doc/src/user_guide/batch_statement.rst

Copy file name to clipboardExpand all lines: doc/src/user_guide/batch_statement.rst
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@ Batch Statement Execution and Bulk Loading
77
Inserting or updating multiple rows can be performed efficiently with
88
:meth:`Cursor.executemany()`, making it easy to work with large data sets with
99
cx_Oracle. This method can significantly outperform repeated calls to
10-
:meth:`Cursor.execute()` by reducing network transfer costs and database load.
10+
:meth:`Cursor.execute()` by reducing network transfer costs and database overheads.
1111
The :meth:`~Cursor.executemany()` method can also be used to execute PL/SQL
1212
statements multiple times at once.
1313

‎doc/src/user_guide/connection_handling.rst

Copy file name to clipboardExpand all lines: doc/src/user_guide/connection_handling.rst
+6-7Lines changed: 6 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -426,8 +426,8 @@ set the ``threaded`` parameter to *True* when creating a connection pool:
426426
pool = cx_Oracle.SessionPool("hr", userpwd, "dbhost.example.com/orclpdb1",
427427
min=2, max=5, increment=1, threaded=True, encoding="UTF-8")
428428
429-
See `Threads.py
430-
<https://github.com/oracle/python-cx_Oracle/tree/master/samples/Threads.py>`__
429+
See `ConnectionPool.py
430+
<https://github.com/oracle/python-cx_Oracle/tree/master/samples/ConnectionPool.py>`__
431431
for an example.
432432

433433
Before :meth:`SessionPool.acquire()` returns, cx_Oracle does a lightweight check
@@ -806,7 +806,8 @@ be returned to the Python application.
806806
Although applications can choose whether or not to use pooled connections at
807807
runtime, care must be taken to configure the database appropriately for the
808808
number of expected connections, and also to stop inadvertent use of non-DRCP
809-
connections leading to a resource shortage.
809+
connections leading to a database server resource shortage. Conversely, avoid
810+
using DRCP connections for long-running operations.
810811

811812
The example below shows how to connect to Oracle Database using Database
812813
Resident Connection Pooling:
@@ -842,17 +843,15 @@ then allows maximum use of DRCP pooled servers by the database:
842843
.. code-block:: python
843844
844845
# Do some database operations
845-
connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com/orclpdb1:pooled",
846-
encoding="UTF-8")
846+
connection = mypool.acquire(cclass="MYCLASS", purity=cx_Oracle.ATTR_PURITY_SELF)
847847
. . .
848848
connection.close();
849849
850850
# Do lots of non-database work
851851
. . .
852852
853853
# Do some more database operations
854-
connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com/orclpdb1:pooled",
855-
encoding="UTF-8")
854+
connection = mypool.acquire(cclass="MYCLASS", purity=cx_Oracle.ATTR_PURITY_SELF)
856855
. . .
857856
connection.close();
858857

‎doc/src/user_guide/plsql_execution.rst

Copy file name to clipboardExpand all lines: doc/src/user_guide/plsql_execution.rst
+16-15Lines changed: 16 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -154,31 +154,32 @@ For example:
154154
.. code-block:: python
155155
156156
# enable DBMS_OUTPUT
157-
158157
cursor.callproc("dbms_output.enable")
159158
160159
# execute some PL/SQL that calls DBMS_OUTPUT.PUT_LINE
161-
162160
cursor.execute("""
163161
begin
164162
dbms_output.put_line('This is the cx_Oracle manual');
165-
dbms_output.put_line('Demonstrating use of DBMS_OUTPUT');
163+
dbms_output.put_line('Demonstrating how to use DBMS_OUTPUT');
166164
end;""")
167165
168-
# fetch the text that was added by PL/SQL
169-
170-
chunkSize = 10 # Tune this size for your application
166+
# tune this size for your application
167+
chunk_size = 100
171168
172-
charArr = connection.gettype("SYS.DBMS_OUTPUT.CHARARR")
173-
lines = charArr.newobject()
169+
# create variables to hold the output
170+
lines_var = cursor.arrayvar(str, chunk_size)
171+
num_lines_var = cursor.var(int)
172+
num_lines_var.setvalue(0, chunk_size)
174173
175-
numLines = cursor.var(int)
176-
numLines.setvalue(0, chunkSize)
177-
178-
while numLines.getvalue() == chunkSize:
179-
cursor.callproc("dbms_output.get_lines", (lines, numLines))
180-
for line in lines.aslist():
181-
print(line)
174+
# fetch the text that was added by PL/SQL
175+
while True:
176+
cursor.callproc("dbms_output.get_lines", (lines_var, num_lines_var))
177+
num_lines = num_lines_var.getvalue()
178+
lines = lines_var.getvalue()[:num_lines]
179+
for line in lines:
180+
print(line or "")
181+
if num_lines < chunk_size:
182+
break
182183
183184
This will produce the following output::
184185

‎doc/src/user_guide/sql_execution.rst

Copy file name to clipboardExpand all lines: doc/src/user_guide/sql_execution.rst
+103-6Lines changed: 103 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -24,13 +24,13 @@ in ``RunSqlScript()`` in `samples/SampleEnv.py
2424
SQL statements should not contain a trailing semicolon (";") or forward slash
2525
("/"). This will fail:
2626

27-
.. code-block:: sql
27+
.. code-block:: python
2828
2929
cur.execute("select * from MyTable;")
3030
3131
This is correct:
3232

33-
.. code-block:: sql
33+
.. code-block:: python
3434
3535
cur.execute("select * from MyTable")
3636
@@ -106,6 +106,9 @@ method :meth:`Cursor.fetchall()` can be used.
106106
for row in rows:
107107
print(row)
108108
109+
The fetch methods return data as tuples. To return results as dictionaries, see
110+
:ref:`rowfactories`.
111+
109112
Closing Cursors
110113
---------------
111114

@@ -149,9 +152,14 @@ minimum or maximum number of rows returned by a query.
149152

150153
Along with tuning ``arraysize``, make sure your `SQL statements are optimal
151154
<https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=TGSQL>`_ and avoid
152-
selecting columns that are not required by the application. For queries that do
153-
not need to fetch all data, use a :ref:`row limiting clause <rowlimit>` to
154-
reduce the number of rows processed by the database.
155+
selecting columns that are not required by the application. For queries that do
156+
not need to fetch all data, use appropriate ``WHERE`` clauses such as a
157+
:ref:`row limiting clause <rowlimit>` to reduce the number of rows processed by
158+
the database. For small, mostly static, lookup tables enable :ref:`Client Result
159+
Caching <crc>` to avoid round-trips between cx_Oracle and the database. For
160+
queries that return large data or a large number of rows, or when using a slow
161+
network, tune the network `Session Data Unit (SDU) and socket buffer sizes
162+
<https://static.rainfocus.com/oracle/oow19/sess/1553616880266001WLIh/PF/OOW19_Net_CON4641_1569022126580001esUl.pdf>`__.
155163

156164
An example of setting ``arraysize`` is:
157165

@@ -360,7 +368,8 @@ or the value ``None``. The value ``None`` indicates that the default type
360368
should be used.
361369

362370
Examples of output handlers are shown in :ref:`numberprecision` and
363-
:ref:`directlobs`.
371+
:ref:`directlobs`. Also see samples such as `samples/TypeHandlers.py
372+
<https://github.com/oracle/python-cx_Oracle/blob/master/samples/TypeHandlers.py>`__
364373

365374
.. _numberprecision:
366375

@@ -411,6 +420,10 @@ The Python ``decimal.Decimal`` converter gets called with the string
411420
representation of the Oracle number. The output from ``decimal.Decimal`` is
412421
returned in the output tuple.
413422

423+
See `samples/ReturnNumbersAsDecimals.py
424+
<https://github.com/oracle/python-cx_Oracle/blob/master/samples/ReturnNumbersAsDecimals.py>`__
425+
426+
414427
.. _outconverters:
415428

416429
Changing Query Results with Outconverters
@@ -434,6 +447,44 @@ For example, to make queries return empty strings instead of NULLs:
434447
435448
connection.outputtypehandler = OutputTypeHandler
436449
450+
451+
.. _rowfactories:
452+
453+
Changing Query Results with Rowfactories
454+
----------------------------------------
455+
456+
cx_Oracle "rowfactories" are methods called for each row that is retrieved from
457+
the database. The :meth:`Cursor.rowfactory` method is called with the tuple that
458+
would normally be returned from the database. The method can convert the tuple
459+
to a different value and return it to the application in place of the tuple.
460+
461+
For example, to fetch each row of a query as a dictionary:
462+
463+
.. code-block:: python
464+
465+
cursor.execute("select * from locations where location_id = 1000")
466+
columns = [col[0] for col in cursor.description]
467+
cursor.rowfactory = lambda *args: dict(zip(columns, args))
468+
data = cursor.fetchone()
469+
print(data)
470+
471+
The output is::
472+
473+
{'LOCATION_ID': 1000, 'STREET_ADDRESS': '1297 Via Cola di Rie', 'POSTAL_CODE': '00989', 'CITY': 'Roma', 'STATE_PROVINCE': None, 'COUNTRY_ID': 'IT'}
474+
475+
If you join tables where the same column name occurs in both tables with
476+
different meanings or values, then use a column alias in the query. Otherwise
477+
only one of the similarly named columns will be included in the dictionary:
478+
479+
.. code-block:: sql
480+
481+
select
482+
cat_name,
483+
cats.color as cat_color,
484+
dog_name,
485+
dogs.color
486+
from cats, dogs
487+
437488
.. _scrollablecursors:
438489

439490
Scrollable Cursors
@@ -629,6 +680,52 @@ query is::
629680
Make sure to use :ref:`bind variables <bind>` for the upper and lower limit
630681
values.
631682

683+
.. _crc:
684+
685+
Client Result Cache
686+
-------------------
687+
688+
Python cx_Oracle applications can use Oracle Database's `Client Result Cache
689+
<https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-35CB2592-7588-4C2D-9075-6F639F25425E>`__
690+
The CRC enables client-side caching of SQL query (SELECT statement) results in
691+
client memory for immediate use when the same query is re-executed. This is
692+
useful for reducing the cost of queries for small, mostly static, lookup tables,
693+
such as for postal codes. CRC reduces network round-trips, and also reduces
694+
database server CPU usage.
695+
696+
The cache is at the application process level. Access and invalidation is
697+
managed by the Oracle Client libraries. This removes the need for extra
698+
application logic, or external utilities, to implement a cache.
699+
700+
CRC can be enabled by setting the `database parameters
701+
<https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-A9D4A5F5-B939-48FF-80AE-0228E7314C7D>`__
702+
``CLIENT_RESULT_CACHE_SIZE`` and ``CLIENT_RESULT_CACHE_LAG``, and then
703+
restarting the database. For example, to set the parameters:
704+
705+
.. code-block:: sql
706+
707+
SQL> ALTER SYSTEM SET CLIENT_RESULT_CACHE_LAG = 3000 SCOPE=SPFILE;
708+
SQL> ALTER SYSTEM SET CLIENT_RESULT_CACHE_SIZE = 64K SCOPE=SPFILE;
709+
710+
CRC can alternatively be configured in an :ref:`oraaccess.xml <optclientfiles>`
711+
or :ref:`sqlnet.ora <optnetfiles>` file on the Python host, see `Client
712+
Configuration Parameters
713+
<https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-E63D75A1-FCAA-4A54-A3D2-B068442CE766>`__.
714+
715+
Tables can then be created, or altered, so repeated queries use CRC. This
716+
allows existing applications to use CRC without needing modification. For example:
717+
718+
.. code-block:: sql
719+
720+
SQL> CREATE TABLE cities (id number, name varchar2(40)) RESULT_CACHE (MODE FORCE);
721+
SQL> ALTER TABLE locations RESULT_CACHE (MODE FORCE);
722+
723+
Alternatively, hints can be used in SQL statements. For example:
724+
725+
.. code-block:: sql
726+
727+
SELECT /*+ result_cache */ postal_code FROM locations
728+
632729
.. _codecerror:
633730

634731
Querying Corrupt Data

‎samples/Threads.py renamed to ‎samples/ConnectionPool.py

Copy file name to clipboardExpand all lines: samples/ConnectionPool.py
+15-7Lines changed: 15 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -3,20 +3,28 @@
33
#------------------------------------------------------------------------------
44

55
#------------------------------------------------------------------------------
6-
# Threads.py
7-
# This script demonstrates the use of threads with cx_Oracle. A session pool
8-
# is used so that multiple connections are available to perform work on the
9-
# database. Only one operation (such as an execute or fetch) can take place at
10-
# a time on a connection. In the below example, one of the threads performs
11-
# dbms_lock.sleep while the other performs a query.
6+
# ConnectionPool.py
7+
# This script demonstrates the use of connection pooling in cx_Oracle. Pools
8+
# can significantly reduce connection times for long running applications that
9+
# repeatedly open and close connections. Internal features help protect against
10+
# dead connections, and also aid use of Oracle Database features such as FAN
11+
# and Application Continuity.
12+
# The script uses threading to show multiple users of the pool. One thread
13+
# performs a database sleep while another performs a query. A more typical
14+
# application might be a web service that handles requests from multiple users.
15+
# Applications that use connections concurrently in multiple threads should set
16+
# the 'threaded' parameter to True. Note only one operation (such as an execute
17+
# or fetch) can take place at a time on each connection.
18+
#
19+
# Also see SessionCallback.py.
1220
#
13-
# This script requires cx_Oracle 2.5 and higher.
1421
#------------------------------------------------------------------------------
1522

1623
import cx_Oracle
1724
import SampleEnv
1825
import threading
1926

27+
# Create a Connection Pool
2028
pool = cx_Oracle.SessionPool(SampleEnv.GetMainUser(),
2129
SampleEnv.GetMainPassword(), SampleEnv.GetConnectString(), min=2,
2230
max=5, increment=1, threaded=True)

‎samples/DRCP.py

Copy file name to clipboardExpand all lines: samples/DRCP.py
+6-2Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
#------------------------------------------------------------------------------
2-
# Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
2+
# Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.
33
#
44
# Portions Copyright 2007-2015, Anthony Tuininga. All rights reserved.
55
#
@@ -25,7 +25,11 @@
2525
# There is no difference in how a connection is used once it has been
2626
# established.
2727
#
28-
# This script requires cx_Oracle 5.0 and higher.
28+
# DRCP has most benefit when used in conjunction with cx_Oracle's local
29+
# connection pool, see the cx_Oracle documentation.
30+
#
31+
# This script requires cx_Oracle 5.0 or higher.
32+
#
2933
#------------------------------------------------------------------------------
3034

3135
import cx_Oracle

‎samples/DbmsOutput.py

Copy file name to clipboard
+44Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
#------------------------------------------------------------------------------
2+
# Copyright (c) 2020, Oracle and/or its affiliates. All rights reserved.
3+
#------------------------------------------------------------------------------
4+
5+
#------------------------------------------------------------------------------
6+
# DbmsOutput.py
7+
# This script demonstrates one method of fetching the lines produced by
8+
# the DBMS_OUTPUT package.
9+
#------------------------------------------------------------------------------
10+
11+
import cx_Oracle
12+
import SampleEnv
13+
14+
connection = cx_Oracle.connect(SampleEnv.GetMainConnectString())
15+
cursor = connection.cursor()
16+
17+
# enable DBMS_OUTPUT
18+
cursor.callproc("dbms_output.enable")
19+
20+
# execute some PL/SQL that generates output with DBMS_OUTPUT.PUT_LINE
21+
cursor.execute("""
22+
begin
23+
dbms_output.put_line('This is the cx_Oracle manual');
24+
dbms_output.put_line('');
25+
dbms_output.put_line('Demonstrating use of DBMS_OUTPUT');
26+
end;""")
27+
28+
# tune this size for your application
29+
chunk_size = 10
30+
31+
# create variables to hold the output
32+
lines_var = cursor.arrayvar(str, chunk_size)
33+
num_lines_var = cursor.var(int)
34+
num_lines_var.setvalue(0, chunk_size)
35+
36+
# fetch the text that was added by PL/SQL
37+
while True:
38+
cursor.callproc("dbms_output.get_lines", (lines_var, num_lines_var))
39+
num_lines = num_lines_var.getvalue()
40+
lines = lines_var.getvalue()[:num_lines]
41+
for line in lines:
42+
print(line or "")
43+
if num_lines < chunk_size:
44+
break

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.