Python Driver For Oracle Database Goes Asynchronous

v2.0 of the driver has been upgraded to support asynchronous operations from Python code. This is a major boon for Python developers.

The previous instance pf the driver was a milestone in that it had a major name change from cx_Oracle to python-oracledb to signal that the new driver is a thin driver and therefore makes no use of the Oracle Client libraries. As such it was aligned with the rest of the language*-oracledb naming schemes as node-oracledb does.

The new driver conformed to the Python Database API v2. 0 Specification and easily installed through pip install oracledb. Alternatively it can also work in Thick mode too when it links with the Oracle Client libraries.

This new version, 2. 0 comes with another major addition;that of asyncio, now enabling developers to do asynchronous programming with it, that is the Asynchronous I/O (asyncio) Python library can be used with python-oracledb Thin mode for concurrent programming.

The asyncio library allows you to run operations in parallel, for example to run a long-running operation in the background without blocking the rest of the application. With asyncio, you can easily write concurrent code with the async and await syntax.

Any database call which needs a round-trip to the database gets now an async counterpart. This means your code can choose whether to use the existing synchronous API or alternatively use the new async API.

Of course, that the database driver is async aware is very important because it enables true asynchrony. It is a matter I examined for Java in Hibernate goes Reactive – What Does That Mean? , but the principle applies everywhere:

First a definition of the problem. JPA/JDBC was always blocking, meaning that any operation that touches the database through JDBC operations will block on socket IO, and when that’s happening the thread it is running on will be blocked.

So that means that when I make a call to the database from my main thread, the thread will block until the driver returns the results of the query. Then what about wrapping that call in a CompletableFuture to make the call asynchronous and wait on the Future instead, calling my callback when the database operation completes? That way don’t I need a non-blocking driver after all?Perhaps, but since the stack is not non-blocking all the way down, there are certain disadvantages.

But why should the stack be non-blocking all the way down?

In that case we just had wrapped a synchronous call in an asynchronous wrapper, just faking asynchrony. The thread that makes the actual jdbc call will block until the query returns and won’t be able to go back to the thread pool and serve another request.

On the other hand, the R2DBC spec provides a rough outline to communicate with a relational DB in a way that doesn’t block disk I/O. The spec itself is really just a guideline, and the actual implementation is left up to you but it can also be used in combination with CompletableFutures.

The gist being the DB wire protocol implementation can be used in a more efficient way, eliminating threads, thereby decreasing memory usage and possibly Increasing throughout. Of course, it all really depends on the use case.

Java’s CompletableFuture closest equivalent in Python is coroutines with the asyncio module. The non blockingI/O principles remain as is for both.

But what does the async property of the new driver mean practically?

Your application communicates with Oracle Database by executing SQL statements. Statements such as queries (statements beginning with SELECT or WITH), Data Manipulation Language (DML), and Data Definition Language (DDL) are executed using the asynchronous methods AsyncCursor. execute() or AsyncCursor. executemany(). Rows can be iterated over, or fetched using one of the methods AsyncCursor. fetchone(), AsyncCursor. fetchone(), AsyncCursor. fetchmany(), or AsyncCursor. fetchall().

You can also use shortcut methods on the API: AsyncConnection Objects object such as AsyncConnection. execute() or AsyncConnection. executemany(). Rows can be fetched using one of the shortcut methods AsyncConnection. fetchone(), AsyncConnection. fetchmany(), or AsyncConnection. fetchall().

An example of using AsyncConnection. fetchall():

So this is a pretty important milestone in the driver’s lifecycle and a big boon programmaticaly for Python developers. But the driver comes with other changes and additions too, with the most noteworthy being:

Added parameter ConnectParams. sdu for configuring the Session Data Unit (SDU) size for sizing internal buffers used for tuning communication with the database. The connection property Connection. sdu was also added.

Added parameter ConnectParams. ssl_context to modify the SSL context used when connecting via TLS,

Added support for an Oracle Database 23c JSON feature allowing field names with more than 255 UTF-8 encoded bytes.

Dropped support for Python 3. 6.

Added properties that provide information about the database: Connection. db_domain, Connection. db_name, Connection. max_open_cursors, Connection. service_name and Connection. transaction_in_progress.

Added property Connection. proxy_user to show the name of the user which was used as a proxy when connecting.

Added parameter data to Connection. createlob() to allow data to be written at LOB creation time.

Added type DB_TYPE_XMLTYPE to represent data of type SYS. XMLTYPE in the database. Previously the value of FetchInfo. type_code for data of this type was DB_TYPE_LONG in Thick mode and DB_TYPE_OBJECT in Thin mode.

Of course, the usual bug fixes and internal changesWith Oracle offering its latest advanced Free Database version 23 and at the same time providing asynchronous interfaces to it, its tools are a bit more accessible by developers.

More Information

Python-oracledb 2.0 has asyncio support

Full release notes

New Database Drivers for Oracle and PostgreSQL Released

New NodeJS Database Driver for Oracle Released

Hibernate goes Reactive – What Does That Mean?

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.