0
# DB API 2.0 Interface
1
2
Python Database API 2.0 compliant interface providing standard database connectivity patterns for chDB. This interface enables integration with existing database workflows, connection pooling, and tools that expect standard Python database behavior.
3
4
## Capabilities
5
6
### Connection Management
7
8
Create and manage database connections with standard DB API methods.
9
10
```python { .api }
11
def connect(*args, **kwargs):
12
"""
13
Create a new database connection.
14
15
Parameters:
16
- cursorclass: Custom cursor class (default: Cursor)
17
- path: Optional database path for persistent storage
18
19
Returns:
20
Connection: Database connection object
21
"""
22
23
def Connect(*args, **kwargs):
24
"""Alias for connect() function."""
25
26
Connection = connect # Alias for compatibility
27
```
28
29
### Connection Class
30
31
Standard database connection with transaction support and cursor creation.
32
33
```python { .api }
34
class Connection:
35
def __init__(self, cursorclass=None, path: str = None):
36
"""
37
Initialize database connection.
38
39
Parameters:
40
- cursorclass: Custom cursor class to use
41
- path: Optional folder path to store database files
42
"""
43
44
def close(self):
45
"""
46
Close the database connection.
47
48
Raises:
49
Error: If connection is already closed
50
"""
51
52
def commit(self):
53
"""Commit current transaction (no-op in chDB)."""
54
55
def rollback(self):
56
"""Roll back current transaction (no-op in chDB)."""
57
58
def cursor(self, cursor=None):
59
"""
60
Create a new cursor for executing queries.
61
62
Parameters:
63
- cursor: Optional cursor class to use
64
65
Returns:
66
Cursor: Database cursor object
67
"""
68
69
@property
70
def open(self) -> bool:
71
"""Return True if connection is open."""
72
```
73
74
### Cursor Operations
75
76
Execute queries and fetch results using standard cursor interface.
77
78
```python { .api }
79
class Cursor:
80
# Properties
81
max_stmt_length: int = 1024000 # Maximum statement size for executemany
82
arraysize: int = 1 # Default number of rows for fetchmany
83
84
def __init__(self, connection):
85
"""Initialize cursor with database connection."""
86
87
def execute(self, query: str, args=None):
88
"""
89
Execute a database query.
90
91
Parameters:
92
- query: SQL query string with optional placeholders (%s or %(name)s)
93
- args: Query parameters (tuple, list, or dict)
94
95
Returns:
96
int: Number of affected rows
97
"""
98
99
def executemany(self, query: str, seq_of_parameters):
100
"""
101
Execute query multiple times with different parameters.
102
Optimized for bulk INSERT and REPLACE operations.
103
104
Parameters:
105
- query: SQL query string
106
- seq_of_parameters: Sequence of parameter tuples/dicts
107
108
Returns:
109
int: Total number of affected rows
110
"""
111
112
def callproc(self, procname: str, args=tuple()):
113
"""
114
Execute stored procedure (returns original args).
115
116
Parameters:
117
- procname: Name of procedure to execute
118
- args: Sequence of parameters for procedure
119
120
Returns:
121
tuple: Original arguments (procedure outputs via server variables)
122
"""
123
124
def fetchone(self):
125
"""
126
Fetch next row of query result set.
127
128
Returns:
129
tuple: Next row or None if no more rows
130
"""
131
132
def fetchmany(self, size: int = None):
133
"""
134
Fetch multiple rows from query result set.
135
136
Parameters:
137
- size: Number of rows to fetch (uses arraysize if None)
138
139
Returns:
140
list: List of row tuples
141
"""
142
143
def fetchall(self):
144
"""
145
Fetch all remaining rows from query result set.
146
147
Returns:
148
list: List of all row tuples
149
"""
150
151
def nextset(self):
152
"""
153
Move to next result set (if multiple result sets available).
154
155
Returns:
156
bool: True if more result sets available, False otherwise
157
"""
158
159
def mogrify(self, query: str, args=None) -> str:
160
"""
161
Return exact string that would be sent to database.
162
163
Parameters:
164
- query: SQL query string with placeholders
165
- args: Parameters to substitute
166
167
Returns:
168
str: Formatted query string
169
"""
170
171
def close(self):
172
"""Close the cursor and exhaust remaining data."""
173
174
# Properties
175
@property
176
def description(self):
177
"""Column description tuple for last executed query."""
178
179
@property
180
def rowcount(self) -> int:
181
"""Number of rows affected by last operation."""
182
183
@property
184
def lastrowid(self):
185
"""ID of last inserted row."""
186
187
@property
188
def rownumber(self) -> int:
189
"""Current row number in result set."""
190
```
191
192
### Type Objects and Constants
193
194
DB API 2.0 compliant type objects for field type checking.
195
196
```python { .api }
197
# API Level Constants
198
apilevel: str = "2.0"
199
threadsafety: int = 1
200
paramstyle: str = "format"
201
202
# Type Objects
203
STRING: DBAPISet # String field types
204
BINARY: DBAPISet # Binary field types
205
NUMBER: DBAPISet # Numeric field types
206
DATE: DBAPISet # Date field types
207
TIME: DBAPISet # Time field types
208
TIMESTAMP: DBAPISet # Timestamp field types
209
DATETIME: DBAPISet # Datetime field types (alias for TIMESTAMP)
210
ROWID: DBAPISet # Row ID field types
211
212
# Special Values
213
NULL: str = "NULL"
214
```
215
216
### Utility Functions
217
218
Helper functions for type conversion and client information.
219
220
```python { .api }
221
def Binary(x):
222
"""
223
Convert value to binary type.
224
225
Parameters:
226
- x: Value to convert
227
228
Returns:
229
bytes: Binary representation
230
"""
231
232
def get_client_info() -> str:
233
"""
234
Get client version information.
235
236
Returns:
237
str: Client version string
238
"""
239
```
240
241
### Exception Classes
242
243
DB API 2.0 compliant exception hierarchy for error handling.
244
245
```python { .api }
246
class StandardError(Exception):
247
"""Base exception for chDB operations."""
248
249
class Warning(StandardError):
250
"""Exception for important warnings like data truncations."""
251
252
class Error(StandardError):
253
"""Base class for all error exceptions (not Warning)."""
254
255
class InterfaceError(Error):
256
"""Exception for database interface related errors."""
257
258
class DatabaseError(Error):
259
"""Exception for database related errors."""
260
261
class DataError(DatabaseError):
262
"""Exception for data processing errors (division by zero, etc.)."""
263
264
class OperationalError(DatabaseError):
265
"""Exception for database operational errors (disconnect, etc.)."""
266
267
class IntegrityError(DatabaseError):
268
"""Exception for relational integrity violations (foreign key, etc.)."""
269
270
class InternalError(DatabaseError):
271
"""Exception for internal database errors (invalid cursor, etc.)."""
272
273
class ProgrammingError(DatabaseError):
274
"""Exception for programming errors (table not found, syntax error, etc.)."""
275
276
class NotSupportedError(DatabaseError):
277
"""Exception for unsupported operations or database features."""
278
```
279
280
### Custom Classes
281
282
```python { .api }
283
class DBAPISet(frozenset):
284
"""Custom set class for DB API type comparisons."""
285
286
def __ne__(self, other): ...
287
def __eq__(self, other): ...
288
def __hash__(self): ...
289
```
290
291
## Usage Examples
292
293
### Basic Connection and Query
294
295
```python
296
import chdb.dbapi as dbapi
297
298
# Create connection
299
conn = dbapi.connect()
300
301
# Create cursor and execute query
302
cur = conn.cursor()
303
cur.execute('SELECT version()')
304
305
# Get column information
306
print("Columns:", cur.description)
307
308
# Fetch results
309
result = cur.fetchone()
310
print("Result:", result)
311
312
# Clean up
313
cur.close()
314
conn.close()
315
```
316
317
### Context Manager Usage
318
319
```python
320
import chdb.dbapi as dbapi
321
322
# Using connection as context manager
323
with dbapi.connect() as conn:
324
cur = conn.cursor()
325
cur.execute('SELECT 1 as id, "test" as name')
326
327
# Fetch all results
328
rows = cur.fetchall()
329
for row in rows:
330
print(row)
331
```
332
333
### Persistent Database Path
334
335
```python
336
import chdb.dbapi as dbapi
337
338
# Connect with persistent storage
339
conn = dbapi.connect(path="/tmp/my_chdb")
340
341
cur = conn.cursor()
342
343
# Create table
344
cur.execute('''
345
CREATE TABLE IF NOT EXISTS users (
346
id Int32,
347
name String,
348
email String
349
) ENGINE = Memory
350
''')
351
352
# Insert data
353
cur.execute("INSERT INTO users VALUES (1, 'Alice', 'alice@example.com')")
354
cur.execute("INSERT INTO users VALUES (2, 'Bob', 'bob@example.com')")
355
356
# Query data
357
cur.execute("SELECT * FROM users ORDER BY id")
358
users = cur.fetchall()
359
360
for user in users:
361
print(f"ID: {user[0]}, Name: {user[1]}, Email: {user[2]}")
362
363
cur.close()
364
conn.close()
365
```
366
367
### Working with Different Data Types
368
369
```python
370
import chdb.dbapi as dbapi
371
372
conn = dbapi.connect()
373
cur = conn.cursor()
374
375
# Query with various data types
376
cur.execute('''
377
SELECT
378
42 as int_val,
379
3.14 as float_val,
380
'hello' as string_val,
381
today() as date_val,
382
now() as timestamp_val
383
''')
384
385
# Check column descriptions
386
for col in cur.description:
387
print(f"Column: {col[0]}, Type: {col[1]}")
388
389
# Fetch and display data
390
row = cur.fetchone()
391
print("Values:", row)
392
393
cur.close()
394
conn.close()
395
```
396
397
### Using fetchmany()
398
399
```python
400
import chdb.dbapi as dbapi
401
402
conn = dbapi.connect()
403
cur = conn.cursor()
404
405
# Execute query that returns multiple rows
406
cur.execute('''
407
SELECT number, number * 2 as doubled
408
FROM numbers(100)
409
''')
410
411
# Fetch rows in batches
412
batch_size = 10
413
while True:
414
rows = cur.fetchmany(batch_size)
415
if not rows:
416
break
417
418
print(f"Batch of {len(rows)} rows:")
419
for row in rows:
420
print(f" {row[0]} -> {row[1]}")
421
422
cur.close()
423
conn.close()
424
```
425
426
### Client Information
427
428
```python
429
import chdb.dbapi as dbapi
430
431
# Get client version information
432
client_info = dbapi.get_client_info()
433
print(f"chDB client version: {client_info}")
434
435
# Check API level and threading support
436
print(f"API Level: {dbapi.apilevel}")
437
print(f"Thread Safety: {dbapi.threadsafety}")
438
print(f"Parameter Style: {dbapi.paramstyle}")
439
```