0
# DB-API 2.0 Interface
1

2
Python Database API 2.0 compliant interface providing standard database connectivity patterns. This is the recommended interface for most applications as it follows established Python database standards and integrates seamlessly with existing database tooling.
3

4
## Capabilities
5

6
### Connection Factory
7

8
Creates database connections with comprehensive configuration options including authentication, session properties, timeouts, and protocol settings.
9

10
```python { .api }
11
def connect(
12
    host: str,
13
    port: int = None,
14
    user: str = None,
15
    source: str = "trino-python-client",
16
    catalog: str = None,
17
    schema: str = None,
18
    session_properties: Dict[str, str] = None,
19
    http_headers: Dict[str, str] = None,
20
    http_scheme: str = None,
21
    auth: Authentication = None,
22
    extra_credential: List[Tuple[str, str]] = None,
23
    max_attempts: int = 3,
24
    request_timeout: float = 30.0,
25
    isolation_level: IsolationLevel = IsolationLevel.AUTOCOMMIT,
26
    verify: bool = True,
27
    http_session: Session = None,
28
    client_tags: List[str] = None,
29
    legacy_primitive_types: bool = False,
30
    legacy_prepared_statements: bool = None,
31
    roles: Union[Dict[str, str], str] = None,
32
    timezone: str = None,
33
    encoding: Union[str, List[str]] = None
34
) -> Connection
35
```
36

37
**Parameters:**
38
- `host`: Trino coordinator hostname or URL
39
- `port`: TCP port (default: 8080 for HTTP, 443 for HTTPS)
40
- `user`: Username for authentication and query attribution
41
- `source`: Query source identifier for tracking
42
- `catalog`: Default catalog for queries
43
- `schema`: Default schema for queries
44
- `session_properties`: Trino session configuration properties
45
- `http_headers`: Additional HTTP headers
46
- `http_scheme`: "http" or "https" (auto-detected from URL or port)
47
- `auth`: Authentication instance (BasicAuthentication, JWTAuthentication, etc.)
48
- `extra_credential`: Additional credentials as key-value pairs
49
- `max_attempts`: Maximum retry attempts for failed requests
50
- `request_timeout`: Request timeout in seconds
51
- `isolation_level`: Transaction isolation level
52
- `verify`: SSL certificate verification (boolean or path to CA bundle)
53
- `http_session`: Custom requests.Session instance
54
- `client_tags`: Tags for query identification
55
- `legacy_primitive_types`: Return string representations for edge-case values
56
- `legacy_prepared_statements`: Force legacy prepared statement protocol
57
- `roles`: Authorization roles for catalogs
58
- `timezone`: Session timezone (IANA timezone name)
59
- `encoding`: Spooled protocol encoding preference
60

61
### Connection Management
62

63
Database connection object providing transaction control, cursor creation, and resource management with context manager support.
64

65
```python { .api }
66
class Connection:
67
    def __init__(
68
        self,
69
        host: str,
70
        port: int = None,
71
        user: str = None,
72
        **kwargs
73
    )
74
    
75
    def cursor(
76
        self,
77
        cursor_style: str = "row",
78
        legacy_primitive_types: bool = None
79
    ) -> Cursor
80
    """
81
    Create a new cursor for executing queries.
82
    
83
    Parameters:
84
    - cursor_style: "row" for regular cursor, "segment" for spooled segments
85
    - legacy_primitive_types: Override connection-level setting
86
    
87
    Returns:
88
    Cursor instance for query execution
89
    """
90
    
91
    def commit(self) -> None
92
    """Commit the current transaction."""
93
    
94
    def rollback(self) -> None
95
    """Rollback the current transaction."""
96
    
97
    def close(self) -> None
98
    """Close the connection and release resources."""
99
    
100
    def start_transaction(self) -> Transaction
101
    """Start a new transaction and return transaction object."""
102
    
103
    @property
104
    def isolation_level(self) -> IsolationLevel
105
    """Current transaction isolation level."""
106
    
107
    @property
108
    def transaction(self) -> Optional[Transaction]
109
    """Current transaction object if active."""
110
    
111
    # Context manager support
112
    def __enter__(self) -> Connection
113
    def __exit__(self, exc_type, exc_value, traceback) -> None
114
```
115

116
### Query Execution
117

118
Cursor object for executing SQL statements with support for parameterized queries, batch execution, and comprehensive result retrieval.
119

