CRUD Operation on Oracle Database Using Python

In this article, we will learn how to perform CURD operations on an Oracle Database by using Python. Oracle Database is a Database Management System produced and marketed by Oracle Corporation. It supports the Structured Query language (SQL) to Manage and Manipulate the Data. As a prerequisite, you must have installed the Oracle database in your system. 

What is CRUD Operation?

CURD operation refers to the basic four operations which are Create, Update, Read and Delete. In order to establish python-oracle connectivity we need to install a connector that communicates with any database through our Python program we require a connector which is nothing but the cx_Oracle module. To Install the cx_Oracle module run the below command:

pip install cx-Oracle

Important Functions to Perform CRUD Operation on Oracle Database Using Python

Basic Functions you need to know before performing CURD operations:

connect(): Connect function is used to establish connections between the database and python the syntax is as follows-

Syntax: cx_Oracle.connect('username/password@host:port/xe')

execute(): Execute function executes the SQL query passed in its arguments 

Syntax: cursor.execute("SQL Query")

commit(): Commit function is used to commit changes and save them permanently to the database

Syntax: con.commit()

close(): Close function is used to close the connection to the database so it cannot be used further

Syntax: con.close()

Creation of Table

CREATE operation is generally used to create tables in a database CREATE is a Data Definition Language(DDL)  command let’s create a table named as GeeksForGeeks by using CREATE command.

Syntax: cursor.execute("CREATE TABLE TableName(attributename attributetype)")

Python3

import cx_Oracle

try:
    con = cx_Oracle.connect('hardik/password@localhost:9501/xe')
    print("Connected")
    cursor = con.cursor()
    cursor.execute(
        "CREATE TABLE GeeksForGeeks(username varchar(10),\
                name varchar(30), age integer)")
    con.commit()
    print("Table Created Successfully!")
    cursor.close()
    con.close()
except Exception as e:
    print("Error: ", str(e))

Output:

Table Created Successfully!

Inserting a Record into Table

To insert records in the table we use the DML command INSERT to insert records in the table. Let’s Insert some data in our table:

Syntax: cursor.execute("INSERT INTO TableName Values('value1', 'value2', 'value3')")

Python3

import cx_Oracle

try:
    con = cx_Oracle.connect('hardik/password@localhost:9501/xe')
    print("Connected")
    cursor = con.cursor()
    cursor.execute(
        "INSERT INTO GeeksForGeeks VALUES
              ('hardik108', 'Hardik Kushwaha', 20)")
    cursor.execute("INSERT INTO GeeksForGeeks VALUES
                       ('harsh01', 'Harsh', 23)")
    cursor.execute(
        "INSERT INTO GeeksForGeeks VALUES
                          ('striver79', 'Striver', 21)")
    cursor.execute("INSERT INTO GeeksForGeeks VALUES
                               ('janki03', 'Janki', 22)")
    cursor.execute(
        "INSERT INTO GeeksForGeeks VALUES
                              ('anurag21', 'Anurag', 25)")
    con.commit()
    print("Records Inserted Successfully!")
    cursor.close()
    con.close()
except Exception as e:
    print("Error: ", str(e))

Output: 

Records Inserted Successfully!

Update Operation in Table

To update any existing record on the table we perform an update operation by using Data Manipulation Language(DML) command UPDATE to any existing record of the table. To update a particular record we have to specify the existing attribute value in the WHERE clause and then set a new value by using SET. To understand better let’s perform the update operation on an existing record of our table.

Syntax: cursor.execute("UPDATE TableName SET attribute='new_value' WHERE attribute='value'")

Python3

import cx_Oracle

try:
    con = cx_Oracle.connect('hardik/password@localhost:9501/xe')
    print("Connected")
    cursor = con.cursor()
    cursor.execute(
        "UPDATE GeeksForGeeks SET age=21 WHERE username='hardik108'")
    con.commit()
    print("Records Updated Successfully!")
    cursor.close()
    con.close()
except Exception as e:
    print("Error: ", str(e))

Output:

Records Updated Successfully!

Read Operation 

Read is the basic operation to fetch the records from the database, we use SELECT command for that purpose.

Syntax: cursor.execute("SELECT attribute1,attribute2, .... FROM TableName")

Python3

import cx_Oracle

try:
    con = cx_Oracle.connect('hardik/password@localhost:9501/xe')
    print("Connected")
    cursor = con.cursor()
    cursor.execute("SELECT * FROM GeeksForGeeks")
    res = cursor.fetchall()
    print("Table data:")
    col_names = [row[0] for row in cursor.description]
    for i in col_names:
        print(i, end='\t')
    print()
    for username, name, age in res:
        print(username, '\t', age, '\t', name)
    con.commit()
    cursor.close()
    con.close()
except Exception as e:
    print("Error: ", str(e))

Output:

Delete Operation

To delete a record from the database we have to use DELETE command which is a DML command, to delete a particular record we have to specify the attribute value in the WHERE clause.

Syntax: cursor.execute("DELETE FROM TableName WHERE attribute='value' ")

Python3

import cx_Oracle

try:
    con = cx_Oracle.connect('hardik/password@localhost:9501/xe')
    print("Connected")
    cursor = con.cursor()
    cursor.execute("DELETE FROM GeeksForGeeks WHERE username='anurag21'")
    con.commit()
    print('Record Deleted successfully!')
    cursor.close()
    con.close()
except Exception as e:
    print("Error: ", str(e))

Output:

Record Deleted successfully!

Drop Table

To delete the whole table we use the DROP command objects deleted using the DROP command are lost permanently and cannot be rolled back.

Syntax: cursor.execute("DROP TABLE TableName")

Python3

import cx_Oracle

try:
    con = cx_Oracle.connect('hardik/password@localhost:9501/xe')
    print("Connected")
    cursor = con.cursor()
    cursor.execute("DROP TABLE GeeksForGeeks")
    con.commit()
    print("Table Deleted")
except Exception as e:
    print("Error: ", str(e))
finally:
    if cursor:
        cursor.close()
    if con:
        con.close()

Output: