Python Oracle Database CRUD Operations: A Comprehensive Guide

Posted on by R SOTH
Estimated read time 3 min read

Introduction

Performing CRUD operations (Create, Read, Update, Delete) in an Oracle database using Python is an essential skill for developing enterprise-level applications. In this guide, we’ll walk through each CRUD operation step by step, using the cx_Oracle library for database interactions.

1. Setting Up Oracle Database:

  • Begin by setting up an Oracle database and creating a table for our CRUD operations. For this example, let’s create a simple employees table.
   CREATE TABLE employees (
       id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
       name VARCHAR2(255),
       position VARCHAR2(255)
   );

2. Installing the Oracle Connector:

  • Install the cx_Oracle library using pip.
   pip install cx_Oracle
  • Additionally, you’ll need to download and install the Oracle Instant Client corresponding to your operating system. Follow the instructions provided by Oracle for installation.

3. Connecting to the Oracle Database:

  • Create a Python script to connect to your Oracle database.
   import cx_Oracle

   # Replace 'your_username', 'your_password', 'your_host', and 'your_port' with your actual database credentials
   conn = cx_Oracle.connect(
       user='your_username',
       password='your_password',
       dsn=f'{your_host}:{your_port}/your_service_name'
   )

   cursor = conn.cursor()

4. Create Operation:

  • Write a function to add a new employee to the employees table.
   def create_employee(name, position):
       query = "INSERT INTO employees (name, position) VALUES (:1, :2)"
       values = (name, position)

       cursor.execute(query, values)
       conn.commit()

5. Read Operation:

  • Retrieve employees from the employees table.
   def read_employees():
       query = "SELECT * FROM employees"

       cursor.execute(query)
       result = cursor.fetchall()

       for employee in result:
           print(employee)

6. Update Operation:

  • Update the position of a specific employee.
   def update_employee_position(employee_id, new_position):
       query = "UPDATE employees SET position = :1 WHERE id = :2"
       values = (new_position, employee_id)

       cursor.execute(query, values)
       conn.commit()

7. Delete Operation:

  • Delete an employee from the employees table.
   def delete_employee(employee_id):
       query = "DELETE FROM employees WHERE id = :1"
       values = (employee_id,)

       cursor.execute(query, values)
       conn.commit()

8. Using the CRUD Operations:

  • Utilize these functions to perform CRUD operations.
   # Create a new employee
   create_employee('John Doe', 'Software Engineer')

   # Read all employees
   read_employees()

   # Update the position of employee with ID 1
   update_employee_position(1, 'Senior Software Engineer')

   # Read all employees after the update
   read_employees()

   # Delete employee with ID 2
   delete_employee(2)

   # Read all employees after the deletion
   read_employees()

9. Closing the Database Connection:

  • After performing CRUD operations, it’s crucial to close the database connection.
   cursor.close()
   conn.close()

Conclusion:

  • This guide provides a comprehensive foundation for implementing CRUD operations in an Oracle database using Python. Adjust the functions as needed for your specific use case. Additionally, consider using an ORM (Object-Relational Mapping) library like SQLAlchemy for more complex applications.
Post Views: 177

Related Articles