Python cx_Oracle 7 Introduces SODA Document Storage

DZone 's Guide to

Python cx_Oracle 7 Introduces SODA Document Storage

Let's take a look at the release of SODA Document Storage and learn about it in Python cx_Oracle.

· Database Zone ·
Free Resource

cx_Oracle 7.0, the extremely popular Oracle Database interface for Python, is now Production on PyPI.

cx_Oracle is an open source package that covers the Python Database API specification with many additions to support Oracle advanced features.

Anthony Tuininga has just released cx_Oracle 7.0. This release brings some key technologies and new features to the Python developer:

  • Oracle Simple Document Access (SODA) support - an exciting addition to the standard relational access model is the new set of APIs for Oracle SODA. See below.
  • Added Connection.callTimeout to support call timeouts when cx_Oracle is using Oracle Client 18.1 and higher. This is a useful backstop to prevent out-of-control SQL and PL/SQL statement execution.The main code layer beneath cx_Oracle's implementation is Oracle Call Interface. This API handles all the network connectivity to Oracle Database. For each OCI function executed by cx_Oracle, zero or more 'round-trips' to the database can occur - calling the database and getting a response back.The callTimeout value applies to each round-trip individually, not to the sum of all round-trips. Time spent processing in cx_Oracle before or after the completion of each round-trip is not counted.
    • If the time from the start of any one round-trip to the completion of that same round-trip exceeds callTimeout milliseconds, then the operation is halted and an error is returned.
    • In the case where a cx_Oracle operation requires more than one round-trip and each round-trip takes less than callTimeout milliseconds, then no timeout will occur, even if the sum of all round-trip calls exceeds callTimeout.
    • If no round-trip is required, the operation will never be interrupted.
    After a timeout is triggered, cx_Oracle attempts to clean up the internal connection state. The cleanup is allowed to take another callTimeout milliseconds. If the cleanup was successful, a DPI-1067 error will be returned and the application can continue to use the connection. For small values of callTimeout, the connection cleanup may not complete successfully within the additional callTimeout period. In this case, an ORA-3114 is returned and the connection will no longer be usable. It should be closed.
  • Added support for closing a session pool via the function SessionPool.close(). This is useful for being 'nice' to the database and making sure that database sessions are not left dangling until the database cleans them up. In particular, the optional 'force' argument is handy when you need to suddenly halt a Python application and immediately free all the sessions in the database.
  • Added support for getting the contents of a SQL collection object as a dictionary, where the keys are the indices of the collection and the values are the elements of the collection. See function Object.asdict().
  • On Windows, cx_Oracle will now attempt to load the Oracle client libraries from the same directory as the cx_Oracle module before doing the standard Windows library location search, e.g. in the directories in the PATH environment variable. This new feature could be useful if you are bundling up applications and want to include the Oracle Instant Client. By putting the client in the same directory as the cx_Oracle library there is no need to set PATH, no need to worry about users changing PATH, and no need to worry about having multiple versions of Oracle client libraries in PATH.
  • A change in cx_Oracle 7 is that when a DML RETURNING statement is executed, variables bound to it will return an array when calling Variable.getvalue(). Attempts to set cx_Oracle.__future__.dml_ret_array_val are now ignored.
  • When a connection is used as a context manager, the connection is now closed when the block ends. Attempts to set cx_Oracle.__future__.ctx_mgr_close are now ignored.

The full release notes show the other new features and changes. Review this list before you upgrade:

python -m pip install cx_Oracle --upgrade

SODA in Python cx_Oracle

Oracle Simple Document Access (SODA) support was originally introduced in Java and recently exposed to C. Python support for SODA is now available in cx_Oracle 7 when using Oracle client 18.3 libraries and connecting to Oracle Database 18.1 or higher. SODA is all hot and new and under rapid development. For this cx_Oracle release, we're labeling SODA support as a "preview." With a future version of the Oracle Client libraries, this will change.

SODA is typically used to store JSON documents in Oracle Database but has the flexibility to let you store other types of content.

Once a DBA has granted you the SODA_APP privilege, you can simply create collections and store documents in them. Some basic examples are:


# Create the parent object for SODA
soda = connection.getSodaDatabase()

# Create a new SODA collection
# This will open an existing collection, if the name is already in use.
collection = soda.createCollection("mycollection")

# Insert a document
# A system generated key is created by default.
content = {'name': 'Matilda', 'address': {'city': 'Melbourne'}}
doc = collection.insertOneAndGet(content)
key = doc.key
print('The key of the new SODA document is: ', key)

You can then get documents back via a key lookup or by a search. A key lookup is straightforward:

# Fetch the document back
doc = collection.find().key(key).getOne() # A SodaDocument
content = doc.getContent()                # A JavaScript object
print('Retrieved SODA document dictionary is:')

For documents that can be converted to JSON, you can alternatively get them as string:

content = doc.getContentAsString()        # A JSON string
print('Retrieved SODA document string is:')

The find() method is an operation builder, with methods that allow progressive filtering criteria to be set, limiting the set of documents that are then operated on by a terminal method such as getOne(), getDocuments() and count().

With JSON documents, a complete filtering specification language can be used to pattern match documents. A brief example is:

# Find all documents with names like 'Ma%'
print("Names matching 'Ma%'")
documents = collection.find().filter({'name': {'$like': 'Ma%'}}).getDocuments()
for d in documents:
    content = d.getContent()

A runnable example is in SodaBasic.py

Check out the cx_Oracle SODA manual and the Introduction to Simple Oracle Document Access (SODA) manual to see its power and simplicity.

Check it out!

cx_Oracle References

Homepage: oracle.github.io/python-cx_Oracle/index.html

Installation instructions: cx-oracle.readthedocs.io/en/latest/installation.html

Documentation: cx-oracle.readthedocs.io/en/latest/index.html

Release Notes: cx-oracle.readthedocs.io/en/latest/releasenotes.html

Source Code Repository: github.com/oracle/python-cx_Oracle

cx_oracle 7 ,database ,oracle call interfac ,oracle database ,oracle soda ,python ,soda document storage

Published at DZone with permission of Christopher Jones , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}