0
# Oracle Database Python Driver (oracledb)
1

2
A comprehensive Python interface to Oracle Database that conforms to the Python Database API 2.0 specification with extensive additions. Supports both thin mode (direct connection without client libraries) and thick mode (using Oracle Client libraries for advanced functionality). The library enables Python programs to execute SQL and PL/SQL statements, call NoSQL-style document APIs, work with data frames, receive database notifications and messages, and perform database administration tasks.
3

4
## Package Information
5

6
- **Package Name**: oracledb
7
- **Language**: Python  
8
- **Installation**: `pip install oracledb`
9
- **Documentation**: http://python-oracledb.readthedocs.io
10

11
## Core Imports
12

13
```python
14
import oracledb
15
```
16

17
For specific functionality:
18

19
```python
20
from oracledb import connect, create_pool, Connection, Cursor
21
```
22

23
## Basic Usage
24

25
```python
26
import oracledb
27
import getpass
28

29
# Basic connection
30
username = "scott"
31
dsn = "localhost/orclpdb"
32
password = getpass.getpass(f"Enter password for {username}@{dsn}: ")
33

34
# Connect and execute SQL
35
with oracledb.connect(user=username, password=password, dsn=dsn) as connection:
36
    with connection.cursor() as cursor:
37
        # Execute a query
38
        cursor.execute("SELECT sysdate FROM dual")
39
        result = cursor.fetchone()
40
        print(f"Current date: {result[0]}")
41
        
42
        # Execute with parameters
43
        cursor.execute("SELECT employee_id, first_name FROM employees WHERE department_id = :dept_id", 
44
                      dept_id=10)
45
        for row in cursor.fetchall():
46
            print(f"Employee {row[0]}: {row[1]}")
47
```
48

49
## Architecture
50

51
Oracle python-oracledb provides a comprehensive database interface with two connectivity modes:
52

53
- **Thin Mode**: Direct connection to Oracle Database without requiring client libraries, suitable for most applications
54
- **Thick Mode**: Uses Oracle Client libraries, providing additional functionality and performance optimizations
55
- **Connection Management**: Single connections and connection pools for scalable applications
56
- **Execution Model**: Cursors for SQL execution with support for bind variables, batch operations, and result fetching
57
- **Data Handling**: Support for all Oracle data types including LOBs, objects, collections, and specialized types like JSON and vectors
58
- **Advanced Features**: Pipelining for performance, event subscriptions, two-phase commit, and administrative operations
59

60
## Capabilities
61

62
### Database Connectivity
63

64
Core connection management including single connections, connection pools, and connection parameter configuration. Supports both synchronous and asynchronous operations with thin and thick connectivity modes.
65

66
```python { .api }
67
def connect(user=None, password=None, dsn=None, **kwargs) -> Connection: ...
68
def connect_async(user=None, password=None, dsn=None, **kwargs) -> AsyncConnection: ...
69
def create_pool(user=None, password=None, dsn=None, **kwargs) -> ConnectionPool: ...
70
def create_pool_async(user=None, password=None, dsn=None, **kwargs) -> AsyncConnectionPool: ...
71

72
class Connection:
73
    def cursor(self, scrollable=False) -> Cursor: ...
74
    def commit(self) -> None: ...
75
    def rollback(self) -> None: ...
76
    def close(self) -> None: ...
77

78
class AsyncConnection:
79
    async def cursor(self, scrollable=False) -> AsyncCursor: ...
80
    async def commit(self) -> None: ...
81
    async def rollback(self) -> None: ...
82
    async def close(self) -> None: ...
83
```
84

85
[Database Connectivity](./connectivity.md)
86

87
### SQL Execution
88

89
SQL and PL/SQL execution through cursors with support for bind variables, batch operations, stored procedures, and functions. Includes comprehensive result fetching and data type handling.
90

91
```python { .api }
92
class Cursor:
93
    def execute(self, statement, parameters=None) -> None: ...
94
    def executemany(self, statement, parameters) -> None: ...
95
    def fetchone(self) -> tuple: ...
96
    def fetchmany(self, size=None) -> list: ...
97
    def fetchall(self) -> list: ...
98
    def callfunc(self, name, return_type, parameters=None): ...
99
    def callproc(self, name, parameters=None) -> list: ...
100

101
class AsyncCursor:
102
    async def execute(self, statement, parameters=None) -> None: ...
103
    async def executemany(self, statement, parameters) -> None: ...
104
    async def fetchone(self) -> tuple: ...
105
    async def fetchmany(self, size=None) -> list: ...
106
    async def fetchall(self) -> list: ...
107
```
108

109
[SQL Execution](./sql-execution.md)
110

111
### Connection Pooling
112

113
Manage pools of database connections for scalable applications. Supports configuration of pool size, connection timeout, and connection acquisition modes.
114

