using python-oracledb to connect rds for Oracle in thin mode

 The Oracle python driver cx_Oracle only works with Oracle thick client (including Oracle instant client) to connect to Oracle database. With the release of python-oracledb, the new name for the Python Oracle driver, the default mode is thin mode, which means no thick/instant client installation required. The feature parity differences between Thin mode, Thick Mode and original cx_Oracle could be found here.

Here are quick references with code samples to connect RDS for Oracle with/without SSL.

Connect to Oracle without TLS/SSL

1. Check the Python environment. Refer to the actual Python version required in the installation guide.

which python3
/usr/bin/python3 -V

[ec2-user@ip-10-1-1-110 ~]$ which python3
/usr/bin/python3

[ec2-user@ip-10-1-1-110 ~]$ /usr/bin/python3 -V
Python 3.7.10

2. Install python-oracledb package. It's optionally run using user root for the installation to share the package with other users.

python3 -m pip install oracledb --upgrade

3. Execute following sample code the test the Oracle connectivity

#!/usr/bin/python3
import oracledb
import os

un = 'admin'
pw = 'yourComPlexPassword'
cs = 'oracle-instance1.fakeramdonid.us-east-1.rds.amazonaws.com:1521/MYTEST'

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:
        sql = """select '['||systimestamp||'] Connectivity OK' from dual"""
        for r in cursor.execute(sql):
            print(r[0])

Sample output:

[ec2-user@ip-10-1-1-110 ~]$ chmod +x non-tls.py

[ec2-user@ip-10-1-1-110 ~]$ ./non-tls.py
[30-JUL-22 08.18.53.670773 AM +00:00] Connectivity OK

Connect to Amazon RDS for Oracle with TLS/SSL

1. Enable Oracle SSL Option following the official documentation.

Make sure you have selected TLS 1.2 instead of default TLS 1.0.

2. Install additional Linux packages and Python modules using root user

yum install libcurl-devel python3-devel openssl-devel gcc ca-certificates

python3 -m pip install pyca

3. Download the Amazon RDS certificate bundle from the official documentation. Here we will use the global bundle as example, which contains intermediate and root certificates for all AWS regions.

4. Append the cert to Linux default cert.pem.

cp global-bundle.pem /etc/pki/ca-trust/source/anchors/

update-ca-trust force-enable

update-ca-trust extract

To verify the certificates installed successfully, below grep command shall have list of certs from Amazon.

grep -i "Amazon RDS" /etc/pki/tls/cert.pem

5. Execute following sample code the test the Oracle connectivity

#!/usr/bin/python3
import oracledb
import os

un = 'admin'
pw = 'yourComPlexPassword'
cs = '''(description = (retry_count=20)(retry_delay=3)(address=(protocol=tcps)
           (port=2484)(host=oracle-instance1.fakeramdonid.us-east-1.rds.amazonaws.com))(connect_data=(SERVICE_NAME=MYTEST))
           (security=(ssl_server_dn_match=yes)
           (ssl_server_cert_dn="C=US,ST=Washington,L=Seattle,O=Amazon.com,OU=RDS,CN=oracle-instance1.fakeramdonid.us-east-1.rds.amazonaws.com")))
           '''

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:
        sql = """select '['||systimestamp||'] Connectivity OK' from dual"""
        for r in cursor.execute(sql):
            print(r[0])

Sample output:

[root@ip-10-1-1-110 ec2-user]# ./tls.py
[30-JUL-22 09.10.56.125153 AM +00:00] Connectivity OK

if you only needs tls/ssl encryption without verifying the server certificates, you can use below simplified connection string:

cs1 = '''(description = (retry_count=20)(retry_delay=3)(address=(protocol=tcps)
           (port=2484)(host=oracle-instance1.fakeramdonid.us-east-1.rds.amazonaws.com))(connect_data=(SERVICE_NAME=MYTEST))
           )
           '''

Common errors and possible solutions

Error message:

oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database. Connection failed with "[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate in certificate chain (_ssl.c:1091)

Solution: Make sure you have downloaded the certificates bundle and added to cert.pem.

Error message:

oracledb.exceptions.DatabaseError: DPY-4011: the database or network closed the connection
[Errno 104] Connection reset by peer

Solution: Make sure have opened necessary firewall ports, and your TLS version is 1.2.

Error message:

oracledb.exceptions.OperationalError: DPY-6002: cannot connect to database. The distinguished name (DN) on the server certificate does not match the expected val

Solution: If you have ssl_server_dn_match=yes included in your connection string, make sure your ssl_server_cert_dn is correct. Part of your DN is your database endpoint.

Reference: Python packages used with version details

[root@ip-10-1-1-110 ec2-user]# pip3 list -v
Package            Version Location                                 Installer
------------------ ------- ---------------------------------------- ---------
aws-cfn-bootstrap  2.0     /usr/lib/python3.7/site-packages
cffi               1.15.1  /usr/local/lib64/python3.7/site-packages pip
click              8.1.3   /usr/local/lib/python3.7/site-packages   pip
configobj          5.0.6   /usr/local/lib/python3.7/site-packages
cryptography       37.0.4  /usr/local/lib64/python3.7/site-packages pip
docutils           0.14    /usr/lib/python3.7/site-packages
Flask              2.1.3   /usr/local/lib/python3.7/site-packages   pip
greenlet           1.1.2   /usr/local/lib64/python3.7/site-packages pip
importlib-metadata 4.12.0  /usr/local/lib/python3.7/site-packages   pip
itsdangerous       2.1.2   /usr/local/lib/python3.7/site-packages   pip
Jinja2             3.1.2   /usr/local/lib/python3.7/site-packages   pip
lockfile           0.11.0  /usr/lib/python3.7/site-packages
MarkupSafe         2.1.1   /usr/local/lib64/python3.7/site-packages pip
oracledb           1.0.2   /usr/local/lib64/python3.7/site-packages pip
pip                20.2.2  /usr/lib/python3.7/site-packages         rpm
prometheus-client  0.14.1  /usr/local/lib/python3.7/site-packages   pip
psutil             5.9.1   /usr/local/lib64/python3.7/site-packages pip
pyca               4.4     /usr/local/lib/python3.7/site-packages
pycparser          2.21    /usr/local/lib/python3.7/site-packages   pip
pycurl             7.45.1  /usr/local/lib64/python3.7/site-packages
pystache           0.5.4   /usr/lib/python3.7/site-packages
python-daemon      2.2.3   /usr/lib/python3.7/site-packages
python-dateutil    2.8.2   /usr/local/lib/python3.7/site-packages   pip
sdnotify           0.3.2   /usr/local/lib/python3.7/site-packages
setuptools         49.1.3  /usr/lib/python3.7/site-packages         pip
simplejson         3.2.0   /usr/lib64/python3.7/site-packages
six                1.16.0  /usr/local/lib/python3.7/site-packages   pip
SQLAlchemy         1.4.39  /usr/local/lib64/python3.7/site-packages pip
typing-extensions  4.3.0   /usr/local/lib/python3.7/site-packages   pip
Werkzeug           2.2.1   /usr/local/lib/python3.7/site-packages   pip
zipp               3.8.1   /usr/local/lib/python3.7/site-packages   pip