120
```python { .api }
121
class Cursor:
122
    def __init__(
123
        self,
124
        connection: Connection,
125
        request: TrinoRequest,
126
        legacy_primitive_types: bool = False
127
    )
128
    
129
    def execute(self, operation: str, params: List[Any] = None) -> Cursor
130
    """
131
    Execute a SQL query with optional parameters.
132
    
133
    Parameters:
134
    - operation: SQL statement to execute
135
    - params: Query parameters for prepared statement execution
136
    
137
    Returns:
138
    Self for method chaining
139
    """
140
    
141
    def executemany(self, operation: str, seq_of_params: List[List[Any]]) -> Cursor
142
    """
143
    Execute SQL query against multiple parameter sets.
144
    
145
    Parameters:
146
    - operation: SQL statement to execute
147
    - seq_of_params: Sequence of parameter lists
148
    
149
    Returns:
150
    Self for method chaining
151
    """
152
    
153
    def fetchone(self) -> Optional[List[Any]]
154
    """Fetch next row or None if no more data."""
155
    
156
    def fetchmany(self, size: int = None) -> List[List[Any]]
157
    """
158
    Fetch multiple rows.
159
    
160
    Parameters:
161
    - size: Number of rows to fetch (defaults to arraysize)
162
    
163
    Returns:
164
    List of rows (each row is a list of column values)
165
    """
166
    
167
    def fetchall(self) -> List[List[Any]]
168
    """Fetch all remaining rows."""
169
    
170
    def describe(self, sql: str) -> List[DescribeOutput]
171
    """
172
    Get column information for a SQL statement without executing it.
173
    
174
    Parameters:
175
    - sql: SQL statement to analyze
176
    
177
    Returns:
178
    List of DescribeOutput objects with column metadata
179
    """
180
    
181
    def cancel(self) -> None
182
    """Cancel the currently executing query."""
183
    
184
    def close(self) -> None
185
    """Close the cursor and cancel any running queries."""
186
    
187
    @property
188
    def description(self) -> List[ColumnDescription]
189
    """Column descriptions for the last executed query."""
190
    
191
    @property
192
    def rowcount(self) -> int
193
    """Number of rows affected by last query (-1 if unknown)."""
194
    
195
    @property
196
    def arraysize(self) -> int
197
    """Default number of rows for fetchmany()."""
198
    
199
    @arraysize.setter
200
    def arraysize(self, size: int) -> None
201
    
202
    @property
203
    def query_id(self) -> Optional[str]
204
    """Trino query ID for the last executed query."""
205
    
206
    @property
207
    def query(self) -> Optional[str]
208
    """SQL text of the last executed query."""
209
    
210
    @property
211
    def stats(self) -> Optional[Dict[str, Any]]
212
    """Query execution statistics."""
213
    
214
    @property
215
    def warnings(self) -> Optional[List[Dict[str, Any]]]
216
    """Query execution warnings."""
217
    
218
    @property
219
    def info_uri(self) -> Optional[str]
220
    """URI for detailed query information."""
221
    
222
    @property
223
    def update_type(self) -> Optional[str]
224
    """Type of update operation (INSERT, UPDATE, DELETE, etc.)."""
225
    
226
    # Context manager support
227
    def __enter__(self) -> Cursor
228
    def __exit__(self, exc_type, exc_value, traceback) -> None
229
```
230

231
### Segment Cursor
232

233
Specialized cursor for handling spooled protocol segments, providing direct access to compressed data segments for high-performance scenarios.
234

235
```python { .api }
236
class SegmentCursor(Cursor):
237
    def __init__(
238
        self,
239
        connection: Connection,
240
        request: TrinoRequest,
241
        legacy_primitive_types: bool = False
242
    )
243
    
244
    def execute(self, operation: str, params: List[Any] = None) -> SegmentCursor
245
    """
246
    Execute query and return segments instead of mapped rows.
247
    Note: params are not supported for SegmentCursor.
248
    """
249
```
250

251
### Metadata Objects
252

253
Data structures for query and column metadata with rich type information and result descriptions.
254