115
```python { .api }
116
class ConnectionPool:
117
    def acquire(self, user=None, password=None, cclass=None, purity=None, tag=None, matchanytag=False, shardingkey=None, supershardingkey=None) -> Connection: ...
118
    def release(self, connection, tag=None) -> None: ...
119
    def close(self, force=False) -> None: ...
120
    def reconfigure(self, min=None, max=None, increment=None, **kwargs) -> None: ...
121

122
class AsyncConnectionPool:
123
    async def acquire(self, user=None, password=None, cclass=None, purity=None, tag=None, matchanytag=False, shardingkey=None, supershardingkey=None) -> AsyncConnection: ...
124
    async def release(self, connection, tag=None) -> None: ...
125
    async def close(self, force=False) -> None: ...
126
```
127

128
[Connection Pooling](./connection-pooling.md)
129

130
### Large Objects (LOBs)
131

132
Handle Binary Large Objects (BLOB), Character Large Objects (CLOB), National Character Large Objects (NCLOB), and Binary Files (BFILE) with streaming read/write operations.
133

134
```python { .api }
135
class LOB:
136
    def read(self, offset=1, amount=None) -> bytes | str: ...
137
    def write(self, data, offset=1) -> None: ...
138
    def size(self) -> int: ...
139
    def trim(self, new_size) -> None: ...
140
    def getchunksize(self) -> int: ...
141
    def open(self) -> None: ...
142
    def close(self) -> None: ...
143

144
class AsyncLOB:
145
    async def read(self, offset=1, amount=None) -> bytes | str: ...
146
    async def write(self, data, offset=1) -> None: ...
147
    async def size(self) -> int: ...
148
    async def trim(self, new_size) -> None: ...
149
```
150

151
[Large Objects](./lobs.md)
152

153
### Database Objects
154

155
Work with Oracle user-defined types including objects, collections, and nested tables. Provides object type metadata and instance manipulation.
156

157
```python { .api }
158
class DbObject:
159
    def asdict(self) -> dict: ...
160
    def aslist(self) -> list: ...
161
    def copy(self) -> DbObject: ...
162
    def append(self, element) -> None: ...
163
    def extend(self, sequence) -> None: ...
164
    def delete(self, index) -> None: ...
165
    def exists(self, index) -> bool: ...
166
    def getelement(self, index): ...
167
    def setelement(self, index, value) -> None: ...
168
    def size(self) -> int: ...
169
    def trim(self, size) -> None: ...
170

171
class DbObjectType:
172
    def newobject(self, value=None) -> DbObject: ...
173
```
174

175
[Database Objects](./database-objects.md)
176

177
### Pipeline Operations
178

179
Batch multiple database operations for improved performance using pipelining. Supports execute, fetch, and stored procedure operations in batches.
180

181
```python { .api }
182
class Pipeline:
183
    def add_execute(self, statement, parameters=None) -> None: ...
184
    def add_executemany(self, statement, parameters) -> None: ...
185
    def add_fetchall(self) -> None: ...
186
    def add_fetchone(self) -> None: ...
187
    def add_fetchmany(self, size=None) -> None: ...
188
    def add_callfunc(self, name, return_type, parameters=None) -> None: ...
189
    def add_callproc(self, name, parameters=None) -> None: ...
190
    def add_commit(self) -> None: ...
191
    def execute(self) -> list: ...
192

193
def create_pipeline() -> Pipeline: ...
194
```
195

196
[Pipeline Operations](./pipeline.md)
197

198
### Simple Oracle Document Access (SODA)
199

200
NoSQL-style API for working with JSON documents in Oracle Database. SODA provides document operations, filtering, indexing, and metadata management without writing SQL.
201

202
```python { .api }
203
# Access SODA through connection
204
def getSodaDatabase(self) -> SodaDatabase: ...
205

206
class SodaDatabase:
207
    def createCollection(self, name: str, metadata: Union[str, dict] = None, mapMode: bool = False) -> SodaCollection: ...
208
    def createDocument(self, content: Any, key: str = None, mediaType: str = "application/json") -> SodaDocument: ...
209
    def getCollectionNames(self, startName: str = None, limit: int = 0) -> List[str]: ...
210
    def openCollection(self, name: str) -> SodaCollection: ...
211

212
class SodaCollection:
213
    def find(self) -> SodaOperation: ...
214
    def insertOne(self, doc: Any) -> None: ...
215
    def insertMany(self, docs: list) -> None: ...
216
    def save(self, doc: Any) -> None: ...
217
    def drop(self) -> bool: ...
218
    def createIndex(self, spec: Union[dict, str]) -> None: ...
219

220
class SodaOperation:
221
    def filter(self, value: Union[dict, str]) -> SodaOperation: ...
222
    def key(self, value: str) -> SodaOperation: ...
223
    def limit(self, value: int) -> SodaOperation: ...
224
    def getDocuments(self) -> list: ...
225
    def getOne(self) -> Union[SodaDocument, None]: ...
226
    def remove(self) -> int: ...
227
    def replaceOne(self, doc: Any) -> bool: ...
228
```
229

230
[Simple Oracle Document Access](./soda.md)
231

232
### Advanced Queuing (AQ)
233

234
Oracle Advanced Queuing provides message queuing functionality for reliable, persistent, and transactional message passing using the database as a message broker.
235

