Part I: Python and the Oracle Autonomous Database - Three Ways to Connect

Don't feel like reading?

Watch the video:

Welcome back

I finally had a break in my PM duties to share a small afternoon project [I started a few weeks ago]. I wanted to develop some code that allowed me to connect to my Autonomous Database using either our python-oracledb driver (library) or with Oracle REST Data Services (ORDS).

I undertook this effort as I also wanted to make some comparisons and maybe draw some conclusions from these different approaches.

NOTE: If you don't feel like reading this drivel, you can jump straight to the repository where this code lives. It's all nicely commented and has everything you need to get it to work. You can check that out here.

The test?files

Reviewing the code, I’ve created three Python test files. test1.py relies on the python-oracledb library to connect to an Oracle Autonomous database while test2.py and test3.py rely on ORDS (test3.py uses OAuth2.0, but more on that later).

Configuration

Configuration directory

I set up this configuration directory (config_dir) to abstract sensitive information from the test files. My ewallet.pem and tnsnames.ora files live in this config_dir. These are both required for Mutual TLS (mTLS) connection to an Oracle Autonomous database (you can find additional details on mTLS in the docs here).

Other files

Other files include oauth2creds.py, testurls.py, and walletcredentials.py. Depending on the test case, I'll use some or all of these files (you'll see that in Part II and III).

NOTE: If not obvious to you, I wouldn't put any sensitive information into a public git repository.

Connecting with python-oracledb

One approach to connecting via your Oracle database is with the python-oracledb driver (library). An Oracle team created this library (people much more experienced and wiser than me), and it makes connecting with Python possible.

FYI: I'm connecting to my Autonomous Database. If you want to try this refer to the documentation for using this library and the Autonomous database. You can find that here.

The Python code that I came up with to make this work:

# Connecting to an Oracle Autonomous Database using the
# Python-OracleDB driver.

import oracledb

# A separate python file I created and later import here. It
# contains my credentials, so as not to show them in this script here.

from walletcredentials import uname, pwd, cdir, wltloc, wltpwd, dsn

# Requires a config directory with ewallet.pem and tnsnames.ora files.

with oracledb.connect(user=uname, password=pwd, dsn=dsn, config_dir=cdir, wallet_location=wltloc, wallet_password=wltpwd) as connection:
   with connection.cursor() as cursor:

# SQL statements should not contain a trailing semicolon (";")
# or forward slash ("/").

???????sql = """select * from BUSCONFIND where location='ZAF'
???????order by value ASC """
???????for r in cursor.execute(sql):
???????   print(r)e        

Here you can see how I import the wallet credentials from the walletcredentials.py file. Without that information, this code wouldn't work. I also import the database username, password, and configuration directory (which includes the ewallet.pem and tnsnames.ora files).

From there, the code is pretty straightforward. However, some library-specific syntax is required (the complete details are in the docs, found here), but aside from that, nothing is too complicated. You’ll see the SQL statement in this code; but the proper SQL format looks like this:

SELECT?* FROM busconfind WHERE location='zaf'
ORDER BY value ASC;        

And here is an example of this SQL output in a SQL Worksheet (in Database Actions):

FYI: This is a Business Confidence Index data-set, in case you werecurious (retrieved here).

That SQL allows me to filter on a Location and then return those results in ascending order according to the Value column. When I do this using the python-oracledb driver, I should expect to see the same results.

NOTE: You've probably noticed that the SQL in the Python file differsfrom that seen in the SQL Worksheet. That is because you need toescape the single quotes surrounding ZAF, as well as remove thetrailing semi-colon in the SQL statement. This is documented in thepython-oracledb documentation, you just have to be aware of this.

Once I have all the necessary information in my walletcredentials.py file, I can import that into the test1.py file and execute the code. I chose to run this in an Interactive Window, but you can also do this in your Terminal. In the images (from top to bottom), you'll see the test1.py file, then a summary of the output from that SQL query (contained in the test1.py code), and finally, the detailed output (in a text editor).

Wrap-up

For those that have an existing Free Tier tenancy, this could be a good option for you. Of course, you have to do some light administration. But if you have gone through the steps to create an Autonomous database in your cloud tenancy, you probably know where to look for the tnsnames.ora and other database wallet files.

I’m not a developer, but I think it would be nice to be able to simplify the business logic found in this Python code. Maybe it would be better to abstract it completely. For prototyping an application (perhaps one that isn’t micro services-oriented, this could work) or for data- and business analysts, this could do the trick for you. In fact, the data is returned to you in rows of tuples; so turning this into a CSV or reading it into a data analysis library (such as pandas) should be fairly easy!

The end

This brings me to the end of Part I of this three-part series. Stay tuned for Part II where I explore connecting to my Autonomous via ORDS sans OAuth2.0!

Follow

And of course, don't forget to follow for more!

• Twitter
• YouTube
• TikTok
• Blog
• GitHub
• Medium

#oracle #python #database #oracledeveloper #pythonprogramminglanguage #pythondeveloper #oracledatabase #sql #sqlqueries #oraclecloud #sqlprogramming