0
# Client Interface
1

2
The Client class provides direct access to ClickHouse's native protocol features with full control over query execution, data processing, and connection management. This interface offers the most comprehensive feature set including streaming results, progress tracking, external tables, and advanced query settings.
3

4
## Capabilities
5

6
### Client Construction
7

8
Create a Client instance with connection parameters and optional client settings for customized behavior.
9

10
```python { .api }
11
class Client:
12
    def __init__(self, host='localhost', port=9000, database='', user='default', 
13
                 password='', client_name='python-driver', connect_timeout=10,
14
                 send_receive_timeout=300, sync_request_timeout=5, 
15
                 compress_block_size=1048576, compression=False, secure=False,
16
                 verify=True, ssl_version=None, ca_certs=None, ciphers=None,
17
                 keyfile=None, certfile=None, server_hostname=None,
18
                 alt_hosts=None, settings_is_important=False, tcp_keepalive=False,
19
                 client_revision=None, settings=None, **kwargs):
20
        """
21
        Initialize ClickHouse client.
22
        
23
        Parameters:
24
        - host: ClickHouse server hostname
25
        - port: ClickHouse server port (9000 for native, 9440 for secure)
26
        - database: Default database name
27
        - user: Username for authentication
28
        - password: Password for authentication
29
        - client_name: Client identifier sent to server
30
        - connect_timeout: Connection timeout in seconds
31
        - send_receive_timeout: Socket send/receive timeout
32
        - sync_request_timeout: Synchronous request timeout
33
        - compress_block_size: Compression block size in bytes
34
        - compression: Enable compression (requires algorithm extras)
35
        - secure: Enable SSL/TLS connection
36
        - verify: Verify SSL certificates
37
        - ssl_version: SSL protocol version (None for default)
38
        - ca_certs: Path to CA certificates file
39
        - ciphers: SSL cipher suite specification
40
        - keyfile: Path to client private key file for SSL authentication
41
        - certfile: Path to client certificate file for SSL authentication
42
        - server_hostname: Expected server hostname for SSL verification
43
        - alt_hosts: Alternative hosts for failover (comma-separated)
44
        - settings_is_important: Whether settings are important for queries
45
        - tcp_keepalive: Enable TCP keepalive for connections
46
        - client_revision: ClickHouse client revision number
47
        - settings: Default query settings dictionary
48
        """
49
```
50

51
### Basic Query Execution
52

53
Execute queries with support for parameters, column type information, and various result formats.
54

55
```python { .api }
56
def execute(self, query, params=None, with_column_types=False, 
57
            external_tables=None, query_id=None, settings=None, 
58
            types_check=False, columnar=False):
59
    """
60
    Execute query and return results.
61
    
62
    Parameters:
63
    - query: SQL query string
64
    - params: Query parameters dictionary for %(name)s substitution
65
    - with_column_types: Return column names and types with results
66
    - external_tables: List of external table definitions
67
    - query_id: Unique query identifier for tracking
68
    - settings: Query-specific ClickHouse settings
69
    - types_check: Enable strict type checking for parameters
70
    - columnar: Return results in columnar format
71
    
72
    Returns:
73
    - List of result rows (tuples), or
74
    - Tuple of (column_info, rows) if with_column_types=True
75
    """
76
```
77

78
### Streaming Query Execution
79

80
Execute queries with streaming results for memory-efficient processing of large datasets.
81

82
```python { .api }
83
def execute_iter(self, query, params=None, with_column_types=False,
84
                 external_tables=None, query_id=None, settings=None,
85
                 types_check=False):
86
    """
87
    Execute query and return streaming iterator.
88
    
89
    Parameters: Same as execute()
90
    
91
    Returns:
92
    - IterQueryResult: Iterator yielding result blocks
93
    
94
    Yields:
95
    - Blocks of rows for memory-efficient processing
96
    """
97
```
98

99
### Progress Tracking Execution
100

101
Execute queries with progress information for long-running operations monitoring.
102

