DEV Community

loading...
Cover image for How to connect to your FREE Autonomous Database using Python from Cloud Shell?

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

loiclefevre profile image Loïc ・2 min read

Oracle made available its Autonomous Database (a cloud database managed with machine learning) for free since September 2019:
Oracle Always Free Tiers

Todd Sharp (Oracle Cloud Developer Advocate) posted about creating an Autonomous Database in the cloud.

So what's next? How about starting to work with your Autonomous Database using Python language?

Once you've subscribed to a free account and you've created your Autonomous Database, you can use the OCI (Oracle Cloud Infrastructure) Cloud Shell:
OCI Web Console

In your OCI 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.

OCI Cloud Shell

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 (which holds numerous information to connect to your database).

$ 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?

Discussion (0)

pic
Editor guide