0
# DB API 2.0 Interface
1
2
Standards-compliant database connectivity following the Python Database API specification (PEP 249). This interface provides familiar cursor-based database interaction patterns compatible with other Python database drivers, making it easy to integrate ClickHouse into existing database applications.
3
4
## Capabilities
5
6
### Connection Factory
7
8
Create database connections using standard DB API 2.0 parameters with support for both DSN and individual parameter formats.
9
10
```python { .api }
11
def connect(dsn=None, host=None, user='default', password='',
12
port=9000, database='', **kwargs):
13
"""
14
Create a new database connection.
15
16
Parameters:
17
- dsn: Data Source Name connection string
18
- host: ClickHouse server hostname
19
- user: Username for authentication
20
- password: Password for authentication
21
- port: ClickHouse server port (default: 9000)
22
- database: Default database name
23
- **kwargs: Additional connection parameters passed to underlying client
24
25
Returns:
26
- Connection: DB API 2.0 connection object
27
28
DSN Format:
29
- clickhouse://[user[:password]@]host[:port][/database][?param=value]
30
- clickhouses:// for SSL connections
31
32
Raises:
33
- ValueError: If neither dsn nor host is provided
34
"""
35
```
36
37
### Connection Class
38
39
DB API 2.0 connection wrapper providing transaction-like interface and cursor factory.
40
41
```python { .api }
42
class Connection:
43
def cursor(self, cursor_factory=None):
44
"""
45
Create a new cursor for executing queries.
46
47
Parameters:
48
- cursor_factory: Optional cursor factory function (unused)
49
50
Returns:
51
- Cursor: New cursor instance for query execution
52
"""
53
54
def close(self):
55
"""Close the connection and free resources."""
56
57
def commit(self):
58
"""
59
Commit pending transaction (no-op for ClickHouse).
60
61
Note: ClickHouse doesn't support transactions, so this is a no-op
62
for DB API 2.0 compatibility.
63
"""
64
65
def rollback(self):
66
"""
67
Rollback pending transaction (no-op for ClickHouse).
68
69
Note: ClickHouse doesn't support transactions, so this is a no-op
70
for DB API 2.0 compatibility.
71
"""
72
```
73
74
### Cursor Class
75
76
DB API 2.0 cursor for query execution with standard fetch methods and result processing.
77
78
```python { .api }
79
class Cursor:
80
def execute(self, operation, parameters=None):
81
"""
82
Execute a single query with optional parameters.
83
84
Parameters:
85
- operation: SQL query string
86
- parameters: Query parameters (dict or sequence)
87
88
Parameter Formats:
89
- Dict: {'param': value} for %(param)s placeholders
90
- Sequence: [value1, value2] for %s placeholders (pyformat style)
91
"""
92
93
def executemany(self, operation, seq_of_parameters):
94
"""
95
Execute query multiple times with different parameter sets.
96
97
Parameters:
98
- operation: SQL query string
99
- seq_of_parameters: Sequence of parameter sets
100
101
Note: Optimized for INSERT operations with multiple value sets
102
"""
103
104
def fetchone(self):
105
"""
106
Fetch single row from query results.
107
108
Returns:
109
- Tuple: Single result row, or None if no more rows
110
"""
111
112
def fetchmany(self, size=None):
113
"""
114
Fetch multiple rows from query results.
115
116
Parameters:
117
- size: Number of rows to fetch (default: cursor.arraysize)
118
119
Returns:
120
- List[Tuple]: List of result rows (may be fewer than size)
121
"""
122
123
def fetchall(self):
124
"""
125
Fetch all remaining rows from query results.
126
127
Returns:
128
- List[Tuple]: All remaining result rows
129
"""
130
131
def close(self):
132
"""Close cursor and free resources."""
133
134
def setinputsizes(self, sizes):
135
"""
136
Set input parameter sizes (no-op for compatibility).
137
138
Parameters:
139
- sizes: Parameter size specifications (ignored)
140
"""
141
142
def setoutputsize(self, size, column=None):
143
"""
144
Set output column size (no-op for compatibility).
145
146
Parameters:
147
- size: Column size specification (ignored)
148
- column: Column index (ignored)
149
"""
150
```
151
152
### Cursor Properties
153
154
Access query metadata and result information through standard DB API 2.0 properties.
155
156
```python { .api }
157
class Cursor:
158
@property
159
def description(self):
160
"""
161
Column description information.
162
163
Returns:
164
- List[Tuple]: Column metadata as (name, type_code, display_size,
165
internal_size, precision, scale, null_ok) tuples
166
167
Note: Only name and type_code are meaningful for ClickHouse
168
"""
169
170
@property
171
def rowcount(self):
172
"""
173
Number of rows affected by last operation.
174
175
Returns:
176
- int: Row count for INSERT/UPDATE/DELETE, -1 for SELECT
177
"""
178
179
@property
180
def columns_with_types(self):
181
"""
182
Column names with ClickHouse type information (non-standard).
183
184
Returns:
185
- List[Tuple]: Column information as (name, clickhouse_type) tuples
186
"""
187
```
188
189
### Non-Standard Cursor Extensions
190
191
ClickHouse-specific functionality extending standard DB API 2.0 interface.
192
193
```python { .api }
194
class Cursor:
195
def set_stream_results(self, stream_results, max_row_buffer=None):
196
"""
197
Enable streaming results for memory-efficient processing.
198
199
Parameters:
200
- stream_results: Enable streaming mode
201
- max_row_buffer: Maximum rows to buffer (optional)
202
"""
203
204
def set_settings(self, settings):
205
"""
206
Set ClickHouse-specific query settings.
207
208
Parameters:
209
- settings: Dictionary of ClickHouse settings
210
"""
211
212
def set_types_check(self, types_check):
213
"""
214
Enable strict type checking for parameters.
215
216
Parameters:
217
- types_check: Enable type validation
218
"""
219
220
def set_external_table(self, name, structure, data):
221
"""
222
Add external table for query execution.
223
224
Parameters:
225
- name: Table name for use in queries
226
- structure: List of (column_name, type) tuples
227
- data: Table data as list of tuples
228
"""
229
230
def set_query_id(self, query_id):
231
"""
232
Set unique identifier for query tracking.
233
234
Parameters:
235
- query_id: Unique query identifier string
236
"""
237
```
238
239
### Context Manager Support
240
241
Connection and cursor objects support context manager protocol for automatic resource cleanup.
242
243
```python { .api }
244
class Connection:
245
def __enter__(self):
246
"""Enter context manager."""
247
return self
248
249
def __exit__(self, exc_type, exc_val, exc_tb):
250
"""Exit context manager and close connection."""
251
self.close()
252
253
class Cursor:
254
def __enter__(self):
255
"""Enter context manager."""
256
return self
257
258
def __exit__(self, exc_type, exc_val, exc_tb):
259
"""Exit context manager and close cursor."""
260
self.close()
261
```
262
263
## DB API 2.0 Module Constants
264
265
Standard module-level constants providing interface metadata.
266
267
```python { .api }
268
apilevel = '2.0' # DB API specification level
269
threadsafety = 2 # Thread safety level (connections may be shared)
270
paramstyle = 'pyformat' # Parameter placeholder style (%(name)s)
271
```
272
273
## Usage Examples
274
275
### Basic Connection and Queries
276
277
```python
278
from clickhouse_driver import connect
279
280
# Connect with individual parameters
281
conn = connect(host='localhost', user='default', database='mydb')
282
cursor = conn.cursor()
283
284
# Execute simple query
285
cursor.execute('SELECT version()')
286
result = cursor.fetchone()
287
print(result[0])
288
289
# Execute query with parameters
290
cursor.execute('SELECT * FROM users WHERE age > %(min_age)s', {'min_age': 25})
291
rows = cursor.fetchall()
292
for row in rows:
293
print(row)
294
295
cursor.close()
296
conn.close()
297
```
298
299
### DSN Connection
300
301
```python
302
# Connect using Data Source Name
303
conn = connect('clickhouse://user:pass@localhost:9000/mydb')
304
cursor = conn.cursor()
305
306
cursor.execute('SELECT count() FROM large_table')
307
count = cursor.fetchone()[0]
308
print(f"Table has {count} rows")
309
310
cursor.close()
311
conn.close()
312
```
313
314
### Context Manager Usage
315
316
```python
317
# Automatic resource cleanup
318
with connect(host='localhost') as conn:
319
with conn.cursor() as cursor:
320
cursor.execute('SELECT * FROM system.tables LIMIT 5')
321
tables = cursor.fetchall()
322
for table in tables:
323
print(table[0]) # table name
324
# Connection and cursor automatically closed
325
```
326
327
### Bulk Insert Operations
328
329
```python
330
with connect(host='localhost') as conn:
331
with conn.cursor() as cursor:
332
# Create table
333
cursor.execute('''
334
CREATE TABLE IF NOT EXISTS test_insert (
335
id UInt32,
336
name String,
337
value Float64
338
) ENGINE = Memory
339
''')
340
341
# Bulk insert with executemany
342
data = [
343
(1, 'Alice', 3.14),
344
(2, 'Bob', 2.71),
345
(3, 'Charlie', 1.41)
346
]
347
348
cursor.executemany('INSERT INTO test_insert VALUES', data)
349
print(f"Inserted {cursor.rowcount} rows")
350
```
351
352
### Streaming Results
353
354
```python
355
with connect(host='localhost') as conn:
356
with conn.cursor() as cursor:
357
# Enable streaming for large result sets
358
cursor.set_stream_results(True, max_row_buffer=1000)
359
360
cursor.execute('SELECT * FROM large_table')
361
362
# Process results in chunks
363
while True:
364
rows = cursor.fetchmany(100)
365
if not rows:
366
break
367
368
for row in rows:
369
process_row(row)
370
```
371
372
### ClickHouse-Specific Features
373
374
```python
375
with connect(host='localhost') as conn:
376
with conn.cursor() as cursor:
377
# Set ClickHouse settings
378
cursor.set_settings({
379
'max_memory_usage': 10000000000,
380
'max_execution_time': 60
381
})
382
383
# Enable type checking
384
cursor.set_types_check(True)
385
386
# Set query ID for tracking
387
cursor.set_query_id('my_query_123')
388
389
# Add external table
390
cursor.set_external_table(
391
'temp_data',
392
[('id', 'UInt32'), ('name', 'String')],
393
[(1, 'Alice'), (2, 'Bob')]
394
)
395
396
cursor.execute('''
397
SELECT main.*, temp.name as temp_name
398
FROM main_table main
399
JOIN temp_data temp ON main.id = temp.id
400
''')
401
402
# Access column information
403
columns = cursor.columns_with_types
404
print("Columns:", columns)
405
406
results = cursor.fetchall()
407
print("Results:", results)
408
```
409
410
### Error Handling
411
412
```python
413
from clickhouse_driver import connect
414
from clickhouse_driver.dbapi.errors import DatabaseError, OperationalError
415
416
try:
417
with connect(host='localhost') as conn:
418
with conn.cursor() as cursor:
419
cursor.execute('SELECT invalid_function()')
420
421
except OperationalError as e:
422
print(f"Query execution error: {e}")
423
except DatabaseError as e:
424
print(f"Database error: {e}")
425
except Exception as e:
426
print(f"Unexpected error: {e}")
427
```