0
# DB-API Interface
1
2
Standard Python Database API 2.0 implementation providing Connection and Cursor objects for compatibility with existing database tools and frameworks. Enables seamless integration with applications that expect standard database interfaces.
3
4
## Capabilities
5
6
### Module Constants
7
8
DB-API 2.0 compliance constants defining the interface capabilities and parameter formatting style.
9
10
```python { .api }
11
apilevel: str = '2.0'
12
"""DB-API version level (2.0 compliance)."""
13
14
threadsafety: int = 2
15
"""Thread safety level: threads may share the module and connections."""
16
17
paramstyle: str = 'pyformat'
18
"""Parameter style: Python extended format codes (e.g., %(name)s)."""
19
```
20
21
### Connection Factory
22
23
Primary function for creating DB-API compatible connection objects with ClickHouse server configuration.
24
25
```python { .api }
26
def connect(
27
host: str | None = None,
28
database: str | None = None,
29
username: str | None = '',
30
password: str | None = '',
31
port: int | None = None,
32
secure: bool = False,
33
**kwargs
34
) -> Connection:
35
"""
36
Create a DB-API 2.0 compatible connection to ClickHouse.
37
38
Parameters:
39
- host: ClickHouse server hostname (default: localhost)
40
- database: Database name (default: default database for user)
41
- username: Username for authentication (default: 'default')
42
- password: Password for authentication
43
- port: Server port (default: 8123 for HTTP, 8443 for HTTPS)
44
- secure: Use HTTPS connection
45
- **kwargs: Additional connection parameters passed to create_client()
46
47
Returns:
48
Connection object implementing DB-API 2.0 interface
49
50
Example:
51
conn = clickhouse_connect.dbapi.connect(
52
host='localhost',
53
database='analytics',
54
username='analyst',
55
password='secret'
56
)
57
"""
58
```
59
60
### Base Exception Classes
61
62
Exception hierarchy following DB-API 2.0 specification for consistent error handling across database applications.
63
64
```python { .api }
65
class Error(Exception):
66
"""Base class for all database errors."""
67
pass
68
```
69
70
### Connection Class
71
72
DB-API 2.0 compatible connection object providing transaction management, cursor creation, and connection lifecycle methods.
73
74
```python { .api }
75
class Connection:
76
"""DB-API 2.0 compatible connection to ClickHouse database."""
77
78
def close(self):
79
"""
80
Close the connection permanently.
81
82
After calling close(), the connection object and any cursor
83
objects created from it are unusable.
84
"""
85
86
def commit(self):
87
"""
88
Commit any pending transaction.
89
90
Note: ClickHouse does not support transactions, so this is a no-op
91
for compatibility. All changes are automatically committed.
92
"""
93
94
def rollback(self):
95
"""
96
Rollback any pending transaction.
97
98
Note: ClickHouse does not support transactions, so this is a no-op
99
for compatibility. Changes cannot be rolled back.
100
"""
101
102
def cursor(self) -> Cursor:
103
"""
104
Create a new cursor object using the connection.
105
106
Returns:
107
Cursor object for executing statements and fetching results
108
"""
109
110
def command(self, cmd: str, parameters: dict | None = None) -> Any:
111
"""
112
Execute a command and return the result.
113
114
Parameters:
115
- cmd: Command string to execute
116
- parameters: Optional parameters dictionary
117
118
Returns:
119
Command result value
120
"""
121
122
def raw_query(
123
self,
124
query: str,
125
parameters: dict | None = None,
126
settings: dict | None = None,
127
fmt: str = 'Native'
128
) -> bytes:
129
"""
130
Execute raw query and return bytes result.
131
132
Parameters:
133
- query: SQL query string
134
- parameters: Query parameters
135
- settings: ClickHouse settings
136
- fmt: Output format
137
138
Returns:
139
Raw bytes response from ClickHouse
140
"""
141
```
142
143
### Cursor Class
144
145
DB-API 2.0 compatible cursor object for executing statements and fetching results with standard interface methods.
146
147
```python { .api }
148
class Cursor:
149
"""DB-API 2.0 compatible cursor for ClickHouse operations."""
150
151
# Cursor properties
152
@property
153
def description(self) -> Sequence[Sequence] | None:
154
"""
155
Column description for the last executed query.
156
157
Returns:
158
Sequence of (name, type_code, display_size, internal_size,
159
precision, scale, null_ok) tuples, or None if no query executed.
160
"""
161
162
@property
163
def rowcount(self) -> int:
164
"""
165
Number of rows affected by the last operation.
166
167
Returns:
168
Row count, or -1 if not available
169
"""
170
171
@property
172
def arraysize(self) -> int:
173
"""
174
Default number of rows to fetch at a time with fetchmany().
175
176
Default value is 1, can be modified by application.
177
"""
178
179
@arraysize.setter
180
def arraysize(self, size: int):
181
"""Set the arraysize property."""
182
183
# Cursor methods
184
def close(self):
185
"""
186
Close the cursor permanently.
187
188
After calling close(), the cursor object is unusable.
189
The cursor is automatically closed when deleted.
190
"""
191
192
def execute(
193
self,
194
operation: str,
195
parameters: dict | Sequence | None = None
196
):
197
"""
198
Execute a database operation (query or command).
199
200
Parameters:
201
- operation: SQL statement string
202
- parameters: Parameters for the operation (dict or sequence)
203
204
Supports parameter substitution using %(name)s format for dict
205
parameters or positional parameters for sequence parameters.
206
207
Example:
208
cursor.execute(
209
"SELECT * FROM users WHERE age > %(min_age)s",
210
{'min_age': 25}
211
)
212
"""
213
214
def executemany(
215
self,
216
operation: str,
217
seq_of_parameters: Sequence[dict | Sequence]
218
):
219
"""
220
Execute operation multiple times with different parameters.
221
222
Parameters:
223
- operation: SQL statement string
224
- seq_of_parameters: Sequence of parameter dicts or sequences
225
226
Equivalent to calling execute() multiple times but may be
227
optimized for batch operations.
228
229
Example:
230
cursor.executemany(
231
"INSERT INTO users (name, age) VALUES (%(name)s, %(age)s)",
232
[
233
{'name': 'Alice', 'age': 25},
234
{'name': 'Bob', 'age': 30}
235
]
236
)
237
"""
238
239
def fetchone(self) -> Sequence | None:
240
"""
241
Fetch the next row from the query result.
242
243
Returns:
244
Single row as sequence, or None if no more rows available
245
246
Example:
247
cursor.execute("SELECT name, age FROM users")
248
row = cursor.fetchone()
249
if row:
250
name, age = row
251
print(f"{name}: {age}")
252
"""
253
254
def fetchmany(self, size: int | None = None) -> Sequence[Sequence]:
255
"""
256
Fetch multiple rows from the query result.
257
258
Parameters:
259
- size: Number of rows to fetch (defaults to arraysize)
260
261
Returns:
262
Sequence of rows, each row as a sequence
263
264
Example:
265
cursor.execute("SELECT * FROM products")
266
while True:
267
rows = cursor.fetchmany(100)
268
if not rows:
269
break
270
process_batch(rows)
271
"""
272
273
def fetchall(self) -> Sequence[Sequence]:
274
"""
275
Fetch all remaining rows from the query result.
276
277
Returns:
278
Sequence of all remaining rows, each row as a sequence
279
280
Warning: Can consume large amounts of memory for big result sets.
281
Consider using fetchmany() for large queries.
282
283
Example:
284
cursor.execute("SELECT id, name FROM categories")
285
all_categories = cursor.fetchall()
286
for cat_id, cat_name in all_categories:
287
print(f"{cat_id}: {cat_name}")
288
"""
289
```
290
291
## Usage Examples
292
293
### Basic DB-API Operations
294
295
```python
296
import clickhouse_connect.dbapi
297
298
# Connect to ClickHouse
299
conn = clickhouse_connect.dbapi.connect(
300
host='localhost',
301
database='analytics',
302
username='analyst'
303
)
304
305
# Create cursor
306
cursor = conn.cursor()
307
308
# Execute query
309
cursor.execute("SELECT count() FROM events")
310
row_count = cursor.fetchone()[0]
311
print(f"Total events: {row_count}")
312
313
# Parameterized query
314
cursor.execute(
315
"SELECT event_type, count() FROM events WHERE date >= %(start_date)s GROUP BY event_type",
316
{'start_date': '2023-01-01'}
317
)
318
319
# Fetch results
320
for event_type, count in cursor.fetchall():
321
print(f"{event_type}: {count}")
322
323
# Clean up
324
cursor.close()
325
conn.close()
326
```
327
328
### Transaction-Style Operations
329
330
```python
331
import clickhouse_connect.dbapi
332
333
conn = clickhouse_connect.dbapi.connect(host='localhost')
334
cursor = conn.cursor()
335
336
try:
337
# Insert data (auto-committed in ClickHouse)
338
cursor.execute(
339
"INSERT INTO logs (timestamp, level, message) VALUES (%(ts)s, %(level)s, %(msg)s)",
340
{
341
'ts': '2023-12-01 10:00:00',
342
'level': 'INFO',
343
'msg': 'Application started'
344
}
345
)
346
347
# "Commit" (no-op for ClickHouse compatibility)
348
conn.commit()
349
print(f"Inserted {cursor.rowcount} rows")
350
351
except Exception as e:
352
# "Rollback" (no-op for ClickHouse)
353
conn.rollback()
354
print(f"Error: {e}")
355
finally:
356
cursor.close()
357
conn.close()
358
```
359
360
### Batch Operations
361
362
```python
363
import clickhouse_connect.dbapi
364
365
conn = clickhouse_connect.dbapi.connect(host='localhost')
366
cursor = conn.cursor()
367
368
# Batch insert using executemany
369
user_data = [
370
{'name': 'Alice', 'age': 25, 'city': 'NYC'},
371
{'name': 'Bob', 'age': 30, 'city': 'LA'},
372
{'name': 'Carol', 'age': 35, 'city': 'Chicago'}
373
]
374
375
cursor.executemany(
376
"INSERT INTO users (name, age, city) VALUES (%(name)s, %(age)s, %(city)s)",
377
user_data
378
)
379
380
print(f"Inserted {len(user_data)} users")
381
382
# Query with result iteration
383
cursor.execute("SELECT name, age, city FROM users ORDER BY age")
384
385
# Process results in batches
386
cursor.arraysize = 100 # Set batch size for fetchmany()
387
while True:
388
batch = cursor.fetchmany()
389
if not batch:
390
break
391
392
for name, age, city in batch:
393
print(f"{name} ({age}) from {city}")
394
395
cursor.close()
396
conn.close()
397
```
398
399
### Integration with Database Tools
400
401
```python
402
# Example with a hypothetical ORM or database tool
403
import clickhouse_connect.dbapi
404
405
def get_connection():
406
"""Factory function for database connections."""
407
return clickhouse_connect.dbapi.connect(
408
host='clickhouse.example.com',
409
database='production',
410
username='app_user',
411
password='secure_password'
412
)
413
414
class DatabaseManager:
415
"""Example database manager using DB-API interface."""
416
417
def __init__(self):
418
self.conn = get_connection()
419
420
def execute_query(self, sql, params=None):
421
"""Execute query and return all results."""
422
cursor = self.conn.cursor()
423
try:
424
cursor.execute(sql, params)
425
return cursor.fetchall(), cursor.description
426
finally:
427
cursor.close()
428
429
def execute_command(self, sql, params=None):
430
"""Execute command and return row count."""
431
cursor = self.conn.cursor()
432
try:
433
cursor.execute(sql, params)
434
return cursor.rowcount
435
finally:
436
cursor.close()
437
438
def close(self):
439
"""Close database connection."""
440
self.conn.close()
441
442
# Usage
443
db = DatabaseManager()
444
445
# Query data
446
results, description = db.execute_query(
447
"SELECT product_id, sum(quantity) as total FROM orders GROUP BY product_id"
448
)
449
450
column_names = [desc[0] for desc in description]
451
print(f"Columns: {column_names}")
452
453
for row in results:
454
print(dict(zip(column_names, row)))
455
456
db.close()
457
```
458
459
### Error Handling
460
461
```python
462
import clickhouse_connect.dbapi
463
464
conn = clickhouse_connect.dbapi.connect(host='localhost')
465
cursor = conn.cursor()
466
467
try:
468
# Invalid query
469
cursor.execute("SELECT * FROM non_existent_table")
470
471
except clickhouse_connect.dbapi.Error as e:
472
print(f"Database error: {e}")
473
474
except Exception as e:
475
print(f"Unexpected error: {e}")
476
477
finally:
478
cursor.close()
479
conn.close()
480
```
481
482
### Connection Context Manager
483
484
```python
485
import clickhouse_connect.dbapi
486
from contextlib import contextmanager
487
488
@contextmanager
489
def get_cursor():
490
"""Context manager for database cursor."""
491
conn = clickhouse_connect.dbapi.connect(host='localhost')
492
cursor = conn.cursor()
493
try:
494
yield cursor
495
finally:
496
cursor.close()
497
conn.close()
498
499
# Usage with context manager
500
with get_cursor() as cursor:
501
cursor.execute("SELECT database()")
502
current_db = cursor.fetchone()[0]
503
print(f"Current database: {current_db}")
504
505
cursor.execute("SELECT count() FROM system.tables")
506
table_count = cursor.fetchone()[0]
507
print(f"Tables in system: {table_count}")
508
```