Experiment 1: Connecting your Python application to an Oracle Cloud Autonomous Database

This time I'll describe my experiment of connecting a simple Python application to an Oracle Cloud Database.

My recipe:

1. Windows 10
2. Python 3.7
3. PyCharm community edition from JetBrains
4. A free OCI account with an ATP/ADW instance up and running. If you do not have an OCI account, go here or google for “Oracle Cloud Free Tier”
5. A locally installed Oracle Instant Client, even the basic one, but be aware it must be at least 19 or higher, otherwise, the whole thing will not work. If you need you can download the right version for your os from Oracle. It’s a simple zip file to unpack in a folder of your choice, e.g. C:\Oracle\instantclient_19_14

 

Credits: this experiment is based on the Christopher Jones’ session at the 2021 Oracle Database Word, you can find it here. 

Disclaimer: I’m not a professional Python developer, I do not use it in my daily job, but I like it, so the meaning of this experiment is to have fun with Python and ADB. Said that you might find leaks in my Python code.

Setting up the connection

In the next steps, we will set up a connection using Python to an ADB instance running in an OCI tenancy and then run a query against the Star Schema Benchmark (SSB) sample schema.

First thing first, we need the credentials to connect to the ADB, aka “Wallett”.

Log in to your OCI tenancy, open the Oracle cloud “get started” console and from the upper left hamburger menu navigate to the “Oracle database” page and open “autonomous database”.

if you chose a specific compartment when you provisioned your ADB (like I did), choose the compartment from the list on the left.

From the autonomous database grid, click the one you want to connect to.

This opens the ADB details page. In my case, the ADB is an Autonomous Transaction Processing database (ATP) but it can be an ADW as well.

Click on DB connection, and in the pop-up window select “instance wallet” as wallet type and then “Download wallet”.

This opens the download wallet page, where you have to set a password for the wallet before downloading. Not all clients require that password (and Python is one of these) but maybe you’ll use the wallet for other things in future, so choose a password and save it somewhere.

Download the wallet_xxxxx.zip file and unzip the content in a folder of your choice, I suggest creating a new one among the instant client folders.

Now, if you look at the files, you’ll notice a classic tnsnames.ora, containing a list of connection definitions slightly different from the ones you may be used to. You should find something like

db[a_series_of_numbers]_high     =

db[a_series_of_numbers]_low      =

db[a_series_of_numbers]_medium   =

db[a_series_of_numbers]_tp       =

db[a_series_of_numbers]_tpurgent =

These are the predefined service name for our ADB. We’ll use one of them to connect in the next steps. You can find more information about these entries here.

Now switch to PyCharm IDE and create an empty Python project, be sure to select the options “Virtualenv” and “create a main.py”.

After this, it’s time to install the CX_Oracle package to connect to Oracle databases: in PyCharm open the terminal window, get sure the virtual environment is running by checking that the string “(venv)” shows up at the beginning of the command prompt. 

If not, start manually the virtual environment with one of the following command:
Venv\scripts\activate.bat if your terminal window is a cmd.exe window

Venv\scripts\activate.ps1 if your terminal window is a PowerShell.exe window

Now, to install the CX_Oracle package run the following command from the terminal
pip install cx_Oracle –-upgrade

then let’s install a package to handle .env files, because we do not want our database credentials to be hardcoded in the source code, so we’ll use environment variables to store them:
pip install python-dotenv --upgrade

Now, at the same level of main.py we create a .env file to store the following environment variables:

DBUSER=ADMIN

DBPASSWORD= the ADMIN  password for the ADB

CLIENT_LIB= here we set the folder where we unzipped the instant client libraries, e.g. C:/Oracle/instantclient_19_14 (be sure to use ‘/’ in the path string)

CLOUD_CONN= here we set a connection string in the form Protocol://host:port/service_name.

We pick up the necessary parameters for the connection string from one of the entries you previously see in the tnsnames.ora, so we’ll compose a connection URL like: tcps://adb.ap-sidney-1.oraclecloud.com:1522/a1b2c3d4e5f6g7h_db202201040000_low.adb.oraclecloud.com

Now, the most important detail: at the end of the connection string we append the named parameter “wallet_location”, where to set the folder containing the unzipped wallet files, e.g.:

wallet_location=C:/Oracle/instantclient_19_14/wallet  (again use ‘/’ in the path string)

So we’ll end up with a URL connection string like this:

CLOUD_CONN=tcps://adb.ap-sidney-1.oraclecloud.com:1522/a1b2c3d4e5f6g7h_db202201040000_low.adb.oraclecloud.com?wallet_location=C:/Oracle/instantclient_19_14/wallet

Writing the Python code

In our main.py the first thing is obviously to import the packages we installed

from dotenv import load_dotenv
import cx_Oracle

and to use the environment variables we defined, we have to add
import os

now, the first thing after  if __name__ == '__main__': is to initialize the environment variables:
load_dotenv()

then we can read the .env values and set some local variables to use:
cloud_user = os.environ.get("DBUSER")
cloud_pwd = os.environ.get("DBPASSWORD")
client_lib = os.environ.get("CLIENT_LIB")
cloud_conn = os.environ.get("CLOUD_CONN")

last thing before connecting, we initialize CX_Oracle loading the Oracle Client libraries to make it talk to our ADB instance:
cx_Oracle.init_oracle_client(lib_dir=client_lib)

now we are ready to connect to the database, so we open a connection: if something may go wrong, this is the place:

connection = cx_Oracle.connect(user=cloud_user, password=cloud_pwd, dsn=cloud_conn)

if there are no errors, we can try to run a simple query against the SSB.CUSTOMER table and show out the results in the console:
with connection.cursor() as cursor:
    sql = "select distinct c_city, c_nation, c_region  from ssb.customer order by c_nation"
    for r in cursor.execute(sql):
        print(f"city:{r[0]}, nation:{r[1]}, region:{r[2]}")
connection.close()

and that’s all. Have fun!