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-
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
close(): Close function is used to close the connection to the database so it cannot be used further
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)")
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')")
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'")
Records Updated Successfully!
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")
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' ")
Record Deleted successfully!
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")
Related Articles: Oracle Database Connection in Python
Please Login to comment...