0
# Database Connectivity
1

2
Core connection management including single connections, connection pools, and connection parameter configuration. Oracle python-oracledb supports both synchronous and asynchronous operations with thin mode (direct connection) and thick mode (using Oracle Client libraries) connectivity options.
3

4
## Capabilities
5

6
### Creating Connections
7

8
Create database connections with various authentication and configuration options.
9

10
```python { .api }
11
def connect(
12
    dsn: Optional[str] = None,
13
    *,
14
    pool: Optional[ConnectionPool] = None,
15
    pool_alias: Optional[str] = None,
16
    conn_class: Type[Connection] = Connection,
17
    params: Optional[ConnectParams] = None,
18
    user: Optional[str] = None,
19
    proxy_user: Optional[str] = None,
20
    password: Optional[str] = None,
21
    newpassword: Optional[str] = None,
22
    wallet_password: Optional[str] = None,
23
    access_token: Optional[Union[str, tuple, Callable]] = None,
24
    host: Optional[str] = None,
25
    port: Optional[int] = None,
26
    protocol: Optional[str] = None,
27
    https_proxy: Optional[str] = None,
28
    https_proxy_port: Optional[int] = None,
29
    service_name: Optional[str] = None,
30
    instance_name: Optional[str] = None,
31
    sid: Optional[str] = None,
32
    server_type: Optional[str] = None,
33
    cclass: Optional[str] = None,
34
    purity: Optional[Purity] = None,
35
    expire_time: Optional[int] = None,
36
    retry_count: Optional[int] = None,
37
    retry_delay: Optional[int] = None,
38
    tcp_connect_timeout: Optional[float] = None,
39
    ssl_server_dn_match: Optional[bool] = None,
40
    ssl_server_cert_dn: Optional[str] = None,
41
    wallet_location: Optional[str] = None,
42
    events: Optional[bool] = None,
43
    externalauth: Optional[bool] = None,
44
    mode: Optional[AuthMode] = None,
45
    disable_oob: Optional[bool] = None,
46
    stmtcachesize: Optional[int] = None,
47
    edition: Optional[str] = None,
48
    tag: Optional[str] = None,
49
    matchanytag: Optional[bool] = None,
50
    config_dir: Optional[str] = None,
51
    appcontext: Optional[list] = None,
52
    shardingkey: Optional[list] = None,
53
    supershardingkey: Optional[list] = None,
54
    debug_jdwp: Optional[str] = None,
55
    connection_id_prefix: Optional[str] = None,
56
    ssl_context: Optional[Any] = None,
57
    sdu: Optional[int] = None,
58
    pool_boundary: Optional[str] = None,
59
    use_tcp_fast_open: Optional[bool] = None,
60
    ssl_version: Optional[Any] = None,
61
    program: Optional[str] = None,
62
    machine: Optional[str] = None,
63
    terminal: Optional[str] = None,
64
    osuser: Optional[str] = None,
65
    driver_name: Optional[str] = None,
66
    use_sni: Optional[bool] = None,
67
    thick_mode_dsn_passthrough: Optional[bool] = None,
68
    extra_auth_params: Optional[dict] = None,
69
    pool_name: Optional[str] = None,
70
    handle: Optional[int] = None,
71
) -> Connection:
72
    """
73
    Creates a connection to an Oracle database and returns a Connection object.
74
    
75
    Parameters:
76
    - dsn (str): Data source name, connection string, or Easy Connect string  
77
    - pool (ConnectionPool): Connection pool to acquire connection from
78
    - pool_alias (str): Named pool alias to acquire connection from
79
    - params (ConnectParams): Pre-configured connection parameters object
80
    - user (str): Username for database authentication
81
    - proxy_user (str): Proxy user name for proxy authentication
82
    - password (str): Password for database authentication
83
    - newpassword (str): New password for password change on connect
84
    - wallet_password (str): Password for encrypted wallet (thin mode)
85
    - access_token (Union[str, tuple, Callable]): OAuth2 or OCI IAM token
86
    - host (str): Database server hostname or IP address
87
    - port (int): Database listener port number (default: 1521)
88
    - protocol (str): Connection protocol ("tcp" or "tcps")
89
    - service_name (str): Oracle service name
90
    - sid (str): Oracle System Identifier (SID)
91
    - mode (AuthMode): Authentication mode (SYSDBA, SYSOPER, etc.)
92
    - Other parameters: Various connection and SSL configuration options
93
    
94
    Returns:
95
    Connection: Database connection object
96
    """
97

98
def connect_async(
99
    dsn: Optional[str] = None,
100
    *,
101
    pool: Optional[AsyncConnectionPool] = None,
102
    pool_alias: Optional[str] = None,
103
    conn_class: Type[AsyncConnection] = AsyncConnection,
104
    params: Optional[ConnectParams] = None,
105
    **kwargs  # Same parameters as connect()
106
) -> AsyncConnection:
107
    """
108
    Creates an asynchronous connection to an Oracle database.
109
    
110
    Parameters: Same as connect() but returns AsyncConnection
111
    
112
    Returns:
113
    AsyncConnection: Asynchronous database connection object
114
    """
115
```
116

