31. Appendix D: Python Frameworks, SQL Generators, and ORMs

Python-oracledb’s Thin and Thick modes cover the feature needs of frameworks that depend upon the Python Database API.

31.1. Using python-oracledb with Data Frame Libraries

Python-oracledb can fetch directly to data frames that expose an Apache Arrow PyCapsule interface. This is an efficient way to work with data using Python libraries such as Apache PyArrow, Pandas, Polars, NumPy, PyTorch, or to write files in Apache Parquet format.

See Working with Data Frames for more information.

31.2. Connecting with Flask

The Flask web application framework works well with python-oracledb, either directly or by using a library such as SQLAlchemy.

Examples using python-oracledb directly are available in connection_pool.py, drcp_pool.py, and session_callback.py.

31.3. Connecting with SQLAlchemy

SQLAlchemy, and libraries such as Pandas that internally use SQLAlchemy, can connect easily in python-oracledb as shown in this section.

Also, see SQLAlchemy documentation on connecting and SQLAlchemy general documentation about Oracle Database.

31.3.1. Connecting with SQLAlchemy 2

SQLAlchemy 2 supports python-oracledb directly.

31.3.1.1. Standalone Connections in SQLAlchemy

An example of creating a standalone connection in SQLAlchemy 2 is:

# Using python-oracledb in SQLAlchemy 2

import os
import getpass
import oracledb
from sqlalchemy import create_engine
from sqlalchemy import text

# Uncomment to use python-oracledb Thick mode
# Review the doc for the appropriate parameters
#oracledb.init_oracle_client(<your parameters>)

un = os.environ.get("PYTHON_USERNAME")
cs = os.environ.get("PYTHON_CONNECTSTRING")
pw = getpass.getpass(f'Enter password for {un}@{cs}: ')

# Note the first argument is different for SQLAlchemy 1.4 and 2
engine = create_engine('oracle+oracledb://@',
                       connect_args={
                           # Pass any python-oracledb connect() parameters
                           "user": un,
                           "password": pw,
                           "dsn": cs
                       }
         )

with engine.connect() as connection:
    print(connection.scalar(text(
           """select unique client_driver
              from v$session_connect_info
              where sid = sys_context('userenv', 'sid')""")))

Note that the create_engine() argument driver declaration uses oracle+oracledb:// for SQLAlchemy 2 but oracle:// for SQLAlchemy 1.4.

The connect_args dictionary can use any appropriate oracledb.connect() parameter.

31.3.1.2. Pooled Connections in SQLAlchemy

Most multi-user applications should use a connection pool. The python-oracledb pool is preferred because of its high availability support. Some single-user applications may also benfit from these availability features.

To use a python-oracledb connection pool in SQLAlchemy:

# Using python-oracledb in SQLAlchemy 2

import os, platform
import getpass
import oracledb
from sqlalchemy import create_engine
from sqlalchemy import text
from sqlalchemy.pool import NullPool

# Uncomment to use python-oracledb Thick mode
# Review the doc for the appropriate parameters
#oracledb.init_oracle_client(<your parameters>)

un = os.environ.get("PYTHON_USERNAME")
cs = os.environ.get("PYTHON_CONNECTSTRING")
pw = getpass.getpass(f'Enter password for {un}@{cs}: ')

pool = oracledb.create_pool(user=un, password=pw, dsn=cs,
                            min=4, max=4, increment=0)
engine = create_engine("oracle+oracledb://", creator=pool.acquire, poolclass=NullPool)

with engine.connect() as connection:
    print(connection.scalar(text("""select unique client_driver
                                    from v$session_connect_info
                                    where sid = sys_context('userenv', 'sid')""")))

31.3.2. Connecting with SQLAlchemy 1.4

SQLAlchemy 1.4 can use python-oracledb with the help of the module name mapping code shown in Older Versions of Python Frameworks, SQL Generators, and ORMs. An example is:

# Using python-oracledb in SQLAlchemy 1.4

import os
import getpass
import oracledb
from sqlalchemy import create_engine
from sqlalchemy import text

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

# Uncomment to use python-oracledb Thick mode
# Review the doc for the appropriate parameters
#oracledb.init_oracle_client(<your parameters>)

un = os.environ.get("PYTHON_USERNAME")
cs = os.environ.get("PYTHON_CONNECTSTRING")
pw = getpass.getpass(f'Enter password for {un}@{cs}: ')

# Note the first argument is different for SQLAlchemy 1.4 and 2
engine = create_engine('oracle://@',
                       connect_args={
                           # Pass any python-oracledb connect() parameters
                           "user": un,
                           "password": pw,
                           "dsn": cs
                       }
         )

with engine.connect() as connection:
    print(connection.scalar(text(
           """select unique client_driver
              from v$session_connect_info
              where sid = sys_context('userenv', 'sid')""")))

Note that the create_engine() argument driver declaration uses oracle:// for SQLAlchemy 1.4 but oracle+oracledb:// for SQLAlchemy 2.

The connect_args dictionary can use any appropriate oracledb.connect() parameter.

You can also use python-oracledb connection pooling with SQLAlchemy 1.4. This is similar to pooled connections in SQLAlchemy 2 but use the appropriate name mapping code and first argument to create_engine().

31.4. Connecting with Django

Django 5 supports python-oracledb directly. Earlier versions should use name mapping code.

See Django 5.2 documentation for Oracle Database.

31.4.1. Standalone Connections

To connect in Django 5, an example settings.py file is:

DATABASES = {
  "default": {
    "ENGINE": "django.db.backends.oracle",
    "NAME": "example.com:1521/orclpdb",
    "USER": "hr",
    "PASSWORD": "the-hr-password"
  }
}

31.4.2. Pooled Connections

Django 5.2 supports python-oracledb connection pools. Most multi-user applications should use a connection pool. The python-oracledb pool is preferred because of its high availability support. Some single-user applications may also benfit from these availability features.

To use a connection pool in Django 5.2, an example settings.py file is:

DATABASES = {
  "default": {
    "ENGINE": "django.db.backends.oracle",
    "NAME": "example.com:1521/orclpdb",
    "USER": "hr",
    "PASSWORD": "the-hr-password"
    "OPTIONS": {
      "pool": {
        "min": 0,
        "max": 4,
        "increment": 1,
        # Additional python-oracledb pool parameters can be added here
      }
    }
  },
}

31.5. Older Versions of Python Frameworks, SQL Generators, and ORMs

For versions of SQLAlchemy, Django, Superset, other frameworks, object-relational mappers (ORMs), and libraries that support the obsolete cx_Oracle driver but do not have native support for python-oracledb, you can add code like this to use python-oracledb:

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

Note

This must occur before any import of cx_Oracle by your code or the library.

See Connecting with SQLAlchemy 1.4 for an example.

To use Thick mode, for example, if you need to connect to Oracle Database 11gR2, add a call to oracledb.init_oracle_client() with the appropriate parameters for your environment, see Enabling python-oracledb Thick mode.

SQLAlchemy 2 and Django 5 have native support for python-oracledb so the above code snippet is not needed in those versions. Check your preferred library for which Oracle Database driver it requires.

For details on using Superset with python-oracledb, refer to the blog post Steps to use Apache Superset and Oracle Database.