How to connect to your FREE Autonomous Database using Python from Cloud Shell?

 

Remember Todd’s post about creating your Always Free Autonomous Database? What’s next? How about starting to work with it using Python language?

In your OCI (Oracle Cloud Infrastructure) web console, click on the Cloud Shell icon in the upper right corner. This opens a Linux shell right inside your browser! The first run might take more than one minute in order to configure it. You’ll have up to 5 GB of storage and numerous tools pre-installed.

Cloud Shell open

Now the fun part… In order to run a test program that will display the date and time, you’ll need to download and unzip the Autonomous Database wallet.

$ oci db autonomous-database generate-wallet --autonomous-database-id ocid1.autonomousdatabase.oc1.eu-frankfurt-1.abtheljtcwfhy5ohok66arn6ngrpqdigvq7bzcpygtfnkbldvgtk4rv3xhtq --file wallet.zip --password My_Strong_Pa55word$ unzip wallet.zip

You’ll also need to set up the environment (TNS_ADMIN environment variable) so that the driver can find the connection string stored inside the tnsnames.ora file.

$ pwd$ export TNS_ADMIN=/home/loic_lefev$ sed -i 's/?\/network\/admin/$TNS_ADMIN/' sqlnet.ora

Finally, you’ll have to install the Oracle Python driver using the pip3 package manager.

$ pip3 install --user cx_Oracle

The following program can be copied and pasted right into a file named test.py:

import cx_Oracle
def connect():
    connection = cx_Oracle.connect("dragon", "My_Strong_Pa55word", "dragon_tp")
    return connection
  
def selectdate(connection):
    with connection:
        cursor = connection.cursor()
        result = cursor.execute('''select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual''')
        data = result.fetchone()
        print("Date is " + data[0])
  
if __name__ == '__main__':
    connection = connect()
    selectdate(connection)

And when run, it will display the expected result:

$ python3 test.py
Date is 2020-09-27 13:46:02

Oracle Cloud Shell running a python script that connects to an Always Free Autonomous Transaction Processing database.

Neat! So what’s next? How about discovering Oracle Asktom one-day (or more) SQL path learning?