117
### Connection Class
118

119
Manage database connections with transaction control, cursor creation, and administrative operations.
120

121
```python { .api }
122
class Connection:
123
    """Synchronous database connection."""
124
    
125
    # Properties
126
    autocommit: bool
127
    call_timeout: int
128
    current_schema: str
129
    dsn: str
130
    username: str
131
    version: tuple
132
    transaction_in_progress: bool
133
    thin: bool
134
    warning: Warning
135
    
136
    def cursor(self, scrollable=False) -> Cursor:
137
        """
138
        Create a cursor for executing SQL statements.
139
        
140
        Parameters:
141
        - scrollable (bool): Enable scrollable cursor
142
        
143
        Returns:
144
        Cursor object
145
        """
146
    
147
    def commit(self) -> None:
148
        """Commit the current transaction."""
149
    
150
    def rollback(self) -> None:
151
        """Rollback the current transaction."""
152
        
153
    def close(self) -> None:
154
        """Close the connection and free resources."""
155
        
156
    def ping(self) -> None:
157
        """Test if the connection is still active."""
158
        
159
    def begin(self, formatId=None, transactionId=None, branchId=None) -> None:
160
        """
161
        Begin a new transaction or join distributed transaction.
162
        
163
        Parameters:
164
        - formatId (int): Format identifier for distributed transaction
165
        - transactionId (bytes): Transaction identifier  
166
        - branchId (bytes): Branch identifier
167
        """
168
        
169
    def prepare(self) -> bool:
170
        """
171
        Prepare the current transaction for two-phase commit.
172
        
173
        Returns:
174
        bool: True if transaction was prepared, False if no work to prepare
175
        """
176
        
177
    def subscribe(
178
        self,
179
        namespace=SUBSCR_NAMESPACE_DBCHANGE,
180
        protocol=SUBSCR_PROTO_CALLBACK,
181
        callback=None,
182
        timeout=0,
183
        operations=OPCODE_ALLOPS,
184
        port=0,
185
        qos=0,
186
        ip_address=None,
187
        grouping_class=SUBSCR_GROUPING_CLASS_NONE,
188
        grouping_value=0,
189
        grouping_type=SUBSCR_GROUPING_TYPE_SUMMARY,
190
        name=None,
191
        client_initiated=False,
192
        recipient_name=None
193
    ) -> Subscription:
194
        """
195
        Create a subscription for database event notifications.
196
        
197
        Parameters:
198
        - namespace (int): Subscription namespace
199
        - protocol (int): Notification protocol  
200
        - callback: Callback function for notifications
201
        - timeout (int): Subscription timeout
202
        
203
        Returns:
204
        Subscription object
205
        """
206
        
207
    def createlob(self, lob_type) -> LOB:
208
        """
209
        Create a temporary LOB.
210
        
211
        Parameters:
212
        - lob_type: LOB type (DB_TYPE_BLOB, DB_TYPE_CLOB, etc.)
213
        
214
        Returns:
215
        LOB object
216
        """
217
        
218
    def gettype(self, name) -> DbObjectType:
219
        """
220
        Get database object type by name.
221
        
222
        Parameters:
223
        - name (str): Object type name
224
        
225
        Returns:
226
        DbObjectType object
227
        """
228
        
229
    def cancel(self) -> None:
230
        """Cancel long-running database operation."""
231
        
232
    def shutdown(self, mode=DBSHUTDOWN_IMMEDIATE) -> None:
233
        """
234
        Shutdown the database.
235
        
236
        Parameters:
237
        - mode (int): Shutdown mode
238
        """
239
        
240
    def startup(self, force=False, restrict=False, pfile=None, spfile=None) -> None:
241
        """
242
        Startup the database.
243
        
244
        Parameters:
245
        - force (bool): Force startup
246
        - restrict (bool): Restrict mode
247
        - pfile (str): Parameter file path
248
        - spfile (str): Server parameter file path
249
        """
250
```
251

252
### Async Connection Class
253

254
Asynchronous version of Connection with async/await support.
255

256
```python { .api }
257
class AsyncConnection:
258
    """Asynchronous database connection."""
259
    
260
    # Properties (same as Connection)
261
    autocommit: bool
262
    call_timeout: int
263
    current_schema: str
264
    dsn: str
265
    username: str
266
    version: tuple
267
    transaction_in_progress: bool
268
    thin: bool
269
    warning: Warning
270
    
271
    async def cursor(self, scrollable=False) -> AsyncCursor:
272
        """
273
        Create an async cursor for executing SQL statements.
274
        
275
        Parameters:
276
        - scrollable (bool): Enable scrollable cursor
277
        
278
        Returns:
279
        AsyncCursor object
280
        """
281
    
282
    async def commit(self) -> None:
283
        """Commit the current transaction."""
284
    
285
    async def rollback(self) -> None:
286
        """Rollback the current transaction."""
287
        
288
    async def close(self) -> None:
289
        """Close the connection and free resources."""
290
        
291
    async def ping(self) -> None:
292
        """Test if the connection is still active."""
293
```
294

