How to interface Oracle Database with Python and execute queries

Oracle and Python, both two words are very familiar among the developers as well as every tech-related guy also. So, today you’re gonna learn how to attach these two words or in other words how to perform DDL and DML operations on Oracle Database through Python Programming.

Installation

You must need the Oracle Database installed on your PC. I’ve written an article about the guideline for installing Oracle Database on PC. You can check out that article here.

Now comes about the preparation of Python. I believe as you’re in this article; it means you are not noob enough to show you step by step installing python and modules via pip or anaconda. Anyway, you’ll need the "cx-Oracle" module for this interfacing and it’s available via pip and anaconda officially.

I’ll use the "HR" sample user account provided with Oracle Database by default. Because this account comes up with some ready-made tables which I’m gonna use in the rest of the article as I’m not creating databases here.

Establishing a connection

To perform any queries, a connection with the database is needed. For that, you need your database’s username, password, server(host), and service name. I’m using Express Edition of Oracle Database 11g. So, my service name is “XE”. After declaring the variables of this informations, you’re ready to connect. It’s important to keep the connection establishing code into the try-except block because it can generate errors and those errors are also needed to be handled carefully.

Code 1: Connection

import cx_Oracle

def main():
    # Variables
    username: str = "HR"
    password: str = "<YOUR_PASSWORD>"
    host: str = "localhost"
    service: str = "XE"

    # Connection establishing
    connection = None
    try:
        connection = cx_Oracle.connect(
            username,
            password,
            "{0}/{1}".format(host, service)
        )
        print("Connection successful")
        connection.close()
    except cx_Oracle.DatabaseError as e:
        print(e)

if __name__ == '__main__':
    main()

The output of this code is, "Connection successful" if it connects. Otherwise, the try-except block will catch the error and print it into the terminal.

Data Definition Langauge (DDL)

A data definition or data description language (DDL) is a syntax for defining data structures, especially database schemas. DDL only care for data reading as it’s a data definition language.

Code 2: Reading data from Database (1)

Here the code starting from line no 22 to 28 is added and the rest of the code is as same as Code 1 because without creating a connection you can’t do anything. Getting back into the code, always you need a cursor for executing any query whether the query is for reading or writing, and after every successful connection, you can get the cursor from the connection object variable. For every single query you want to execute; you just need to get a new cursor object and the return of the query’s output can be grabbed from the cursor object of that query. By the way, you don’t need to reconnect every time to get a new cursor. Every time create a new cursor object from an existing connection object variable. So, after getting the cursor object, I executed a simple query to get all the First Name and Last Name data from the table named Employee. And after getting the data I just printed it all.

import cx_Oracle

def main():
    # Variables
    username: str = "HR"
    password: str = "<YOUR_PASSWORD>"
    host: str = "localhost"
    service: str = "XE"

    # Connection establishing
    connection = None
    try:
        connection = cx_Oracle.connect(
            username,
            password,
            "{0}/{1}".format(host, service)
        )
        print("Connection successful")
    except cx_Oracle.DatabaseError as e:
        print(e)

    # Executing DDL
    cursor = connection.cursor()
    cursor.execute("SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES")

    # Printing output
    for i in cursor:
        print(i)

    connection.close()        

if __name__ == '__main__':
    main()

The output of this code is all the First & Last name stored in the Employee table.

Code 3: Reading data from Database (2)

The reason I’ve put another example is to show you how to write lengthy queries and use variables inside a query. Because if you wish to use the variable in cursor execution braces you’ve to follow the below syntax. But you can also build your query on a string variable and then put that into the cursor execution braces and it will work. But Oracle’s documentation of the "cx-Oracle" module recommends the first way.

import cx_Oracle

def main():
    # Variables
    username: str = "HR"
    password: str = "<YOUR_PASSWORD>"
    host: str = "localhost"
    service: str = "XE"

    # Connection establishing
    connection = None
    try:
        connection = cx_Oracle.connect(
            username,
            password,
            "{0}/{1}".format(host, service)
        )
        print("Connection successful")
    except cx_Oracle.DatabaseError as e:
        print(e)

    # Executing DDL
    minSalary: int = 15000
    maxManagerID: int = 200
    cursor = connection.cursor()
    cursor.execute(
        """SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES
            WHERE SALARY > :sal AND MANAGER_ID < :man""",
        sal=minSalary, man=maxManagerID
    )

    # Printing output
    for fname, lname in cursor:
        print("{0} {1}".format(fname, lname))

    connection.close()    

if __name__ == '__main__':
    main()

The output of this code is, all those names from the Employee table who has a minimum salary of 15000 and manager id is smaller than 200.

Data Manipulation Langauge (DML)

A data manipulation language (DML) is some command that deals with the manipulation of data present in the database. DML only care for data writing as it’s a data manipulation language.

Code 4

Here in this code, I want to add a record on the table named Country. On line 27, I’ve executed an insertion query to add Bangladesh to the Country table. The country table has three columns; Country code, Country name, and Region id. But there is an important thing. Have you noticed on line 33? In the previous three examples, I didn’t use commit. Are you thinking that I already executed the query so the changes are done? Actually no. Changes have done temporarily but not permanently. If you log in to the Oracle database now, you won’t see the newly added record Bangladesh in the Country table. To make changes permanent, I also committed the changes. And now my changes have made into the database permanently. So far, I was only concerned about getting data thus I didn’t need to use commit because there were no changes to make. So, after performing every DML operation, you must commit it or the changes won’t be made into the database.

Before running the code there is no record of Bangladesh.

Now, run the code

import cx_Oracle

def main():
    # Variables
    username: str = "HR"
    password: str = "<YOUR_PASSWORD>"
    host: str = "localhost"
    service: str = "XE"

    # Connection establishing
    connection = None
    try:
        connection = cx_Oracle.connect(
            username,
            password,
            "{0}/{1}".format(host, service)
        )
        print("Connection successful")
    except cx_Oracle.DatabaseError as e:
        print(e)

    # Executing DML
    countryCode: str = "BD"
    countryName: str = "Bangladesh"
    regionID: int = 3
    cursor = connection.cursor()
    cursor.execute(
        """INSERT INTO COUNTRIES
        (COUNTRY_ID, COUNTRY_NAME, REGION_ID)
        VALUES (:cCode, :cName, :rId)""",
        cCode=countryCode, cName=countryName, rId=regionID
    )
    connection.commit()
    print("Insertion successful")
    connection.close()

if __name__ == '__main__':
    main()

The output of this code is positive if insertion becomes successful.

See? The changes have made to the database.

Conclusion

So far, I’ve tried to give you the basic interfacing of the Oracle database with Python. But it’s not enough. I suggest you checkout cx_Oracle’s official documentation https://cx-oracle.readthedocs.io. And I believe if you understood all the codes I showed, then the documentation will be easily understandable to you.

Share your opinion in the discussion section below and of course the questions if any. Don't forget to follow us.

💡 AND SUBSCRIBING to our YouTube TechLearnersInc and Telegram t.me/TechLearners will be amazing.