255
```python { .api }
256
class ColumnDescription:
257
    """Column metadata following DB-API 2.0 specification."""
258
    name: str
259
    type_code: int
260
    display_size: int
261
    internal_size: int
262
    precision: int
263
    scale: int
264
    null_ok: bool
265
    
266
    @classmethod
267
    def from_column(cls, column: Dict[str, Any]) -> ColumnDescription
268

269
class DescribeOutput:
270
    """Output column information from DESCRIBE statement."""
271
    name: str
272
    catalog: str
273
    schema: str
274
    table: str
275
    type: str
276
    type_size: int
277
    aliased: bool
278
    
279
    @classmethod
280
    def from_row(cls, row: List[Any]) -> DescribeOutput
281
```
282

283
### Global Module Attributes
284

285
DB-API 2.0 compliance information and global configuration.
286

287
```python { .api }
288
apilevel: str = "2.0"
289
threadsafety: int = 2
290
paramstyle: str = "qmark"
291
```
292

293
### Type Objects
294

295
Type classification objects for result column type checking following DB-API 2.0 specification.
296

297
```python { .api }
298
class DBAPITypeObject:
299
    def __init__(self, *values: str)
300
    def __eq__(self, other: str) -> bool
301

302
# Type categories
303
STRING: DBAPITypeObject  # VARCHAR, CHAR, VARBINARY, JSON, IPADDRESS
304
BINARY: DBAPITypeObject  # ARRAY, MAP, ROW, HyperLogLog, P4HyperLogLog, QDigest
305
NUMBER: DBAPITypeObject  # BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, REAL, DOUBLE, DECIMAL
306
DATETIME: DBAPITypeObject  # DATE, TIME, TIMESTAMP, INTERVAL types
307
ROWID: DBAPITypeObject  # (empty - Trino has no row IDs)
308
```
309

310
### Date/Time Constructors
311

312
DB-API 2.0 date and time constructor functions for creating temporal values.
313

314
```python { .api }
315
def DateFromTicks(ticks: float) -> date
316
"""Create date from Unix timestamp."""
317

318
def TimestampFromTicks(ticks: float) -> datetime
319
"""Create datetime from Unix timestamp."""
320

321
def TimeFromTicks(ticks: float) -> time
322
"""Create time from Unix timestamp."""
323

324
def Binary(string: str) -> bytes
325
"""Convert string to binary data."""
326

327
# Type aliases
328
Date = date
329
Time = time
330
Timestamp = datetime
331
```
332

333
## Usage Examples
334

335
### Basic Query Execution
336

337
```python
338
from trino.dbapi import connect
339

340
conn = connect(
341
    host="localhost",
342
    port=8080,
343
    user="testuser",
344
    catalog="memory",
345
    schema="default"
346
)
347

348
cur = conn.cursor()
349
cur.execute("SELECT name, age FROM users WHERE age > ?", [25])
350

351
# Fetch results
352
for row in cur:
353
    print(f"Name: {row[0]}, Age: {row[1]}")
354

355
cur.close()
356
conn.close()
357
```
358

359
### Transaction Management
360

361
```python
362
from trino.dbapi import connect
363
from trino.transaction import IsolationLevel
364

365
conn = connect(
366
    host="localhost",
367
    port=8080,
368
    user="testuser",
369
    catalog="memory",
370
    schema="default",
371
    isolation_level=IsolationLevel.READ_COMMITTED
372
)
373

374
with conn:  # Auto-commit on success, rollback on exception
375
    cur = conn.cursor()
376
    cur.execute("INSERT INTO users VALUES (?, ?)", ["John", 30])
377
    cur.execute("INSERT INTO users VALUES (?, ?)", ["Jane", 25])
378
```
379

380
### Batch Operations
381

382
```python
383
from trino.dbapi import connect
384

385
conn = connect(host="localhost", port=8080, user="testuser")
386
cur = conn.cursor()
387

388
# Execute same query with multiple parameter sets
389
users_data = [
390
    ["Alice", 28],
391
    ["Bob", 32],
392
    ["Charlie", 24]
393
]
394

395
cur.executemany("INSERT INTO users VALUES (?, ?)", users_data)
396
```
397

398
### Column Metadata Access
399

400
```python
401
from trino.dbapi import connect
402

403
conn = connect(host="localhost", port=8080, user="testuser")
404
cur = conn.cursor()
405
cur.execute("SELECT * FROM users LIMIT 0")  # Get structure without data
406

407
# Access column information
408
for col in cur.description:
409
    print(f"Column: {col.name}, Type: {col.type_code}")
410
```