Oracle Developers
Published in

Oracle Developers

Using SQLAlchemy 2.0 (development) with python-oracledb

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.

Now native support for the python-oracledb namespace has been merged to the forthcoming SQLAlchemy 2.0 code line. 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 uninstalled SQLAlchemy 1.4:

python -m pip uninstall sqlalchemy

and then I grabbed the development code:

git clone git@github.com:sqlalchemy/sqlalchemy.git

and installed the main branch, which is where SQLAlchemy 2.0 work is landing:

cd sqlalchemy
python setup.py install

Next, I created my 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

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

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

Or, more conveniently for me:

engine = create_engine(
f'oracle+oracledb://{username}:{password}@',
thick_mode=thick_mode,
connect_args={
"dsn": os.environ.get("PYTHON_CONNECTSTRING")
})

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:

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

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.

Keep the conversation going on the DevRel public Slack!

--

--

--

Aggregation of articles from Oracle engineers, Groundbreaker Ambassadors, Oracle ACEs, and Java Champions on all things Oracle technology. The views expressed are those of the authors and not necessarily of Oracle.

Recommended from Medium

Build a Voice Proxy With Cloud Functions

Setting up our Enemy AI: Movement and Rotation

10 Female Nigerian Developers You Should Follow

DB supports Superdigital on global expansion

Are we performance testing the right way?

The Dangers of Docker: Vulnerabilities in Containerized CI/CD

Which Web Browser Is Best For You?

Multi-Arch Images with Docker Buildx and QEMU

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Christopher Jones

Christopher Jones

Oracle Database product manager for language drivers including Python python-oracledb, Node.js node-oracledb, and PHP OCI8 — and more!

More from Medium

Python Flask: Asynchronous Upload to AWS S3

Chunk Read A Large File in Python

How to Mock PostgreSQL with Pytest and pytest-postgresql

Python — Best Practices for Using List Tuple, Dict and Set