236
```python { .api }
237
# Access queues through connection
238
def queue(self, name: str, payload_type: DbObjectType = None) -> Queue: ...
239

240
class Queue:
241
    def enqone(self, message: MessageProperties) -> None: ...
242
    def enqmany(self, messages: list) -> None: ...
243
    def deqone(self) -> Union[MessageProperties, None]: ...
244
    def deqmany(self, max_num_messages: int) -> list: ...
245

246
class AsyncQueue:
247
    async def enqone(self, message: MessageProperties) -> None: ...
248
    async def enqmany(self, messages: list) -> None: ...
249
    async def deqone(self) -> Union[MessageProperties, None]: ...
250
    async def deqmany(self, max_num_messages: int) -> list: ...
251

252
class MessageProperties:
253
    payload: Union[bytes, str, dict, list, DbObject]
254
    priority: int
255
    correlation: str
256
    delay: int
257
    expiration: int
258

259
class DeqOptions:
260
    mode: int  # DEQ_BROWSE, DEQ_LOCKED, DEQ_REMOVE, etc.
261
    navigation: int
262
    visibility: int
263
    wait: int
264

265
class EnqOptions:
266
    visibility: int
267
    deliverymode: int
268
```
269

270
[Advanced Queuing](./advanced-queuing.md)
271

272
### Event Subscriptions
273

274
Subscribe to database events including object changes, query result changes, and Advanced Queuing (AQ) messages for real-time notifications.
275

276
```python { .api }
277
class Message:
278
    type: int
279
    dbname: str
280
    tables: list
281
    queries: list
282
    consumer_name: str
283
    queue_name: str
284

285
class MessageTable:
286
    name: str
287
    operation: int
288
    rows: list
289

290
class MessageRow:
291
    operation: int
292
    rowid: str
293
```
294

295
[Event Subscriptions](./subscriptions.md)
296

297
### Data Types and Constants
298

299
Comprehensive support for all Oracle data types, authentication modes, and configuration constants.
300

301
```python { .api }
302
# Database Type Constants
303
DB_TYPE_VARCHAR: type
304
DB_TYPE_NUMBER: type
305
DB_TYPE_DATE: type
306
DB_TYPE_TIMESTAMP: type
307
DB_TYPE_BLOB: type
308
DB_TYPE_CLOB: type
309
DB_TYPE_JSON: type
310
DB_TYPE_VECTOR: type
311

312
# Authentication Modes
313
AUTH_MODE_DEFAULT: int
314
AUTH_MODE_SYSDBA: int
315
AUTH_MODE_SYSOPER: int
316

317
# API Type Constants
318
STRING: type
319
NUMBER: type
320
DATETIME: type
321
BINARY: type
322
ROWID: type
323
```
324

325
[Data Types and Constants](./data-types.md)
326

327
## Exception Hierarchy
328

329
```python { .api }
330
class Error(Exception): ...
331
class DatabaseError(Error): ...
332
class DataError(DatabaseError): ...
333
class IntegrityError(DatabaseError): ...
334
class InternalError(DatabaseError): ...
335
class NotSupportedError(DatabaseError): ...
336
class OperationalError(DatabaseError): ...
337
class ProgrammingError(DatabaseError): ...
338
class InterfaceError(Error): ...
339
class Warning(Exception): ...
340
```
341

342
## Utility Functions
343

344
```python { .api }
345
def makedsn(host, port, sid=None, service_name=None, region=None, sharding_key=None, super_sharding_key=None) -> str: ...
346
def enable_thin_mode(thin=True) -> None: ...
347
def is_thin_mode() -> bool: ...
348
def init_oracle_client(lib_dir=None, config_dir=None, error_url=None, driver_name=None) -> None: ...
349
def clientversion() -> tuple: ...
350

351
# Arrow Integration
352
def from_arrow(arrow_table, **kwargs): ...
353

354
# Hook Registration
355
def register_params_hook(hook_func) -> None: ...
356
def unregister_params_hook(hook_func) -> None: ...
357
def register_password_type(password_type) -> None: ...
358
def register_protocol(protocol_name, protocol_func) -> None: ...
359

360
# Constructor Functions (DB API 2.0 compatibility)
361
def Binary(data) -> bytes: ...
362
def Date(year, month, day) -> datetime.date: ...
363
def DateFromTicks(ticks) -> datetime.date: ...
364
def Time(hour, minute, second) -> datetime.time: ...
365
def TimeFromTicks(ticks) -> datetime.time: ...
366
def Timestamp(year, month, day, hour, minute, second) -> datetime.datetime: ...
367
def TimestampFromTicks(ticks) -> datetime.datetime: ...
368
```
369

370
## Configuration
371

372
```python { .api }
373
class ConnectParams:
374
    user: str
375
    password: str
376
    dsn: str
377
    # ... many other connection parameters
378

379
class PoolParams:
380
    min: int
381
    max: int
382
    increment: int
383
    # ... many other pool parameters
384

385
# Global defaults
386
defaults: object
387
```