HomeTechnologyConnecting to Oracle Cloud Autonomous Database through SQLAlchemy

Connecting to Oracle Cloud Autonomous Database through SQLAlchemy

SQLAlchemy is a powerful Python library that abstracts the communication between Python applications and relational databases. It provides 2 main components:

  • An SQL abstraction toolkit called the Core that converts Python expressions to SQL statements
  • An optional, but broadly used Object Relational Mapper (ORM) component that maps Python objects to the Relational DB tables.

SQLAlchemy is used broadly in data science and analysis for reading data from huge datastores. It is used in tandem with wellliked python data libraries such as numpy and pandas for efficient data retrieval and processing. In quick, SQLAlchemy eliminates the need for writing complex SQL Queries inside Python programs for any database operation, which speeds up the productiveness and efficiency of the Python applications.

SQLAlchemy works very well with Oracle on-premises and Cloud Autonomous Databases (ADBs) and can seamlessly tap into the unique features of Oracle Database like Session Pooling, DRCP, Shared servers, Sharding etc.

This post will portray how to

  • make a simple connect to Oracle ADB through SQLAlchemy
  • create client-side session pool connections on the Oracle ADB through SQLAlchemy

To connect your applications to an Oracle Autonomous Cloud Database – any of the Oracle Autonomous Database services like Autonomous Data Warehouse (ADW), Autonomous Transaction Processing (ATP), or the new Oracle Autonomous JSON Database (AJD) – you will need to download and use a ‘pockets’. This gives mutual TLS which provides enhanced security for authentication and encryption.

Pre-requisites

This post assumes you’ve already created an Oracle Cloud database. But if you don’t already have 1, then you can quickly get an ‘Always Free’ account for no cost and create a database. There are other posts and documentation that show this, such as this blog. In summary, log in to your cloud account and, from the left hand menu, select which of the 3 kinds of database services suit your workload: ADW, ATP, or AJD (Choose ATP if you are unsure). Click through the few prompts and enter required information, such as the database name. My database is named as scrajudb_high.

During database creation the ‘Allow secure entry from everywhere’ option is selected. This gives easy entry to the database from your Python applications.

Connecting to Oracle Cloud Autonomous Database through SQLAlchemy

During creation you’ll also set a password for the privileged database ADMIN user. (See this blog to understand how to use ADMIN to create a ‘normal’ un-privileged user).

Download the Oracle Database pockets files

Downloading the pockets is simple. Please check out this excellent article on Connecting to Oracle ADBs by Christopher Jones for the same.

Connecting to an Autonomous Database using DSN in SQLAlchemy

For connecting to the Oracle DB, I will be operating my Oracle client in a Windows 10 machine. Oracle DB’s Python driver – cx_Oracle – is used alongside with SQLAlchemy for connecting to the Oracle ADB. The cx_Oracle installation instructions are available here.

  • Download the pockets ZIP file, as proven in the above section

  • Extract the cwallet.sso, sqlnet.ora, and tnsnames.ora files. Keep the files in a secure location:

      [email protected]  1 scraju  staff   6725 15 Aug 00:12 cwallet.sso
      -rw-r--r--   1 scraju  staff    134 15 Aug 10:13 sqlnet.ora
      [email protected]  1 scraju  staff   1801 15 Aug 00:12 tnsnames.ora
    

    The other files in the ZIP are not desired for the Oracle Python driver (cx_Oracle). They would be used, for example, if you were going to connect via JDBC.

  • There are now 2 options:

    • Move the 3 files to the communityadmin directory of the client libraries used by your application. For example if you are using Instant Client 19c, you would put them in C:Oraclescrajuinstantclient_19_11communityadmin directory.

    • Alternatively, move them to any secure, accessible directory, for example C:UsersscrajuCloudDB_WALLETDIR.

      Now here’s the necessary step: Edit sqlnet.ora and change the pockets location directory to the directory containing the cwallet.sso file:

      WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:UsersscrajuCloudDB_WALLETDIR")))
      SSL_SERVER_DN_MATCH=yes
      

      Then you need to indicate to tools where the tnsnames.ora and sqlnet.ora files are, for example by setting the environment variable TNS_ADMIN to C:\Users\scraju\Cloud\DB_WALLETDIR. The Python Oracle library (cx_Oracle 8 and above) will let you pass the directory at runtime using the config_dir argument of the function init_oracle_client(). Neither of these are desired, and you don’t need to edit sqlnet.ora, if you have put all the files in the communityadmin directory.

