cx_Oracle is an open source package for the Python Database API specification with many additions to support advanced Oracle Database features.
It's time for a tidy batch of enhancements and fixes to cx_Oracle. The top changes:
Improved Oracle Advanced Queuing support:
Added support for enqueue and dequeue of "RAW" payloads (adding to the previous support for object AQ messages). This lets you used strings and buffers for messages.
Added bulk enqueue and dequeue of messages. This can significantly improve performance when dealing with multiple messages.
To align the new and existing AQ support, a new method Connection.queue() was introduced, This returns a queue, which has methods and properties for AQ functionality. The previous AQ methods Connection.deq(), Connection.deqOptions(), Connection.enq(), and Connection.enqOptions() are deprecated and will be removed in cx_Oracle 8.
Added support for SODA bulk insert, available when cx_Oracle uses Oracle client libraries 18.5 and higher. This is currently in 'preview' status.
Added support for setting the LOB prefetch length indicator in order to reduce the number of round trips when processing LOB values.
Thanks are deserved, as always, to everyone who has contributed directly and indirectly.
You can install or upgrade cx_Oracle by running:
python -m pip install cx_Oracle --upgrade
See cx_Oracle 7 Installation for details. If the upgrade seems to have no effect, add the pip install option --force-reinstall.
Linux packages will be available soon from Oracle Linux for Python Developers.
Here's an example of the new interface to AQ. I use a RAW queue to send simple strings.
First we need a RAW queue itself, which I'll call DEMO_RAW_QUEUE. This has to be configured in the database, for example in SQL*Plus:
dbms_aqadm.create_queue_table('MY_QUEUE_TABLE', 'RAW'); dbms_aqadm.create_queue('DEMO_RAW_QUEUE', 'MY_QUEUE_TABLE'); dbms_aqadm.start_queue('DEMO_RAW_QUEUE');
To send messages in Python you connect and get a 'queue' object. The queue can be used for enqueuing, dequeuing, or both. Refer to the cx_Oracle and Oracle Advanced Queuing documentation for details on enqueue and dequeue options. Here I use ENQ_IMMEDIATE so that a commit on the connection is not needed to commit messages:
import cx_Oracle connection = cx_Oracle.connect(. . .) queue = connection.queue("DEMO_RAW_QUEUE") queue.enqOptions.visibility = cx_Oracle.ENQ_IMMEDIATE
Now messages can be queued. Here I'm sending three messages, setting an expiration of 60 seconds on them. This means that if no dequeue operation occurs in 60 seconds then the messages will be dropped from the queue:
PAYLOAD_DATA = [ "The first message", "The second message", "The third message" ] for data in PAYLOAD_DATA: queue.enqOne(connection.msgproperties(payload=data, expiration=60))
Dequeuing is similar. Strings are encoded as buffers, so a decode() is used to get the text:
queue = connection.queue("DEMO_RAW_QUEUE") queue.deqOptions.wait = cx_Oracle.DEQ_NO_WAIT queue.deqOptions.navigation = cx_Oracle.DEQ_FIRST_MSG while True: props = queue.deqOne() if not props: break print(props.payload.decode()) connection.commit()
There are examples in the samples directory.
Readers with a good memory will recall I mentioned bulk AQ operations but the example above used a loop for each string. You can convert the examples easily. The bulk enqueue operation simply takes an array of messages. The bulk dequeue method returns an array of messages; the array length is up to a specified size.
Home page: oracle.github.io/python-cx_Oracle/index.html
Installation instructions: cx-oracle.readthedocs.io/en/latest/installation.html
Release Notes: cx-oracle.readthedocs.io/en/latest/releasenotes.html
Source Code Repository: github.com/oracle/python-cx_Oracle
Facebook group: www.facebook.com/groups/oraclescripting