Of course! Inserting data into an Oracle database using Python is a common task. The most reliable and recommended way to do this is by using the cx_Oracle driver.

Here’s a comprehensive guide covering:

1. Prerequisites: What you need to install.
2. Connecting: How to establish a connection to your Oracle database.
3. Basic Insert: The simplest way to insert a single row.
4. Best Practice: Using Bind Variables: The secure and efficient way to insert data, preventing SQL injection.
5. Inserting Multiple Rows: How to efficiently insert many rows at once.
6. Handling Transactions: Committing and rolling back changes.
7. Complete Example: A full, runnable script.

Prerequisites

Before you start, make sure you have the following:

• Python Installed: (e.g., Python 3.7+).
• Oracle Client: The Oracle database client libraries must be installed on your machine. cx_Oracle uses these to communicate with the database.
  • Easy Install (Recommended): If you have an Oracle database, the easiest way is often to install the Oracle Instant Client. Download the appropriate version for your operating system and add the unzipped directory to your system's PATH environment variable.
  • Full Client: You can also install a full Oracle Client or Oracle Database, which includes the necessary libraries.
• cx_Oracle Driver: Install the Python library using pip.

  pip install cx_Oracle

Connecting to the Oracle Database

First, you need to create a connection object. This requires your database credentials.

import cx_Oracle
# --- Database Connection Details ---
# Replace with your actual database credentials
db_user = "your_username"
db_password = "your_password"
db_dsn = "localhost:1521/XEPDB1" # Format: host:port/service_name
try:
    # Create a connection to the Oracle database
    connection = cx_Oracle.connect(user=db_user, password=db_password, dsn=db_dsn)
    print("Successfully connected to Oracle Database!")
    # You can now create a cursor to execute SQL statements
    cursor = connection.cursor()
    # ... your SQL code will go here ...
except cx_Oracle.DatabaseError as e:
    error, = e.args
    print(f"Oracle Error {error.code}: {error.message}")
finally:
    # Ensure the connection is closed
    if 'connection' in locals() and connection:
        connection.close()
        print("Database connection closed.")

Basic Insert (The Hardcoded Way)

For simple, one-off scripts, you can write the values directly into your SQL string. Warning: This is not recommended for applications that accept user input, as it is vulnerable to SQL injection.

# Assume 'connection' and 'cursor' are already established from the previous step
# SQL statement with hardcoded values
sql_insert_query = """
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
VALUES (1, 'John', 'Doe', 'john.doe@example.com', TO_DATE('2025-01-15', 'YYYY-MM-DD'), 'IT_PROG')
"""
try:
    # Execute the command
    cursor.execute(sql_insert_query)
    print("Row inserted successfully.")
    # Commit the transaction to make the change permanent
    connection.commit()
except cx_Oracle.DatabaseError as e:
    # Rollback in case of error
    connection.rollback()
    error, = e.args
    print(f"Error inserting row: {error.message}")

Best Practice: Using Bind Variables (The Secure Way)

To prevent SQL injection and to make your code more efficient, you should always use bind variables. You use placeholders (like 1, 2, or named placeholders like first_name) in your SQL statement and pass the values as a separate tuple or dictionary to the cursor.execute() method.

This is the standard and recommended approach.

Example with Positional Bind Variables (1, 2)

# Assume 'connection' and 'cursor' are already established
# SQL statement with placeholders
sql_insert_query = """
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
VALUES (:1, :2, :3, :4, TO_DATE(:5, 'YYYY-MM-DD'), :6)
"""
# The values corresponding to the placeholders
new_employee = (
    2,                   # :1
    "Jane",              # :2
    "Smith",             # :3
    "jane.smith@example.com", # :4
    "2025-02-20",        # :5
    "SA_MAN"             # :6
)
try:
    # Execute the command by passing the values as a tuple
    cursor.execute(sql_insert_query, new_employee)
    print("Row inserted successfully using bind variables.")
    # Commit the transaction
    connection.commit()
except cx_Oracle.DatabaseError as e:
    connection.rollback()
    error, = e.args
    print(f"Error inserting row: {error.message}")

Example with Named Bind Variables (key)

Named bind variables can make your code even more readable, especially with many columns.

# Assume 'connection' and 'cursor' are already established
# SQL statement with named placeholders
sql_insert_query = """
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
VALUES (:emp_id, :f_name, :l_name, :email, TO_DATE(:h_date, 'YYYY-MM-DD'), :job)
"""
# The values in a dictionary corresponding to the named placeholders
new_employee_dict = {
    'emp_id': 3,
    'f_name': 'Peter',
    'l_name': 'Jones',
    'email': 'peter.jones@example.com',
    'h_date': '2025-03-10',
    'job': 'ST_CLERK'
}
try:
    # Execute the command by passing the dictionary
    cursor.execute(sql_insert_query, new_employee_dict)
    print("Row inserted successfully using named bind variables.")
    # Commit the transaction
    connection.commit()
