Using python-oracledb 1.0 with SQLAlchemy, Pandas, Django and Flask

4 min readMay 27, 2022

A new, major release of the Python cx_Oracle driver for Oracle Database is available — and comes with a brand new name: python-oracledb.

See the release announcement for details.

What does this name change mean for your favorite frameworks, ORMs, SQL generators, and libraries? The driver continues to conform to the Python Database API v2.0 Specification (with many additions and just a couple of exclusions), so any framework etc. that expects a standard driver should have no problem. Until each framework etc. adds support for the new name, you can add a few lines of code to your app for name mapping. Note that some functionality, such as dead-connection error detection, may not work until native support lands, in this case because some driver errors have necessarily changed.

Here are the tweaks you can use for testing some popular environments.

SQLAlchemy 1.4

Update: native support for python-oracledb has been added to the SQLAlchemy 2.0 release, see the commit and the usage notes and this blog post. If you are not using this version of SQLAlchemy, follow the instructions below.

To try out python-oracledb 1.0 today, install it with pip:

python -m pip install oracledb

Then add this to your top level SQLAlchemy 1.4 file:

import sys
import oracledb
oracledb.version = "8.3.0"
sys.modules["cx_Oracle"] = oracledb

This snippet needs to occur before SQLAlchemy 1.4 does its “import cx_Oracle”. It enables the default ‘Thin’ mode of python-oracledb. That’s the brand new mode that doesn’t need any Oracle Client libraries. We have been running the SQLAlchemy test suite like this. And we owe a big shout out to SQLAlchemy’s testing breadth which helped us in the early development of the Thin mode.

If you want to use the python-oracledb ‘Thick’ mode (which offers a few more features like Advanced Queuing, see the feature list), then you can add a call to init_oracle_client(). I do that with this code added immediately after the code shown above:

import os
import platform
if platform.system() == "Darwin":     
     oracledb.init_oracle_client(lib_dir=os.environ.get("HOME")+"/Downloads/instantclient_19_8")
elif platform.system() == "Windows":
    oracledb.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_14")
else:
    oracledb.init_oracle_client()

On Linux, you need to call init_oracle_client() but without passing a lib_dir argument: you must run ldconfig or set LD_LIBRARY_PATH before starting Python on Linux.

The python-oracledb Thick mode has the same architecture as cx_Oracle and uses the same Oracle Client Library stack.

Connection in SQLAlchemy is the same in python-oracledb as before, for example:

engine = create_engine(
    f'oracle://{un}:{pw}@{host}:{port}/?service_name={service_name}', max_identifier_length=128
)

You can optionally make use of some new connection parameters available in python-oracledb by using SQLAlchemy’s connect_args option. Here is a complete app that sets python-oracledb's new individual host, port and service_name connection parameters:

import os
import oracledb
import sys
oracledb.version = "8.3.0"
sys.modules["cx_Oracle"] = oracledbfrom sqlalchemy import create_engine
from sqlalchemy import textun = os.environ.get("PYTHON_USERNAME")
pw = os.environ.get("PYTHON_PASSWORD")engine = create_engine(f'oracle://{un}:{pw}@',
                       connect_args={
                           "host": "localhost",
                           "port": 1521,
                           "service_name": "orclpdb"
                       }
         )with engine.connect() as conn:
    print(conn.scalar(text(
           """SELECT UNIQUE CLIENT_DRIVER
              FROM V$SESSION_CONNECT_INFO
              WHERE SID = SYS_CONTEXT('USERENV', 'SID')""")))

This shows that the Thin mode of the driver is in use:

python-oracledb thn : 1.0.0

To fit in the CLIENT_DRIVER column width, the abbreviations “thn” and “thk” are used to denote the two driver modes.

Check the python-oracledb documentation about other connect() parameters that can be passed.

Update: the dead-connection detection support mentioned in the introduction will land in SQLAlchemy 1.4.37, but you will still have use the name-mapping snippet until full support for python-oracledb is merged.

Update: if you’re also looking at my post on Using SQLAlchemy 2.0 (development) with python-oracledb for Oracle Database note that with 1.4 you connect with oracle:// but with 2.0 you connect with oracle+oracledb://.

Pandas

Since Pandas uses SQLAlchemy, the steps above can be used for Pandas 1.5. Add the snippet to the top of the first file run.

Update: Pandas 2 uses SQLAlchemy 2 so see Using SQLAlchemy 2.0 with python-oracledb for Oracle Database.

Django

Update: Django 5 has native support for python-oracledb. See Django 5.0 supports python-oracledb natively.

With Django 4, the same, initial snippet of code used for SQLAlchemy 1.4 can also be used. It needs to be added to the top of settings.py:

import sys
import oracledb
oracledb.version = "8.3.0"
sys.modules["cx_Oracle"] = oracledb

You can call init_oracle_client() too, if you have Oracle Client libraries and want to use python-oracledb 'Thick' mode.

To connect to Oracle Database, your settings.py file connection settings will be as normal, for example:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': 'example.com:1521/orclpdb',
        'USER': 'scott',
        'PASSWORD': 'XXXX'
    }
}

Many options can be added the the Easy Connect String, see the Easy Connect Plus syntax.

For python-oracledb in Thin mode, you can optionally use new python-oracledb connection parameters by using an OPTIONS section. For example, to specify a time-bound for connection, you can pass the new python-oracledb tcp_connect_timeout parameter as shown below. In this example the host, port and service_name values are also kept separate as an example. Note the service name is specified in the OPTIONS block:

DATABASES = {
    'default': {
            'ENGINE': 'django.db.backends.oracle',
            'USER': 'scott',
            'PASSWORD': 'xxx',
            'HOST': 'localhost',
            'PORT': 1521,
            'OPTIONS': {
                'service_name': 'orclpdb',
                'tcp_connect_timeout': 10,
            },
    }
}

Flask

Flask is a ‘micro framework’ that doesn’t enforce dependencies, so you can immediately start writing new apps using python-oracledb. Check out the connection_pool.py sample which shows how to use Flask with a connection pool. If you want to upgrade an app from cx_Oracle to python-oracledb, then check out the upgrading documentation.

SUMMARY

In summary, you can test python-oracledb in your applications right now by adding a little bit of scaffolding code. When the framework / ORM / SQL Generator / library communities have had time to add the new ‘oracledb’ namespace, this scaffolding won’t be needed.

RESOURCES

Home page: oracle.github.io/python-oracledb

Installation instructions: python-oracledb.readthedocs.io/en/latest/installation.html

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

Questions: github.com/oracle/python-oracledb/discussions

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