Connecting Python to Oracle Cloud Database
There are three flavors of Oracle Databases hosted on Oracle Cloud Infrastructure:
Bare Mental, VM, and Exadata
Autonomous Data Warehouse (ADWC)
Autonomous Transaction Processing
Here we are refering "Oracle database" to Autonomous Data Warehouse, while all three should be similar in connectivity interface as they are Oracle-like.
For those of you who don't have an Oracle cloud account, be reminded that you can apply for a free-trial account with 30-day trial period and $3000 HKD usable balance.
Before you begin
Before we discuss how to access Oracle database from Python, you should have already provisioned an
Autonomous Data Warehouse
instance. Refer to official doc for how to provision. Basically, you need to first construct a VCN (Virtual Cloud Network) under
Networking
tag, as later you'll be prompted to bind your ADWC to one of the VCNs. In the startup dialog of provisioning the ADWC, choose storage and other parameters, set up your ADMIN account, etc.
Wait for the ADWC instance (in my case, it is called
demo
) to turn from
Provising is in
Available
state, enter the details page, and enter
DB Connection
page. From the
Download
button, download the client credentials (a.k.a. your
wallet**). The wallet is very important and you will need it to make client connections via any method (SQLDeveloper, Language driver, etc).
Install Oracle Client and Python driver
To make connections to Oracle database, you need an Oracle client. Oracle client can a full one that takes a lot of space, or a light-weight one,
Instant Client
. Because a full client is not available on macOS, I opt for the Instant Client. Download the zip file of proper DB version and OS, and place it in your workspace
cd ~/Downloads
unzip instantclient-basic-macos.x64-18.1.0.0.0.zip
cd instantclient_18_1
We will also need the Python driver
cx_Oracle
. I recommend using
PyPI
python -m pip install cx_Oracle --upgrade
instantclient_18_1
directory has the dynamic libraries required by
cx_Oracle
, they must be on the
Library Search Path
in macOS dynamic linking process, available for the
dlpen
call from
cx_Oracle
.
…, the dynamic loader searches for the library in several locations until it finds it, in the following order:
$LD_LIBRARY_PATH
$DYLD_LIBRARY_PATH
The process’s working directory
$DYLD_FALLBACK_LIBRARY_PATH
Hence, we have several options, such as work in the
instantclient_18_1
directory. Or, create a symbol link in
DYLD_FALLBACK_LIBRARY_PATH
which has a default value
$HOME/lib;/usr/local/lib;/usr/lib
. The second method is more flexible since we don't have to stay in the client directory forever
ln -s ~/Downloads/instantclient_18_1/libclntsh.dylib ~/lib/
Configure Oracle database TNS
TNS stands for
Transparent Network Substrate
, an Oracle computer-networking technology mainly designed for connection to Oracle databases. We need to tell the Instant Client the TNS information of the database we would like to access.
cd ~/Downloads/instantclient_18_1/network/admin
If there is not
network/admin
inside,
mkdir
the subdirectories.
Unzip the wallet files to
network/admin
unzip ~/Downloads/Wallet_demo.zip .
ls
# README ewallet.p12 ojdbc.properties tnsnames.ora
# cwallet.sso keystore.jks sqlnet.ora truststore.jks
There are a bunch of files in the wallet, but only three of them is required to make the connection (though you can always put all files inside
network/admin
).
tnsnames.ora
defines the namespace of where to find the databases (host, portnumber, service name) when
cx_Oracle
is asked to make a connection.
cat tnsnames.ora
demo_high = (description= (address=(protocol=tcps)(port=1522)(host=adb.ap-tokyo-1.oraclecloud.com))(connect_data=(service_name=lsd2p1z0t6mcrz2_demo_high.adwc.oraclecloud.com))(security=(ssl_server_cert_dn=
"CN=adb.ap-tokyo-1.oraclecloud.com,OU=Oracle ADB TOKYO,O=Oracle Corporation,L=Redwood City,ST=California,C=US")) )
# ...
There are other TNS entry points, like
demo_medium
and
demo_low
, they refer to different volumes of data transfer between the client and the database.
Get to the Python part
We are all set, lets create a python script with the following content:
import cx_Oracle
connection = cx_Oracle.connect('
', '
', 'demo_high')
print('Database version:', connection.version)
connection.close()
If the script terminates with any error, you will see output
(base) ➜ Desktop python connection.py
Database version: 18.4.0.0.0
Then we successfully connect to the database.
Note: the third parameter of
cx_Oracle.connect
should one of the names defined in
tnsnames.ora
like
demo_high
,
demo_HIGH
(TNS name is case-insensitive),
NOT the long connection string
(something starting with
adb.ap-tokyo-1.oraclecloud.com
). If you mistakenly use the connection string as the third parameter, you are likely to end up with error
TNS: connection closed
.