103
```python { .api }
104
def execute_with_progress(self, query, params=None, with_column_types=False,
105
                          external_tables=None, query_id=None, settings=None,
106
                          types_check=False):
107
    """
108
    Execute query with progress tracking.
109
    
110
    Parameters: Same as execute()
111
    
112
    Returns:
113
    - ProgressQueryResult: Generator yielding (progress, data) tuples
114
    
115
    Yields:
116
    - Tuple of (progress_info, result_block) during execution
117
    """
118
```
119

120
### DataFrame Integration
121

122
Query and insert pandas DataFrames for data science workloads (requires pandas installation).
123

124
```python { .api }
125
def query_dataframe(self, query, params=None, external_tables=None,
126
                    query_id=None, settings=None, replace_nonwords=True):
127
    """
128
    Execute query and return pandas DataFrame.
129
    
130
    New in version 0.2.0.
131
    
132
    Parameters:
133
    - query: SQL query string
134
    - params: Query parameters dictionary for %(name)s substitution
135
    - external_tables: List of external table definitions
136
    - query_id: Unique query identifier for tracking
137
    - settings: Query-specific ClickHouse settings
138
    - replace_nonwords: Replace non-word characters in column names with underscores
139
    
140
    Returns:
141
    - pandas.DataFrame: Query results as DataFrame with typed columns
142
    
143
    New in version 0.2.0.
144
    
145
    Requires:
146
    - pandas package installation (pip install pandas)
147
    
148
    Raises:
149
    - RuntimeError: If pandas is not installed
150
    """
151

152
def insert_dataframe(self, query, dataframe, external_tables=None,
153
                     query_id=None, settings=None):
154
    """
155
    Insert pandas DataFrame data into ClickHouse table.
156
    
157
    New in version 0.2.0.
158
    
159
    Parameters:
160
    - query: INSERT query string
161
    - dataframe: pandas DataFrame with data to insert
162
    - external_tables: List of external table definitions
163
    - query_id: Unique query identifier for tracking
164
    - settings: Query-specific ClickHouse settings
165
    
166
    Returns:
167
    - int: Number of inserted rows
168
    
169
    Requires:
170
    - pandas package installation (pip install pandas)
171
    
172
    Raises:
173
    - RuntimeError: If pandas is not installed
174
    - ValueError: If DataFrame is missing required columns
175
    """
176
```
177

178
### Connection Management
179

180
Manage client connection lifecycle and query cancellation.
181

182
```python { .api }
183
def disconnect(self):
184
    """Disconnect from ClickHouse server."""
185

186
def cancel(self, with_column_types=False):
187
    """
188
    Cancel currently executing query.
189
    
190
    Parameters:
191
    - with_column_types: Include column information in response
192
    
193
    Returns:
194
    - Cancel operation result
195
    """
196
```
197

198
### URL-based Construction
199

200
Create clients from connection URLs for convenient configuration.
201

202
```python { .api }
203
@classmethod
204
def from_url(cls, url):
205
    """
206
    Create client from connection URL.
207
    
208
    Parameters:
209
    - url: Connection URL (clickhouse://user:password@host:port/database?param=value)
210
    
211
    Returns:
212
    - Client: Configured client instance
213
    
214
    URL Format:
215
    - clickhouse://[user[:password]@]host[:port][/database][?param1=value1&param2=value2]
216
    - clickhouses:// for SSL connections
217
    """
218
```
219

220
### Parameter Substitution
221

222
Advanced parameter handling for complex query construction.
223

224
```python { .api }
225
def substitute_params(self, query, params, context):
226
    """
227
    Substitute parameters in query string.
228
    
229
    Parameters:
230
    - query: SQL query with %(name)s placeholders
231
    - params: Parameters dictionary
232
    - context: Execution context information
233
    
234
    Returns:
235
    - str: Query with substituted parameters
236
    """
237
```
238

239
### Context Manager Support
240

241
Use client as context manager for automatic connection cleanup.
242

