Query Oracle Database from Python

Oracle Database stores critical business data. Python is a great language for querying that data quickly and safely.

The official oracledb driver makes this possible. It supports Thin mode by default and works without Oracle Client libraries.

This guide shows you how to connect, run queries, bind variables, fetch rows, manage transactions, and handle errors.

Prerequisites and Driver Setup

You need Python 3.9 or newer and access to an Oracle Database. A valid username, password, and connection string are required.

Create a virtual environment before installing packages. This keeps database drivers separate from other projects.

# Create and activate a virtual environment
python -m venv oracle-query-env
source oracle-query-env/bin/activate

# On Windows
oracle-query-env\Scripts\activate

Install the driver with pip. If you are new to the driver, this guide on how to install oracledb in Python explains Thin and Thick modes in detail.

Thin mode needs no extra system libraries. That makes it the easiest starting point for query work.

Connect to Oracle Database

Every query needs a connection. The connect() method opens a session using your credentials and DSN.

The DSN follows the format hostname:port/service_name. For example, localhost:1521/orclpdb.

import oracledb

# Connect in the default Thin mode
connection = oracledb.connect(
    user="scott",
    password="tiger",
    dsn="localhost:1521/orclpdb",
)

print("Connected to Oracle Database.")
connection.close()

Connected to Oracle Database.

Always close connections when done. A context manager does this automatically and is the safest pattern.

import oracledb

with oracledb.connect(user="scott", password="tiger", dsn="localhost:1521/orclpdb") as connection:
    print("Connection closed automatically.")

Run a Basic SELECT Query

A cursor sends SQL to the database. The cursor() method creates one from an open connection.

The execute() method runs the statement. Then you can read the result rows.

import oracledb

with oracledb.connect(user="scott", password="tiger", dsn="localhost:1521/orclpdb") as conn:
    with conn.cursor() as cur:
        # Run a simple SELECT statement
        cur.execute("SELECT employee_id, first_name, last_name FROM employees")

for row in cur:
            print(row)

(100, 'Steven', 'King')
(101, 'Neena', 'Kochhar')
(102, 'Lex', 'De Haan')

Iterating over the cursor is memory efficient. The driver fetches rows in batches instead of loading everything at once.

Fetch Rows Safely

There are three common ways to fetch rows. Each has a clear use case.

The fetchone() method returns a single row. Use it when you expect exactly one result.

with oracledb.connect(user="scott", password="tiger", dsn="localhost:1521/orclpdb") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT employee_id, first_name FROM employees WHERE employee_id = :id", id=100)
        row = cur.fetchone()
        print(row)

(100, 'Steven')

The fetchmany() method returns a limited number of rows. Use it for paging through large result sets.

with oracledb.connect(user="scott", password="tiger", dsn="localhost:1521/orclpdb") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT employee_id, first_name FROM employees")
        rows = cur.fetchmany(3)
        print(rows)

[(100, 'Steven'), (101, 'Neena'), (102, 'Lex')]

The fetchall() method returns every remaining row. Use it only when the result set is small.

with oracledb.connect(user="scott", password="tiger", dsn="localhost:1521/orclpdb") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT department_id, department_name FROM departments")
        rows = cur.fetchall()
        print(len(rows), "rows fetched")

27 rows fetched

Use Bind Variables

Never build SQL with string concatenation. That pattern invites SQL injection and hurts performance.

Bind variables keep values separate from the SQL text. The driver sends them safely to the database.

import oracledb

with oracledb.connect(user="scott", password="tiger", dsn="localhost:1521/orclpdb") as conn:
    with conn.cursor() as cur:
        # Named bind variables prevent SQL injection
        cur.execute(
            "SELECT first_name, salary FROM employees WHERE department_id = :dept_id",
            dept_id=50,
        )
        for name, salary in cur:
            print(name, salary)

Matthew 8000
Adam 8200
Payam 7900

Bind variables also help Oracle reuse execution plans. This improves performance for repeated queries.

Use named binds with a dictionary or keyword arguments. Avoid positional binds when many columns are involved.

