Using SQLAlchemy 2.0 with python-oracledb for Oracle Database

Update: SQLAlchemy 2 has been released! Congratulations to the development team. The post below was originally written before the final release, but has been updated.

Earlier I blogged about how to use python-oracledb 1.0 with SQLAlchemy 1.4, and showed the few extra lines of code to map the new driver name to our old name, cx_Oracle.

Native support for the python-oracledb namespace has been merged to SQLAlchemy 2.0. Here’s a little example showing how to connect in either the default ‘Thin’ mode (that doesn’t need Oracle Client libraries), or the ‘Thick’ mode if you want extra functionality. All SQLAlchemy functionality passes in Thin mode, so you only need Thick mode if you want to extend your application, for example, with Oracle Advanced Queuing features, or perhaps if you want the reliability of using Oracle Application Continuity.

First, I upgraded SQLAlchemy to the latest release:

python -m pip install sqlalchemy --upgrade

and, of course, installed python-oracledb:

python -m pip install oracledb

Next, I created a file sa-pydb.py, shown below.

Finally, I set the environment variables PYTHON_USERNAME and PYTHON_PASSWORD to my database credentials. I set PYTHON_CONNECTSTRING to an “Easy Connect” string “localhost/orclpdb1” which has the host name and the database service running on that host.

Running the program:

python sa-pydb.py

gives the query output

python-oracledb thn : 1.0.0

showing that the default ‘Thin’ mode was in use.

Uncommenting one of the thick_mode lines gives:

python-oracledb thk : 1.0.0

Note the use of +oracledb, which is new in SQLAlchemy 2.0. The additional keyword differentiates between the use of cx_Oracle and python-oracledb.

The python-oracledb connect() function can take a number of parameters. These can be passed with SQLAlchemy’s connect_args, for example:

engine = create_engine(
    f'oracle+oracledb://:@',
        thick_mode=False,
        connect_args={
            "user": username,
            "password": password,
            "host": cp.host,
            "port": cp.port,
            "service_name": cp.service_name
    })

Or, more conveniently for my quick testing since I use an Easy Connect format connection string like localhost/orclpdb1:

un = os.environ.get("PYTHON_USERNAME")
cs = os.environ.get("PYTHON_CONNECTSTRING")
pw = getpass.getpass(f'Enter password for {un}@{cs}: ')
engine = create_engine(
    f'oracle+oracledb://:@',
    connect_args={
        "user": un,
        "password": pw,
        "dsn": cs
    })

To connect with the default Thin mode to Oracle Autonomous Database using a wallet (if you haven’t swapped to wallet-less 1-way TLS), you could use something like:

un = os.environ.get("PYTHON_USERNAME")
pw = os.environ.get("PYTHON_PASSWORD")
# directory containing the extracted wallet.zip tnsnames.ora file
cd = os.environ.get("PYTHON_CONFIG_DIR")
# directory containing the extracted wallet.zip ewallet.pem file
wloc = os.environ.get("PYTHON_PEM_DIR")
# wallet password created when downloading the wallet
wpw = os.environ.get("PYTHON_WALLET_PASSWORD")
# connect name from the tnsnames.ora file, like 'myclouddb_low'
cs = os.environ.get("PYTHON_CONNECT_ALIAS")
engine = create_engine(
    f'oracle+oracledb://:@',
    connect_args={
        "user": un,
        "password": pw,
        "dsn": cs,
        "config_dir": cd,
        "wallet_location": wloc,
        "wallet_password": wpw,
    })

For testing from inside a firewall, I add a couple of extra parameters to connect_args:

        "https_proxy": "myproxy.oracle.com",
        "https_proxy_port": 80

Using a proxy is too slow for production systems, but handy for quick access.

Here’s a fuller example if you want to play: https://github.com/cjbj/python-oracledb-demos-2022/blob/main/6_sqlalchemy_example.py It also shows how to use a connection pool with SQLAlchemy, which is important for multi-user applications.

Keep the conversation going on the DevRel public Slack!