Of course! This is a very common and important task for any Python developer working with Oracle databases. Here’s a comprehensive guide on how to connect to an Oracle database from Python using the cx_Oracle library, which is the de-facto standard.

We'll cover:

1. Prerequisites: What you need before you start.
2. Installation: How to install the necessary libraries.
3. Connection: How to establish a connection to the database.
4. Executing Queries: How to run SELECT statements and fetch data.
5. Data Manipulation: How to run INSERT, UPDATE, and DELETE statements.
6. Best Practices: Using connection pools and context managers for robust applications.
7. Full Example: A complete, runnable script.

Prerequisites

Before you write any Python code, you need to ensure your environment is set up correctly.

a) Oracle Client Software

Python's cx_Oracle library is just an interface. It needs the actual Oracle client libraries to communicate with the database. You have two main options:

Option 1: Install the Oracle Instant Client (Recommended) This is the easiest and most common method. It's a lightweight, standalone set of libraries that don't require a full Oracle Database installation.

1. Download: Go to the Oracle Instant Client Downloads page.

2. Select Version: Choose the version that matches your Oracle Database server version (or a recent one, as they are often backward-compatible).

3. Select Platform: Download the appropriate package for your operating system (e.g., x64 for 64-bit Windows, macOS x64 or arm64 for macOS).

4. Unzip: Unzip the downloaded file to a permanent location on your computer (e.g., C:\oracle\instantclient_19_10 on Windows or /opt/oracle/instantclient_19_10 on Linux/macOS).

   
   （图片来源网络，侵删）

5. Set Environment Variable: You must add the path to the unzipped folder to your system's PATH environment variable. This allows Python to find the necessary DLLs (on Windows) or shared libraries (on Linux/macOS).

   • Windows: Search for "Environment Variables", edit the "Path" variable under "System variables", and add the path to your Instant Client folder.
   • Linux/macOS: Add the following line to your ~/.bashrc or ~/.zshrc file and then run source ~/.bashrc or source ~/.zshrc:

     export LD_LIBRARY_PATH=/path/to/your/instantclient_19_10:$LD_LIBRARY_PATH

Option 2: Use a Full Oracle Database Client If you already have a full Oracle client (like SQL*Plus or Oracle Developer Tools installed), you likely have the necessary libraries. Just ensure the PATH is set correctly to find them.

b) Connection Details

You need the following information from your database administrator (DBA):

• Username: Your database username.
• Password: Your database password.
• DSN (Data Source Name): This is the address of the database. It has the format: hostname:port/service_name.
  • hostname: The server's IP address or hostname.
  • port: The port the database is listening on (default is 1521).
  • service_name: The service name of the Oracle database instance.

Example DSN: mydbhost.example.com:1521/ORCLCDB

Installation

You only need to install the cx_Oracle library itself using pip.

pip install cx_Oracle

Establishing a Connection

The core of connecting is to import the library and use the cx_Oracle.connect() function with your credentials.

import cx_Oracle
# --- Connection Details ---
# Replace with your actual database details
username = "your_username"
password = "your_password"
dsn = "your_host:your_port/your_service_name"
# --- Establish Connection ---
try:
    # Create a connection object
    connection = cx_Oracle.connect(user=username, password=password, dsn=dsn)
    print("Successfully connected to Oracle Database!")
    print(f"Database version: {connection.version}")
except cx_Oracle.DatabaseError as e:
    # Handle connection errors
    error, = e.args
    print(f"Oracle Error {error.code}: {error.message}")
    # In a real application, you might want to re-raise or handle this more gracefully
    raise

Executing Queries (SELECT)

To run a query and get the results, you use a cursor object.

import cx_Oracle
# Assume 'connection' is already established from the previous step
try:
    # 1. Create a cursor
    # A cursor is a control structure that enables traversal over the records in a database
    cursor = connection.cursor()
    # 2. Define the SQL query
    sql_query = "SELECT employee_id, first_name, last_name FROM employees WHERE rownum < 6"
    # 3. Execute the query
    cursor.execute(sql_query)
    # 4. Fetch the results
    # fetchone() gets the next row of a query result set
    print("\n--- Fetching one row at a time ---")
    while True:
        row = cursor.fetchone()
        if not row:
            break
        print(f"ID: {row[0]}, Name: {row[1]} {row[2]}")
    # Alternatively, fetch all rows at once (be careful with large result sets)
    # cursor.execute(sql_query)
    # all_rows = cursor.fetchall()
    # print(f"\n--- Fetched all rows ({len(all_rows)}) ---")
    # for row in all_rows:
    #     print(f"ID: {row[0]}, Name: {row[1]} {row[2]}")
except cx_Oracle.DatabaseError as e:
    error, = e.args
    print(f"Oracle Error {error.code}: {error.message}")
finally:
    # 5. Close the cursor
    if 'cursor' in locals() and cursor:
        cursor.close()

Data Manipulation (INSERT, UPDATE, DELETE)

For DML (Data Manipulation Language) statements, you need to commit the transaction to make the changes permanent in the database. It's crucial to handle potential errors and use try...except...finally blocks to ensure resources are closed.