Commit and Roll Back

Data changes need a transaction boundary. The commit() method saves changes permanently.

Call it only after all statements in a logical unit succeed.

import oracledb

with oracledb.connect(user="scott", password="tiger", dsn="localhost:1521/orclpdb") as conn:
    with conn.cursor() as cur:
        cur.execute(
            "UPDATE employees SET salary = salary * 1.05 WHERE department_id = :dept_id",
            dept_id=50,
        )
        conn.commit()  # Save the changes
        print("Salary update committed.")

Salary update committed.

The rollback() method undoes uncommitted changes. Use it when a later step in a workflow fails.

import oracledb

with oracledb.connect(user="scott", password="tiger", dsn="localhost:1521/orclpdb") as conn:
    try:
        with conn.cursor() as cur:
            cur.execute("DELETE FROM employees WHERE employee_id = :id", id=999)
        conn.commit()
    except Exception:
        conn.rollback()  # Undo the delete on failure
        print("Transaction rolled back.")

Transaction rolled back.

Keep transactions short. Long open transactions block other sessions and increase undo usage.

Insert and Update Rows

The executemany() method runs one statement with many parameter sets. It is much faster than looping over single inserts.

import oracledb

rows = [
    (301, "Alice", "Engineering"),
    (302, "Bob", "Finance"),
    (303, "Cara", "Support"),
]

with oracledb.connect(user="scott", password="tiger", dsn="localhost:1521/orclpdb") as conn:
    with conn.cursor() as cur:
        cur.executemany(
            "INSERT INTO staff (staff_id, name, department) VALUES (:1, :2, :3)",
            rows,
        )
        conn.commit()
        print(f"{len(rows)} rows inserted.")

3 rows inserted.

Batch operations reduce network round trips. Use them for bulk loads and scheduled data sync jobs.

Use Connection Pooling

Opening a connection for every request is slow. The create_pool() method keeps connections ready for reuse.

Pools work well in web apps and background workers. They reduce latency and protect the database from connection storms.

import oracledb

# Create a pool with 2 open and up to 10 connections
pool = oracledb.create_pool(
    user="scott",
    password="tiger",
    dsn="localhost:1521/orclpdb",
    min=2,
    max=10,
    increment=1,
)

with pool.acquire() as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT COUNT(*) FROM employees")
        print(cur.fetchone()[0])

107

Close the pool during application shutdown. The close() method releases all pooled connections cleanly.

Handle Errors and Monitor Queries

Database calls can fail. Network issues, permissions, and invalid SQL are common causes.

Wrap query logic in a try block. Catch oracledb.Error and log a clear message.

import oracledb

try:
    with oracledb.connect(user="scott", password="tiger", dsn="localhost:1521/orclpdb") as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT * FROM missing_table")
except oracledb.Error as exc:
    print("Oracle error:", exc)

Oracle error: ORA-00942: table or view does not exist

In production, monitor these failures. You can install sentry-sdk in Python to capture query exceptions and alert your team before users report problems.

If your stack also uses MySQL, the same cursor patterns apply. You can learn to install mysqlclient in Python when you need a second driver.

Best Practices for Clean Queries

Use bind variables everywhere. This is the single most important habit for safe and fast queries.

Fetch only the columns you need. Avoid SELECT * in application code.

Set a reasonable fetch size for large result sets. The driver can tune this, but explicit values help with predictable memory use.

Keep transactions short. Commit or roll back as soon as the logical unit finishes.

Use connection pooling in web apps. It improves throughput and protects the database.

Pin the driver version in your dependency file. Reproducible deployments avoid surprise behavior changes.

Log query failures with context. Include the statement name, not the raw SQL with secrets.

Conclusion

Querying Oracle Database from Python is simple with the oracledb driver. Connect in Thin mode, create a cursor, and run your SQL.

Use bind variables to prevent injection. Fetch rows with the method that fits your result size.

Manage transactions with commit and rollback. Use pooling for high-traffic applications.

With these patterns, your Python code queries Oracle safely, efficiently, and reliably at any scale.