The views expressed in this article are my own and do not necessarily reflect the views of Oracle.
Have you always wanted to connect your Python applications to Oracle Autonomous Transaction Processing (ATP) or to Oracle Autonomous Data Warehouse (ADW)?
This is much easier than you ever thought. In this article I’ll tell you how to do this within a few steps.
First of all, go to the Oracle Cloud Free Tier website and sign up for a cloud account to receive access to Oracle Autonomous Database. Finally, provision an ATP instance. In my case it will be called MSATP.
Let’s start with accessing MSATP with Python. We will make only three steps for this:
- Download and configure the Oracle Instant Client
- Install the cx_Oracle for Python module
- Connect your Python application to the ATP instance
Download and configure the Oracle Instant Client
For downloading the Oracle Instant Client go to the download page. Select your architecture and download the Basic Package:
Unzip the file into a local directory, for example /home/opc/instantclient_19_9, on a Linux based Oracle Compute Cloud Service instance:
In case of no other Oracle software running on your system which could be impacted, add Instant Client to the runtime link path:
Finally export the library path in the shell where you run your applications:
Let’s continue with installing the cx_Oracle module for Python.
Install the cx_Oracle for Python module
First of all, install Python 3 if you haven’t done it before. In my example I will work with cx_Oracle 8. That’s why Python 3.5 or higher is required:
As you have already seen on the architectural diagram above, the cx_Oracle module will use the Oracle Client to communicate over Oracle Net with your ATP instance.
Install the cx_Oracle module from the Python Package Index (PyPI) using pip:
That’s it. Let’s start working with cx_Oracle and connect to the ATP instance.
Connect your Python application to the ATP instance
At the beginning you will need to download client security credentials to connect to your ATP instance. For downloading the client credentials (wallet) navigate to the Autonomous Transaction Processing details page of your ATP instance. Click on <<DB Connection>>:
Click on << Download Wallet >>:
Provide a password for your wallet and download the wallet to your Python environment. Subsequently unzip it, for example to /home/opc/wallet:
You will need only the following files to work with the cx_Oracle module: tnsnames.ora, sqlnet.ora and cwallet.sso.
You can store the wallet configuration file cwallet.sso separately from the tnsnames.ora and the sqlnet.ora files. Please remember, in any case you should store it securely. It’s important to protect this file and its contents to prevent unauthorized database access!
Edit the sqlnet.ora file with vi and enter the path to the cwallet.sso file:
As of now you can start accessing your ATP instance with Python. You can call off cx_Oracle.init_oracle_client() function at the beginning of your code:
Alternatively, you can export the environment variable TNS_ADMIN to /home/opc/wallet:
Test connection to the ATP instance
The following code snippet will query the products table in the sample schema SH. This read-only schema is being provisioned by default with your ATP instance:
from __future__ import print_function
import cx_Oracle
import sys
import getpass
try:
cx_Oracle.init_oracle_client(config_dir="/home/opc/wallet")
except Exception as err:
print(err);
sys.exit(1);
password = getpass.getpass()
connection = cx_Oracle.connect('admin',password,'msatp_low')
sql = """
select prod_name from sh.products where rownum<5"""
print("All rows from the above query:")
cursor = connection.cursor()
for result in cursor.execute(sql):
print(result)
print()
Copy and paste it to a file called querySH.py and run it in your Python environment:
This kind of connection is suitable for standalone connections, where your application will manage the user sessions to the ATP instance.
You may consider using cx_Oracle’s connection pooling for more sophisticated, performance critical scenarios and applications frequently connecting users to and disconnecting them from the ATP instance.
Bonus material: Simple Oracle Document Access (SODA)
As stated in the Oracle Documentation Simple Oracle Document Access (SODA) is a set of NoSQL-style APIs that let you create and store collections of documents (in particular JSON) in Oracle Database, retrieve them, and query them, without needing to know Structured Query Language (SQL) or how the documents are stored in the database.
cx_Oracle provides all methods and objects needed for working with SODA in your ATP instance.
The following code snippet will create a new collection in your ATP instance and insert a JSON document into this collection:
from __future__ import print_function
import cx_Oracle
import sys
import getpass
try:
cx_Oracle.init_oracle_client(config_dir="/home/opc/wallet")
except Exception as err:
print(err);
sys.exit(1);
password = getpass.getpass()
connection = cx_Oracle.connect('admin',password,'msatp_low')
soda = connection.getSodaDatabase()
collection = soda.createCollection("mycollection")
content = {'empid':7782,'empdata': {'firstname': 'Scott', 'lastname': 'Tiger'},"Managerid":7839,"Deptid":10}
returnedDoc = collection.insertOneAndGet(content)
key = returnedDoc.key
connection.commit()
print('The key of the new SODA document is: ', key)
Copy and paste it to a file called soda.py and run it in your Python environment:
You can use the following code snippet to retrieve the JSON document stored in your new collection:
from __future__ import print_function
import cx_Oracle
import sys
import getpass
try:
cx_Oracle.init_oracle_client(config_dir="/home/opc/wallet")
except Exception as err:
print(err);
sys.exit(1);
password = getpass.getpass()
key = input("Provide your doc key: ")
connection = cx_Oracle.connect('admin',password,'msatp_low')
soda = connection.getSodaDatabase()
collection = soda.createCollection("mycollection")
content = collection.find().key(key).getOne().getContent()
print(content)
Copy and paste it to a file called getSodaContent.py and run it in your Python environment:
That's it! You can now use the power and the efficiency of your Python applications with Oracle Autonomous databases!
I installed the library and when trying to access SQL in jupyter notebook with my credentials the following error appears:
DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "The specified module could not be found". See https://oracle.github.io/odpi/doc/installation.html#windows for help
As per documentation accessed on cx_Oracle page.
Step 1: install cx_Oracle
python -m pip install cx_Oracle --upgrade
Step 2: Download and extract Oracle Basic Client
For Windows download and extract Oracle Basic Instatnt client instantclient-basic-windows.x64-19.9.0.0.0dbru.zip file.
Step 3: Inform cx_Oracle module about the Instatnt Client location.
If you stick to documentation and extract them in c:\oracle folder then your script may look like this.
import cx_Oracle
cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_9")
Now you will be free from the error.