import cx_Oracle
# Assume 'connection' is already established
try:
    cursor = connection.cursor()
    # --- Example: INSERT ---
    print("\n--- Inserting a new employee ---")
    insert_sql = "INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id) VALUES (employee_id_seq.NEXTVAL, 'John', 'Doe', 'john.doe@example.com', SYSDATE, 'IT_PROG')"
    cursor.execute(insert_sql)
    connection.commit() # Commit the transaction
    print(f"1 row inserted.")
    # --- Example: UPDATE ---
    print("\n--- Updating an employee's last name ---")
    # Let's update the employee we just inserted. We assume his ID is the highest one.
    # Note: Using a sequence (employee_id_seq.NEXTVAL) is safer.
    update_sql = "UPDATE employees SET last_name = 'Smith' WHERE first_name = 'John'"
    cursor.execute(update_sql)
    connection.commit()
    print(f"{cursor.rowcount} row(s) updated.")
    # --- Example: DELETE ---
    print("\n--- Deleting the employee ---")
    delete_sql = "DELETE FROM employees WHERE first_name = 'John'"
    cursor.execute(delete_sql)
    connection.commit()
    print(f"{cursor.rowcount} row(s) deleted.")
except cx_Oracle.DatabaseError as e:
    error, = e.args
    print(f"Oracle Error {error.code}: {error.message}")
    # It's good practice to rollback on error
    if connection:
        connection.rollback()
    raise
finally:
    if 'cursor' in locals() and cursor:
        cursor.close()

Best Practices

a) Using Context Managers (with statement)

The with statement ensures that your cursor is automatically closed, even if errors occur. This is highly recommended.

with connection.cursor() as cursor:
    cursor.execute("SELECT * FROM my_table WHERE id = :id", id=101)
    row = cursor.fetchone()
    if row:
        print(f"Found: {row}")
# The cursor is automatically closed here

b) Using Connection Pools

For applications that connect to the database frequently (like web servers), creating a new connection for every request is inefficient. Connection pools maintain a cache of open connections for reuse.

import cx_Oracle
# Create a pool
pool = cx_Oracle.SessionPool(
    user="your_username",
    password="your_password",
    dsn="your_dsn",
    min=1,      # Minimum number of connections in the pool
    max=2,      # Maximum number of connections in the pool
    increment=1 # Number of connections to create when a request is made and the pool is empty
)
try:
    # Get a connection from the pool
    with pool.acquire() as connection:
        with connection.cursor() as cursor:
            cursor.execute("SELECT SYSDATE FROM DUAL")
            print(f"Connection from pool. Current time: {cursor.fetchone()[0]}")
finally:
    # Close the pool when your application shuts down
    pool.close()

c) Binding Variables (Preventing SQL Injection)

Never format user input directly into a string query. This is a major security risk (SQL Injection). Always use bind variables.

Bad (Vulnerable):

user_id = input("Enter user ID: ")
sql = f"SELECT * FROM users WHERE id = {user_id}" # DANGEROUS!
cursor.execute(sql)

Good (Secure):

user_id = input("Enter user ID: ")
sql = "SELECT * FROM users WHERE id = :id" # Use a placeholder
cursor.execute(sql, id=user_id) # Pass the value as a parameter

Full Example (Putting It All Together)

Here is a complete, well-structured script that demonstrates connecting, querying, inserting, and using best practices.

import cx_Oracle
import getpass # To securely input the password
def main():
    # --- 1. Get Credentials ---
    username = input("Enter your Oracle username: ")
    password = getpass.getpass("Enter your password: ")
    dsn = input("Enter your DSN (host:port/service_name): ")
    connection = None
    try:
        # --- 2. Establish Connection ---
        print("Connecting to the database...")
        connection = cx_Oracle.connect(user=username, password=password, dsn=dsn)
        print("Connection successful!")
        # --- 3. Query Data with Context Manager ---
        print("\n--- Querying Employees ---")
        with connection.cursor() as cursor:
            sql = "SELECT employee_id, first_name, last_name FROM employees WHERE department_id = :dept_id"
            department_id = 60  # Example: IT department
            cursor.execute(sql, dept_id=department_id)
            print(f"Employees in department {department_id}:")
            for row in cursor:
                print(f"  ID: {row[0]}, Name: {row[1]} {row[2]}")
        # --- 4. Insert Data with Transaction Control ---
        print("\n--- Inserting a New Employee ---")
        new_employee = {
            'first_name': 'Jane',
            'last_name': 'Williams',
            'email': 'jane.williams@example.com',
            'phone_number': '555.123.4567',
            'hire_date': '15-NOV-2025',
            'job_id': 'SA_MAN',
            'salary': 8000,
            'commission_pct': 0.15,
            'manager_id': 145,
            'department_id': 80
        }
        insert_sql = """
            INSERT INTO employees (
                employee_id, first_name, last_name, email, phone_number, hire_date,
                job_id, salary, commission_pct, manager_id, department_id
            ) VALUES (
                employee_id_seq.NEXTVAL, :first_name, :last_name, :email, :phone_number,
                TO_DATE(:hire_date, 'DD-MON-YYYY'), :job_id, :salary, :commission_pct,
                :manager_id, :department_id
            )
        """
        with connection.cursor() as cursor:
            cursor.execute(insert_sql, **new_employee)
            connection.commit() # Commit the transaction
            print(f"Successfully inserted employee: {new_employee['first_name']} {new_employee['last_name']}")
            print(f"New Employee ID: {cursor.lastrowid}") # Note: lastrowid is not standard for Oracle, this is illustrative
    except cx_Oracle.DatabaseError as e:
        error, = e.args
        print(f"Database Error {error.code}: {error.message}")
        if connection:
            print("Rolling back transaction...")
            connection.rollback()
    except Exception as e:
        print(f"An unexpected error occurred: {e}")
    finally:
        # --- 5. Close Connection ---
        if connection:
            connection.close()
            print("\nDatabase connection closed.")
if __name__ == "__main__":
    main()