How to interface Oracle Database with Python and execute queries
Tags: database, beginners, python, tutorial
Published: 2021-01-06 21:21:48

[**Oracle**](https://en.wikipedia.org/wiki/Oracle_Corporation) and [**Python**](https://en.wikipedia.org/wiki/Python_(programming_language)), 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**](https://en.wikipedia.org/wiki/Data_definition_language) and [**DML**](https://en.wikipedia.org/wiki/Data_manipulation_language) operations on [**Oracle Database**](https://en.wikipedia.org/wiki/Oracle_Database) through [**Python Programming**](https://en.wikipedia.org/wiki/Python_(programming_language)).

![Oracle Database](https://dev-to-uploads.s3.amazonaws.com/i/i64ekh7z40wvfmw42dml.png)

### **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**](https://dev.to/rizwan486/how-to-setup-oracle-database-11g-xe-on-windows-and-unlock-the-hr-user-jih-temp-slug-8824709).

{% link techlearners/how-to-setup-oracle-database-11g-xe-on-windows-and-unlock-the-hr-user-37p3 %}

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"**](https://oracle.github.io/python-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
```python
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.

![The output of Connection Establishing Code](https://dev-to-uploads.s3.amazonaws.com/i/mdgrx2t98nljx5zo7zkk.png)

---

### **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.

```python
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.

![The output of Reading data from Database (1) Code](https://dev-to-uploads.s3.amazonaws.com/i/tx6fka72l8c2phy2ge0u.png)

#### 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.

```python
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**.

![The output of Reading data from Database (2) Code](https://dev-to-uploads.s3.amazonaws.com/i/gul9xz9ipv5afotp7j80.png)

---

### **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**.

![No record of Bangladesh](https://dev-to-uploads.s3.amazonaws.com/i/7koi7sqq5wfs13iemlik.png)

> Now, run the code

```python
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.


![The output of Data Inserting Code](https://dev-to-uploads.s3.amazonaws.com/i/bi3g5sn2x1hy526anuoo.png)

> See? The changes have made to the database.

![Added record Bangladesh](https://dev-to-uploads.s3.amazonaws.com/i/ezjw2upgd5f10rvrdgu4.png)

---

### **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**](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.
![Best of Luck](https://dev-to-uploads.s3.amazonaws.com/i/qm2u6bsds1vjr1ci47b4.png)

*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**](https://www.youtube.com/c/TechLearnersInc) ***and Telegram*** [**t.me/TechLearners**](https://t.me/TechLearners) ***will be amazing.***