except cx_Oracle.DatabaseError as e:
    connection.rollback()
    error, = e.args
    print(f"Error inserting row: {error.message}")

Inserting Multiple Rows Efficiently

Inserting rows one by one in a loop is very slow. cx_Oracle provides a highly optimized method called executemany() to insert a large number of rows in a single database round-trip.

The SQL statement uses a single set of placeholders, and you provide a list of tuples, where each tuple represents a row to be inserted.

# Assume 'connection' and 'cursor' are already established
# SQL statement with placeholders (same as before)
sql_insert_query = """
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id)
VALUES (:1, :2, :3, :4, TO_DATE(:5, 'YYYY-MM-DD'), :6)
"""
# A list of tuples, where each tuple is a new employee
new_employees = [
    (4, 'Susan', 'Williams', 'susan.w@example.com', '2025-04-05', 'AC_ACCOUNT'),
    (5, 'David', 'Brown', 'david.b@example.com', '2025-05-12', 'MK_REP'),
    (6, 'Laura', 'Davis', 'laura.d@example.com', '2025-06-18', 'HR_REP')
]
try:
    # executemany() is much faster than looping and calling execute()
    cursor.executemany(sql_insert_query, new_employees)
    print(f"{cursor.rowcount} rows inserted successfully using executemany().")
    # Commit the transaction
    connection.commit()
except cx_Oracle.DatabaseError as e:
    connection.rollback()
    error, = e.args
    print(f"Error inserting rows: {error.message}")

Handling Transactions

When you execute a DML (Data Manipulation Language) command like INSERT, UPDATE, or DELETE, the changes are only temporary and stored in a "transaction" on the server.

• connection.commit(): This makes all changes in the current transaction permanent. It's essential to call this after your inserts if you want the data to be saved.
• connection.rollback(): This undoes all changes made in the current transaction. This is typically used in a try...except block to revert the database to its state before the error occurred.

Golden Rule: Always ensure you either commit or rollback your transactions to avoid leaving the database in an inconsistent state.

Complete, Runnable Example

This script combines all the concepts: connection, creating a table, inserting data using the best practices, and handling transactions.

import cx_Oracle
import sys
# --- Configuration ---
DB_USER = "your_username"
DB_PASSWORD = "your_password"
DB_DSN = "localhost:1521/XEPDB1"
def main():
    connection = None
    cursor = None
    try:
        # 1. Establish connection
        connection = cx_Oracle.connect(user=DB_USER, password=DB_PASSWORD, dsn=DB_DSN)
        print("Database connection successful.")
        cursor = connection.cursor()
        # 2. Create a sample table (if it doesn't exist)
        print("\nCreating table 'my_employees'...")
        cursor.execute("""
            BEGIN
                EXECUTE IMMEDIATE 'DROP TABLE my_employees';
            EXCEPTION
                WHEN OTHERS THEN NULL;
            END;
        """)
        cursor.execute("""
            CREATE TABLE my_employees (
                id          NUMBER PRIMARY KEY,
                name        VARCHAR2(100),
                department  VARCHAR2(50),
                salary      NUMBER(10, 2)
            )
        """)
        connection.commit()
        print("Table 'my_employees' created.")
        # 3. Insert a single row using named bind variables
        print("\nInserting a single row...")
        sql_single_insert = """
            INSERT INTO my_employees (id, name, department, salary)
            VALUES (:id, :name, :dept, :sal)
        """
        employee_data = {'id': 101, 'name': 'Alice', 'dept': 'Engineering', 'sal': 95000.00}
        cursor.execute(sql_single_insert, employee_data)
        connection.commit()
        print("Single row inserted.")
        # 4. Insert multiple rows using executemany()
        print("\nInserting multiple rows...")
        sql_multi_insert = """
            INSERT INTO my_employees (id, name, department, salary)
            VALUES (:1, :2, :3, :4)
        """
        new_employees = [
            (102, 'Bob', 'Sales', 80000.00),
            (103, 'Charlie', 'HR', 72000.50),
            (104, 'Diana', 'Marketing', 88000.00)
        ]
        cursor.executemany(sql_multi_insert, new_employees)
        connection.commit()
        print(f"{cursor.rowcount} rows inserted.")
        # 5. Verify the inserted data
        print("\nVerifying inserted data...")
        cursor.execute("SELECT id, name, department, salary FROM my_employees ORDER BY id")
        for row in cursor:
            print(f"  ID: {row[0]}, Name: {row[1]}, Dept: {row[2]}, Salary: {row[3]}")
    except cx_Oracle.DatabaseError as e:
        error, = e.args
        print(f"Oracle Error {error.code}: {error.message}", file=sys.stderr)
        if connection:
            print("Rolling back changes...", file=sys.stderr)
            connection.rollback()
    finally:
        # 6. Clean up
        if cursor:
            cursor.close()
        if connection:
            connection.close()
            print("\nDatabase connection closed.")
if __name__ == "__main__":
    main()