CRUD Operation on Oracle Database Using Python
Last Updated :
24 Apr, 2025
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:
Related Articles: Oracle Database Connection in Python
Similar Reads
CRUD Operations on Postgres using Async Database In Python
CRUD stands for Create, Read, Update and Delete Operations. All these Operations can be made Asynchronous using the Async Database Connection. After making  Async Connection to Postgres Database, the performance of the Application improves significantly as all the operations are performed Concurrent
3 min read
CRUD Operation in Python using MySQL
In this article, we will be seeing how to perform CRUD (CREATE, READ, UPDATE and DELETE) operations in Python using MySQL. For this, we will be using the Python MySQL connector. For MySQL, we have used Visual Studio Code for python. Before beginning we need to install the MySQL connector with the co
6 min read
How to Create User in Oracle Database ?
In Oracle databases, creating and managing users is a critical task for database administrators to control access and maintain security. The CREATE USER command is used to add new users to the database, assigning them specific roles, privileges, and passwords. Additionally, users can be created and
3 min read
Create Database in MariaDB using PyMySQL in Python
MariaDB is an open source Database Management System and its predecessor to MySQL. The pymysql client can be used to interact with MariaDB similar to that of MySQL using Python. In this article we will look into the process of creating a database using pymysql. To create a database use the below syn
2 min read
How to Import Data into Oracle Database ?
An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. Oracle database contains data in the form of tables in the form of rows and columns. In this article, we will see how to import data into the Oracle database. Here is
2 min read
Interface Python with an SQL Database
Python is an easy-to-learn language and connectivity of python with any SQL database is a much-desired option to have the persistence feature. Python is an object-oriented programming language and it is open source. Newcomers to the software industry including school children too can learn Python ea
8 min read
How to Show all Columns in the SQLite Database using Python ?
In this article, we will discuss how we can show all columns of a table in the SQLite database from Python using the sqlite3 module. Approach:Connect to a database using the connect() method.Create a cursor object and use that cursor object created to execute queries in order to create a table and
3 min read
How to import CSV file in SQLite database using Python ?
In this article, we'll learn how to import data from a CSV file and store it in a table in the SQLite database using Python. You can download the CSV file from here which contains sample data on the name and age of a few students. Contents of the CSV file Approach: Importing necessary modulesRead da
2 min read
How to Install Oracle Database 11g on Windows?
Oracle Database (known as Oracle RDBMS) is a Database Management System produced and marketed by Oracle Corporation. The Most Fundamental and common usage of Oracle Database is to store a Pre-Defined type of Data. It supports the Structured Query language (SQL) to Manage and Manipulate the Data that
4 min read
Connecting to SQL Database using SQLAlchemy in Python
In this article, we will see how to connect to an SQL database using SQLAlchemy in Python. To connect to a SQL database using SQLAlchemy we will require the sqlalchemy library installed in our python environment. It can be installed using pip - !pip install sqlalchemyThe create_engine() method of sq
3 min read