Now that the pockets is set, We can connect to the Cloud ADB using 1 of the connection strings from tnsnames.ora as the DSN in SQLAlchemy as below:

import cx_Oracle
from sqlalchemy import create_engine
import sys
import os

if sys.platform.startswith("darwin"):
    cx_Oracle.init_oracle_client(
        lib_dir=os.environ.get("HOME")+"/Downloads/instantclient_19_8",
        config_dir="")
elif sys.platform.startswith("win"):
    cx_Oracle.init_oracle_client(
        lib_dir=r"C:\Program Files\Oracle\instantclient_19_11")
# else assume system library search path includes Oracle Client libraries
#  On Linux, use ldconfig or set LD_LIBRARY_PATH, as described in installation documentation.

username = "admin"
# set the password in an environment variable called "MYPW" for security
password = os.environ.get("MYPW")
dsn = "scrajudb_high"

engine = create_engine(
    f'oracle://{username}:{password}@{dsn}/?encoding=UTF-8&nencoding=UTF-8', max_identifier_length=128)

with engine.connect() as conn:
    print(conn.scalar("select sysdate from dual"))

We use SQLAlchemy’s create_engine() function to create an engine object that will handle the Oracle ADB connection. The above program simply queries the database for the current date.

Connecting to Autonomous Database using Connection Pooling in SQL Alchemy

Oracle DB’s Python library cx_Oracle lets applications create and maintain a pool of connections to the database. Connection pooling is necessary for performance when applications frequently connect and disconnect from the database. The pool implementation uses Oracle’s session pool technology which helps Oracle’s high availability features and is counseled for applications that should be reliable. This also means that small pools can be useful for applications that want a few connections available for rare use.

The Wallet location and files needs to be set and ready as mentioned in the previous section.

A connection pool is created by calling SessionPool(). This is generally called during application initialization. The initial pool size, maximum pool size and the increment parameters are provided at the time of pool creation. 

Once the Session Pool has been created, we use the SQLAlchemy’s create_engine() function (with pool.acquire passed as a value to the creator parameter) to create an engine object that will handle the pooled connections to Oracle ADB.

import cx_Oracle
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
import sys
import os

if sys.platform.startswith("darwin"):
    cx_Oracle.init_oracle_client(lib_dir=os.environ.get(
        "HOME")+"/Downloads/instantclient_19_8")
elif sys.platform.startswith("win"):
    cx_Oracle.init_oracle_client(
        lib_dir=r"C:\Program Files\Oracle\instantclient_19_11")
# else assume system library search path includes Oracle Client libraries
# On Linux, use ldconfig or set LD_LIBRARY_PATH, as described in installation documentation.

# Create the pool once at startup
# --------------------------------------
# ADB Connection
username = "admin"
# set the password in an environment variable called "MYPW" for security
password = os.environ.get("MYPW")
connect_string = "scrajudb_high"

# --------------------------------------
# With cx_Oracle 8.0, the encodings default to UTF-8.
# With cx_Oracle 8.2, the threaded parameter is ignored and threading is always used.
# In summary, with the latest version you can simplify the call
# --------------------------------------
pool = cx_Oracle.SessionPool(user=username, password=password, dsn=connect_string,
                             min=4, max=4, increment=0, threaded=True,
                             encoding="UTF-8", nencoding="UTF-8")


engine = create_engine("oracle://", creator=pool.acquire,
                       poolclass=NullPool, max_identifier_length=128)

# Then each 'user' will use a pooled connection

with engine.connect() as conn:
    print(conn.scalar("select sysdate from dual"))

The above program simply queries the database for the current date.

Go to the source

Most Popular