295
### Connection Parameters
296

297
Configure connection parameters using ConnectParams class.
298

299
```python { .api }
300
class ConnectParams:
301
    """Connection parameter configuration."""
302
    
303
    user: str
304
    password: str
305
    dsn: str
306
    mode: int
307
    encoding: str
308
    nencoding: str
309
    edition: str
310
    appcontext: list
311
    tag: str
312
    matchanytag: bool
313
    config_dir: str
314
    appname: str
315
    stmtcachesize: int
316
    disable_oob: bool
317
    auth_token: str
318
    access_token: str
319
    expires_in: int
320
    private_key: str
321
    wallet_location: str
322
    wallet_password: str
323
    cclass: str
324
    purity: int
325
    server_type: str
326
    ctype: str
327
    sdu: int
328
    pool_boundary: str
329
    use_tcp_fast_open: bool
330
    ssl_server_dn_match: bool
331
    ssl_server_cert_dn: str
332
    ssl_version: str
333
    https_proxy: str
334
    https_proxy_port: int
335
    debug_jdwp: str
336
    connection_id_prefix: str
337
    ssl_context: object
338
    soda_metadata_cache: bool
339
    fetch_lobs: bool
340
    fetch_decimals: bool
341
    fetch_info: list
342
    autocommit: bool
343
    call_timeout: int
344
    retry_count: int
345
    retry_delay: int
346
    externalauth: bool
347
    homogeneous: bool
348
```
349

350
### DSN Construction
351

352
Create data source names (DSN) for database connections.
353

354
```python { .api }
355
def makedsn(
356
    host,
357
    port,
358
    sid=None,
359
    service_name=None,
360
    region=None,
361
    sharding_key=None,
362
    super_sharding_key=None,
363
    cclass=None,
364
    purity=None,
365
    expire_time=None,
366
    retry_count=None,
367
    retry_delay=None,
368
    tcp_connect_timeout=None,
369
    ssl_server_dn_match=None,
370
    ssl_server_cert_dn=None,
371
    wallet_location=None,
372
    **kwargs
373
) -> str:
374
    """
375
    Create a data source name string.
376
    
377
    Parameters:
378
    - host (str): Database server hostname or IP
379
    - port (int): Database server port number
380
    - sid (str): Oracle System Identifier (exclusive with service_name)
381
    - service_name (str): Service name (exclusive with sid)
382
    - region (str): Oracle Cloud region
383
    - sharding_key (list): Sharding key values
384
    - super_sharding_key (list): Super sharding key values
385
    
386
    Returns:
387
    str: Formatted DSN string
388
    """
389
```
390

391
## Usage Examples
392

393
### Basic Connection
394

395
```python
396
import oracledb
397
import getpass
398

399
# Simple connection
400
username = "hr"
401
dsn = "localhost:1521/xepdb1"
402
password = getpass.getpass(f"Password for {username}: ")
403

404
connection = oracledb.connect(user=username, password=password, dsn=dsn)
405
print(f"Connected to Oracle Database version: {connection.version}")
406
connection.close()
407
```
408

409
### Connection with Advanced Options
410

411
```python
412
import oracledb
413

414
# Connection with advanced configuration
415
connection = oracledb.connect(
416
    user="scott",
417
    password="tiger",
418
    dsn="myhost:1521/myservice",
419
    encoding="UTF-8",
420
    autocommit=False,
421
    call_timeout=30000,  # 30 seconds
422
    appname="MyApplication",
423
    stmtcachesize=50
424
)
425

426
# Use connection
427
with connection.cursor() as cursor:
428
    cursor.execute("SELECT COUNT(*) FROM employees")
429
    count = cursor.fetchone()[0]
430
    print(f"Employee count: {count}")
431
    
432
connection.close()
433
```
434

435
### Async Connection
436

437
```python
438
import asyncio
439
import oracledb
440

441
async def main():
442
    # Create async connection
443
    connection = await oracledb.connect_async(
444
        user="hr", 
445
        password="password", 
446
        dsn="localhost/xepdb1"
447
    )
448
    
449
    async with connection.cursor() as cursor:
450
        await cursor.execute("SELECT sysdate FROM dual")
451
        result = await cursor.fetchone()
452
        print(f"Current date: {result[0]}")
453
    
454
    await connection.close()
455

456
# Run async function
457
asyncio.run(main())
458
```