How to connect Python application to OCI DBCS using Python-oracledb

In this blog I will show you how to connect Python application to OCI DBCS using the python-Oracledb thick mode.

Pre-requisite: You should have Oracle database running on DBCS OCI

What is Python-oracledb?

The python-oracledb driver is a Python programming language extension module allowing Python programs to connect to Oracle Database. Python-oracledb is the new name for Oracle's popular cx_Oracle driver.

Python-oracledb Architecture:

The python-oracledb driver enables access to Oracle Database using either one of two modes. By default, python-oracledb runs in a "thin" mode, which connects directly to Oracle Database. This mode does not need Oracle Client libraries.

However, some additional features are available when python-oracledb uses them. Python-oracledb applications that load the Oracle Client libraries via an application script runtime option are said to be in "thick" mode.

The database can be on the same machine as Python or it can be remote.

1. Create DBCS in OCI. (as a pre-requisite)

2. Install Python3:

sudo yum install -y python3

I already have installed, to check the version

3. Install python-oracledb driver:

Use this link for installation: Python for Oracle Linux

sudo yum -y install oraclelinux-developer-release-el7

sudo yum -y install python3-oracledb

4. Test your install by launching the Python console and list the available modules. Where you can see the oracledb module.

 

Python3

help('modules')

5. Create a small python application script that connects with DBCS PDB.

 

import oracledb
import os

oracledb.init_oracle_client()

db_user = os.environ.get('DBAAS_USER_NAME', 'SYSTEM')
db_password = os.environ.get('DBAAS_USER_PASSWORD', '********************')
db_connect = os.environ.get('DBAAS_DEFAULT_CONNECT_DESCRIPTOR', "10.10.10.10:1521/fsuat")

connection = oracledb.connect(user=db_user, password=db_password, dsn=db_connect)
cursor = connection.cursor()
sql = """select name from v$database"""
for r in cursor.execute(sql):
    print(r)

The above script used oracledb.init.oracle_client() which means thick mode. As thick mode used oracle client libraries.

I used thick mode because DBCS DB use encryption and encryption is only supported by thick mode.

If you are not using encryption you can use thin mode without oracle client libraries.

Below link shows Oracle Database features supported by python-oracledb Thin and Thick modes:

Features supported by python-oracledb

Connect Autonomous database in 3 different ways

In this blog, I will show you how to connect your Autonomous Database using three different ways:

Pre-Requisite: You should have a ADW or ATP DB provisioned.

1. Connect with SQL Worksheet 

We can access the browser-based SQL Worksheet directly from our ADW/ATP console. (I have ADW already provisioned). 

Login to OCI console -> Oracle Database -> Autonomous Data Warehouse -> Click on ADW DB.

Click on Database Actions dropdown list and select "View all database actions".

A sign-in page will open for the database actions. Use the administrator account, Username - admin (you create this when provision ADW) and click Sign in.

The database Actions page opens. In the Development box, click SQL.

The SQL Worksheet will open with some series of pop-up informational boxes to provide you the tour of main features, so just click next, next on each pop up.

We can run a query on sample data set to see the result.

2. Connect SQL developer to autonomous database securely without Wallet, using TLS authentication

When we provisioned autonomous database instance with a network access type of "Secure access from everywhere", by default, mTLS authentication was required, and the only ways to enable TLS in addition to mTLS are either to define an access control list (ACL) or to use a private endpoint. 

I will configure the IP ACL. Then I will disable the mTLS which in turn will enable the TLS for connecting without a wallet.

To enable TLS, go to ADW home page, in network section, click Edit next to Access control list.

In Edit ACL window, select IP address from IP notation type drop down list and click on "Add my IP address" and your computer IP address would be added. Click save.

Now in the network section, we will see ACL is enabled now and Access type automatically changed from Allow secure access from anywhere to Allow secure access from specified IPs and VCNs.

Now disable the mTLS by click on Edit button. and then deselect the checkbox and save it.

Now you will see mTLS changed to Not Required.

Next we have to obtain the TLS connection string to connect from SQL developer.

Go to ADW home page and click on Database connection button.

Scroll down to connection string section and change TLS Authentication from mTLS to TLS. this will enable SQL developer and other applications to connect to our ADW securely without a wallet.

Choose one of the connection strings, for example I choose adwtest_high and click copy to copy the connection string. we can save that in notepad to use in SQL developer.

Now go to SQL developer on your laptop and in connections panel, click on New connection button.

Enter any Name, DB username and password.

Choose connection type: Custom JDBC

Custom JDBC URL: Enter the following:

jdbc:oracle:thin:@ followed by the connection string copied in above step

Click test and connect.

3. Connect Securely using a connection wallet containing credentials.

For this we have to download the connection wallet from autonomous database home Page.

Go to Console --> ADW/ATP home page and click on Database connection

Choose wallet type as Instance wallet and click on Download wallet button.

Specify a password, you will need this password when connecting the database via SQL developer. Click Download.

Once Wallet downloaded, close the database connection dialog.

Now start the SQL developer from your laptop and click on new connection icon.

Fill the details:

Name: any name ( I used adw_wallet_test)

Authentication type: Default

Use admin username and password you used when provision database.

connection type: Cloud wallet

Configuration file: browse for the wallet you downloaded in previous step

Service: there are 3 services whenever we provision autonomous database, I choose <databasename>_high.

Click test, save and connect and its connected.

now you can query your database, so these were the 3 different ways to connect autonomous database hope you like the blog!