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