243
```python { .api }
244
def __enter__(self):
245
    """Enter context manager."""
246
    return self
247

248
def __exit__(self, exc_type, exc_val, exc_tb):
249
    """Exit context manager and disconnect."""
250
    self.disconnect()
251
```
252

253
## Client Settings
254

255
Configure client behavior through settings dictionary passed to constructor or individual queries:
256

257
### Data Processing Settings
258

259
```python { .api }
260
# Client-specific settings (passed in settings parameter)
261
settings = {
262
    'insert_block_size': 1048576,           # Chunk size for INSERT operations
263
    'strings_as_bytes': False,              # Disable string encoding/decoding
264
    'strings_encoding': 'utf-8',            # String encoding (default: UTF-8)
265
    'use_numpy': False,                     # Enable NumPy for column processing
266
    'input_format_null_as_default': False,  # Initialize null fields with defaults
267
    'namedtuple_as_json': False,            # Named tuple JSON deserialization
268
    'server_side_params': False,            # Server-side parameter rendering
269
}
270
```
271

272
### Observability Settings
273

274
```python { .api }
275
# OpenTelemetry integration
276
settings = {
277
    'opentelemetry_traceparent': 'trace_header_value',  # W3C trace context parent
278
    'opentelemetry_tracestate': 'trace_state_value',    # W3C trace context state
279
    'quota_key': 'user_quota_key',                      # Quota differentiation
280
}
281
```
282

283
### Connection Settings
284

285
```python { .api }
286
# High availability settings
287
settings = {
288
    'round_robin': True,  # Round-robin host selection for alt_hosts
289
}
290
```
291

292
## Usage Examples
293

294
### Basic Query Execution
295

296
```python
297
from clickhouse_driver import Client
298

299
client = Client('localhost')
300

301
# Simple query
302
result = client.execute('SELECT 1')
303
print(result)  # [(1,)]
304

305
# Query with parameters
306
result = client.execute(
307
    'SELECT * FROM users WHERE age > %(min_age)s',
308
    {'min_age': 18}
309
)
310

311
# Query with column information
312
columns, rows = client.execute(
313
    'SELECT name, age FROM users LIMIT 5', 
314
    with_column_types=True
315
)
316
print(columns)  # [('name', 'String'), ('age', 'UInt8')]
317
```
318

319
### Streaming Large Results
320

321
```python
322
# Process large result sets efficiently
323
for block in client.execute_iter('SELECT * FROM large_table'):
324
    for row in block:
325
        process_row(row)
326
```
327

328
### Progress Tracking
329

330
```python
331
# Monitor long-running queries
332
query = 'SELECT count() FROM huge_table GROUP BY category'
333
for progress, block in client.execute_with_progress(query):
334
    if progress:
335
        print(f"Processed: {progress.rows} rows")
336
    if block:
337
        process_results(block)
338
```
339

340
### External Tables
341

342
```python
343
# Use external data in queries
344
external_data = [
345
    ('id', [1, 2, 3]),
346
    ('name', ['Alice', 'Bob', 'Charlie'])
347
]
348

349
external_table = {
350
    'name': 'temp_users',
351
    'structure': [('id', 'UInt32'), ('name', 'String')],
352
    'data': external_data
353
}
354

355
result = client.execute(
356
    'SELECT * FROM temp_users WHERE id IN (SELECT id FROM main_users)',
357
    external_tables=[external_table]
358
)
359
```
360

361
### Connection URL
362

363
```python
364
# Connect using URL
365
client = Client.from_url('clickhouse://user:pass@server:9000/mydb?secure=1')
366

367
# SSL connection
368
client = Client.from_url('clickhouses://user:pass@server:9440/mydb')
369
```
370

371
### Context Manager Usage
372

373
```python
374
# Automatic connection cleanup
375
with Client('localhost') as client:
376
    result = client.execute('SELECT version()')
377
    print(result)
378
# Connection automatically closed
379
```