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 two main components:

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

SQLAlchemy is used widely in data science and analysis for reading data from huge datastores. It is used in tandem with popular Python data libraries such as NumPy and Pandas (you can refer to this informative article!) for efficient data retrieval and processing. In short, SQLAlchemy eliminates the need for writing complex SQL Queries within Python programs for any database operation, which speeds up the productivity 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 describe 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 - using any of the Oracle ADB 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 'wallet'. 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 one, 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 three kinds of database services suit your workload: ADW, ATP, or AJD (Choose ATP if you are unsure). Click through the few prompts and enter the required information, such as the database name. My database is named scrajudb_high.

During database creation, the 'Allow secure access from everywhere' option is selected. This option gives easy access to the database from your Python applications.

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).

Also, please check that Python (v3.6 and above) and Oracle Instant Client (latest version) are installed on your local machine.

Download the Oracle Database wallet files

Downloading the wallet 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 running my Oracle client on a Windows 10 local machine. Oracle DB's Python driver - cx_Oracle - is used along with SQLAlchemy for connecting to the Oracle ADB. The cx_Oracle installation instructions are available here.

  • Download the wallet ZIP file, as shown in the earlier section

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

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

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

  • There are now two options:

    • Move the three files to the network\admin directory of the client libraries used by your application. For example, if you are using Instant Client 19c, you would put them in C:\Program Files\Oracle\instantclient_19_11\network\admin directory.

    • Alternatively, move them to any secure, accessible directory, for example, C:\Users\scraju\Cloud\DB_WALLETDIR.

      Now here's the important step: Edit sqlnet.ora and change the wallet location directory to the directory containing the cwallet.sso file:

      WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="C:\Users\scraju\Cloud\DB_WALLETDIR")))
SSL_SERVER_DN_MATCH=yes

      Then you need to indicate to the Python Oracle libraries 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 version 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 is needed, and you don't need to edit sqlnet.ora, if you have put all the files in the network\admin directory.

Now that the wallet is set, We can connect to the Cloud ADB using one 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 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 essential for performance when applications frequently connect and disconnect from the database. The pool implementation uses Oracle’s session pool technology which supports Oracle’s high availability features and is recommended for applications that must be reliable. This also means that small pools can be helpful in applications that require a few connections available for infrequent use.

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

A connection pool is created by calling SessionPool() function. This function is generally called during application initialization. The initial pool size, maximum pool size, and 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, you can simplify the call with the latest version
# --------------------------------------
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 queries the database for the current date.

That should get you up and running with Oracle ADB and SQLAlchemy!

Comments

Post a Comment

Popular posts from this blog

Flutter for Single-Page Scrollable Websites with Navigator 2.0

Image
  Single page scrollable websites are everywhere. In this website design pattern, all the content is presented in one long-scrolling layout that contains multiple sections. Visitors can scroll or jump to a section by clicking buttons of a sticky menu. This pattern is a good fit for small content such as brochure websites, software library documentation, portfolios, and landing pages that are used to convert users [ 1 ]. Designers also love this pattern because it is simple, clean, and enables cool scroll animations. Jetbrains Mono single-page scrollable website Hive docs multi-page scrollable website In this series of articles, we will explore how to build a  single-page scrollable website  using Flutter. We will benefit from the Navigator 2.0 API to provide a good navigation experience to the users. You can find the source code of this series’ sample apps on my  Github page . The implementation details of the sample apps will be explained in the following articles: ...
Read more

A Data Science Portfolio is More Valuable than a Resume

Image
A Data Science Portfolio is More Valuable than a Resume Benjamin Obi Tayo Ph.D. Following Nov 20  · 5 min read D ata Science is a practical field. Hands-on skills are very important especially when you are interested in working outside academia as a practicing data scientist. In academia, you need more theoretical and research skills. While in-depth knowledge in the theoretical foundations of data science is important, as a practicing data scientist, hands-on experience is very crucial and one way to showcase you hands-on skills is via building a very great portfolio. Companies interested in hiring you would definitely be asking you for a portfolio, as it gives evidence of your strengths in fundamental data science concepts. While a data science resume is important, a data science portfolio is more valuable than a resume. This article will discuss 3 important platforms that can be used for portfolio buildi...
Read more

Better File Storage in Oracle Cloud

Image
Use Oracle Application Express with Oracle Cloud Infrastructure REST APIs to upload, download, and manage big files in Oracle Cloud. By Adrian Png November 27, 2019 Oracle Cloud Infrastructure  provides a comprehensive suite of REST APIs for accessing and managing cloud resources. For example, administrators can use REST APIs to create and manage autonomous databases and initiate backups. Oracle Application Express (Oracle APEX)  makes working with REST easy. The  APEX_WEB_SERVICE  package, available since Oracle APEX 4.0, provides developers with a function for consuming REST APIs. Oracle APEX 5.0 introduced the  APEX_JSON  package, allowing easy parsing of JSON responses. Oracle APEX 18.1 introduced the Web Source Module (WSM), which enables developers to access REST services and use the data in APEX components such as reports, interactive reports, and interactive grids. These features, when used together with Oracle Cloud Infrastructure REST ...
Read more