oracledb
python-oracledb is Oracle's Python driver for Oracle Database and the successor to cx_Oracle. It implements Python DB API 2.0 plus Oracle-specific features such as connection pools, PL/SQL calls, array binding, Advanced Queuing, database notifications, data-frame interchange, and asynchronous connections. Default Thin mode speaks the database protocol directly with no Oracle Client install. Optional Thick mode loads Oracle Client libraries for older databases and features that Thin mode does not provide.
Use python-oracledb for direct Oracle access; it is the current official driver and Thin mode removes the old mandatory client install. The package is not a database abstraction, and Thick mode, wallets, pooling, and transaction discipline still demand real operational work.
Use it if
- Your Python application talks directly to Oracle Database and needs an Oracle-maintained DB API driver
- You want deployment without Oracle Client libraries and your database is Oracle 12.1 or newer
- You need Oracle-specific features such as PL/SQL types, array DML, Advanced Queuing, session pools, notifications, or Application Continuity
- You need native async connections and pools rather than running blocking database calls in a thread executor
- You are free to choose the database and do not need Oracle features: the driver cannot reduce Oracle Database licensing, administration, SQL dialect, or operational complexity
- You need one portable database layer: DB API 2.0 basics travel, but PL/SQL, Oracle object types, array DML, AQ, DRCP, wallets, and many error codes do not
- You must connect to Oracle Database 11.2 without installing client libraries: Thin mode requires Database 12.1 or later, while older server support requires Thick mode and a compatible Oracle Client
- You expect async support in Thick mode: the documentation restricts asynchronous connections, pools, and pipelining to Thin mode
- You want an ORM or query builder instead of SQL and cursor management: python-oracledb is a driver, so use SQLAlchemy on top and keep this package as the Oracle dialect's DB API dependency
Setup reality
pip install oracledb installs cryptography and typing_extensions and requires Python 3.9 or newer in package metadata; the project README advertises current support from Python 3.10 through 3.15. Most users should start in Thin mode, which needs no Oracle Client but does require Oracle Database 12.1 or later. A DSN is not a generic URL: the common Easy Connect form is host:port/service_name, and SID, service name, TNS alias, RAC, proxy user, and wallet setups each have distinct rules. Thick mode must be enabled with oracledb.init_oracle_client() before the first connection or pool. It needs Oracle Client 19 or later plus operating-system library search paths, and a 64-bit Python requires matching 64-bit client libraries. Containers must include those native files and their system dependencies. A failed client load often appears only at process startup on a clean host, so test the actual deployment image. Autonomous Database and mTLS setups add a wallet directory, tnsnames.ora, sqlnet.ora, certificate files, and secrets; keep them out of the image and source tree. Thin mode can use wallet_location and wallet_password for supported wallets, while configuration-directory handling differs from Thick mode. Connections do not autocommit by default. Closing a connection without commit rolls back pending work, and returning a pooled connection with unfinished transaction state is a production bug. Bind variables are required for values, but they cannot replace table or column names. Pool sizing, getmode, ping interval, statement cache, call timeout, session tagging, and retry behavior need deliberate values under real concurrency. Async code must use connect_async or create_pool_async and remains Thin-only. Do not create a new connection per web request when a process-local pool can amortize authentication and session creation.
Patterns
Connect in default Thin modeconnect-thin
import os
import oracledb
with oracledb.connect(
user=os.environ['DB_USER'],
password=os.environ['DB_PASSWORD'],
dsn='db.example.com:1521/appsvc',
) as connection:
print(connection.version)Thin mode is the default and needs no Oracle Client, but the database must be 12.1 or newer.
Enable Thick mode before connectingenable-thick-mode
import oracledb
oracledb.init_oracle_client(lib_dir='/opt/oracle/instantclient_23_8')
connection = oracledb.connect(
user='app',
password='secret',
dsn='db.example.com/appsvc',
)Call init_oracle_client before any connection or pool is created. Python and Oracle Client architectures must match.
Execute a query with named bind variablesquery-with-binds
sql = '''
select employee_id, first_name, salary
from employees
where department_id = :department_id
order by employee_id
'''
with connection.cursor() as cursor:
cursor.execute(sql, department_id=50)
for employee_id, name, salary in cursor:
print(employee_id, name, salary)Bind values instead of formatting them into SQL. Bind variables cannot stand in for table or column identifiers.
Return query rows as dictionariesfetch-dictionaries
def dict_row(cursor):
columns = [item[0].lower() for item in cursor.description]
cursor.rowfactory = lambda *values: dict(zip(columns, values))
with connection.cursor() as cursor:
cursor.execute('select employee_id, first_name from employees')
dict_row(cursor)
rows = cursor.fetchall()Set rowfactory after execute because cursor.description is populated by the executed query.
Insert a row and commit explicitlyinsert-and-commit
with connection.cursor() as cursor:
cursor.execute(
'insert into audit_log (event_id, message) values (:1, :2)',
[event_id, message],
)
connection.commit()Autocommit is off by default. Closing without commit rolls back uncommitted work.
Insert many rows with executemanybulk-insert
rows = [(101, 'queued'), (102, 'queued'), (103, 'queued')]
with connection.cursor() as cursor:
cursor.setinputsizes(int, 20)
cursor.executemany(
'insert into jobs (job_id, status) values (:1, :2)',
rows,
)
connection.commit()setinputsizes can reduce reallocations for large batches. Use batcherrors only when partial success is an intentional workflow.
Create and use a fixed-size connection poolconnection-pool
pool = oracledb.create_pool(
user=os.environ['DB_USER'],
password=os.environ['DB_PASSWORD'],
dsn=os.environ['DB_DSN'],
min=4,
max=4,
increment=0,
)
with pool.acquire() as connection:
with connection.cursor() as cursor:
value, = cursor.execute('select 1 from dual').fetchone()Size the pool against database session limits and process count, not only request concurrency in one worker.
Run a query with an async connectionasync-query
import oracledb
async def load_employee(employee_id):
connection = await oracledb.connect_async(
user='app', password='secret', dsn='db.example.com/appsvc'
)
async with connection:
with connection.cursor() as cursor:
await cursor.execute(
'select first_name from employees where employee_id = :1',
[employee_id],
)
return await cursor.fetchone()Async connections are supported only in Thin mode. Cursor creation itself is local, so it does not need await.
Acquire from an async connection poolasync-pool
pool = oracledb.create_pool_async(
user='app', password='secret', dsn='db.example.com/appsvc',
min=2, max=10, increment=1,
)
async with pool.acquire() as connection:
with connection.cursor() as cursor:
await cursor.execute('select systimestamp from dual')
row = await cursor.fetchone()create_pool_async returns a pool directly; network work happens when connections are established or acquired.
Call a PL/SQL procedure with an output valuecall-plsql-procedure
with connection.cursor() as cursor:
total = cursor.var(oracledb.NUMBER)
cursor.callproc('billing.calculate_total', [invoice_id, total])
print(total.getvalue())Create output variables with cursor.var and read them after the call; Python return values are not inferred from PL/SQL modes.
Read a CLOB valueread-clob
with connection.cursor() as cursor:
cursor.execute('select document_body from documents where document_id = :1', [doc_id])
lob, = cursor.fetchone()
text = lob.read() if lob is not None else NoneLOB objects depend on an open connection. Read or stream them before the connection is released back to a pool.
Inspect an Oracle database errorhandle-database-errors
try:
cursor.execute(sql, values)
except oracledb.DatabaseError as exc:
error, = exc.args
print(error.full_code, error.message)
if error.code == 1:
raise ValueError('duplicate value') from exc
raiseMatch documented Oracle error codes, not message text, because messages can vary by database version and language.
Alternatives
| Package | Registry | Pick it when |
|---|---|---|
| SQLAlchemy | PyPI | You want an ORM or composable SQL layer and accept that an Oracle DB API driver is still required underneath |
| pyodbc | PyPI | Your organization standardizes on ODBC across databases and can install and configure an Oracle ODBC driver |
| JayDeBeApi | PyPI | You must use an existing Oracle JDBC setup from Python and can accept a JVM bridge in the process |