> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.
> ClickHouse Connect driver API
# ClickHouse Connect driver API
Pass arguments by keyword for client factories and methods with many optional parameters.
*Methods not documented here aren't considered part of the API, and may be removed or changed.*
Client initialization
Use `clickhouse_connect.get_client` to create a synchronous `Client`, or install the `async` extra and await `clickhouse_connect.get_async_client` to create a native `AsyncClient`.
Connection arguments
| Parameter | Type | Default | Description |
| -------------------------- | ----------------------------- | ------------------------------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `interface` | str | `"http"` | `"http"` or `"https"`. The synchronous factory also accepts the experimental `"chdb"` backend. |
| `host` | str | `"localhost"` | ClickHouse server hostname or IP address. |
| `port` | int or None | `8123` or `8443` | Defaults to 8123 for HTTP and 8443 for HTTPS. Passing `None` requests the default. |
| `username` | str or None | `"default"` | ClickHouse user name. The aliases `user` and `user_name` are also accepted. |
| `password` | str | `""` | Password for `username`. Do not combine user/password authentication with token authentication. |
| `access_token` | str or None | `None` | ClickHouse Cloud JWT access token. Mutually exclusive with `token_provider` and user/password authentication. |
| `token_provider` | callable or None | `None` | Callable that supplies a JWT initially and after an authentication rejection. An async provider may be used with `get_async_client`. |
| `database` | str or None | User default | Default database. Passing `None` requests the server default for the user. |
| `secure` | bool or str | `False` | Enable HTTPS/TLS. `interface="https"` also selects HTTPS, as does port 443 or 8443 when `interface` is not set. |
| `dsn` | str or None | `None` | Connection URL. Explicit keyword arguments take precedence over values parsed from the DSN. Percent-encode reserved characters in credentials and database names. |
| `settings` | dict or None | `None` | ClickHouse settings applied to every request made by the client. |
| `headers` | dict or None | `None` | HTTP headers applied to every request, including client initialization. User headers are applied after driver defaults and can override them. |
| `compress` | bool or str | `True` | Enable compression or select `"lz4"`, `"zstd"`, `"br"`, or `"gzip"`. See [Compression](/docs/integrations/language-clients/python/additional-options#compression). |
| `query_limit` | int | `0` | Default row limit appended to eligible queries. Zero means unlimited. Stream large results instead of materializing them all in memory. |
| `query_retries` | int | `2` | Retry budget for retryable read failures. Commands and inserts are not generally retried because replay can duplicate side effects. |
| `connect_timeout` | int | `10` | Connection timeout in seconds. |
| `send_receive_timeout` | int | `300` | Socket read timeout in seconds. |
| `client_name` | str or None | `None` | Prefix added to the HTTP User-Agent for identification in `system.query_log`. |
| `session_id` | str or None | Generated for sync | Explicit ClickHouse session ID. Synchronous clients generate one by default; async clients do not. |
| `autogenerate_session_id` | bool or None | Global setting for sync, `False` for async | Override automatic session ID generation. Disable it on a client shared by concurrent operations unless session state is required. |
| `autogenerate_query_id` | bool or None | Global setting, `True` | Override automatic UUID query ID generation. |
| `http_proxy` | str or None | Environment/default | Per-client HTTP proxy address. |
| `https_proxy` | str or None | Environment/default | Per-client HTTPS proxy address. |
| `pool_mgr` | `urllib3.PoolManager` or None | Shared default | Custom pool manager for the synchronous client only. |
| `tz_source` | str or None | `"auto"` | Fallback timezone source for columns without timezone metadata: `"auto"`, `"server"`, or `"local"`. |
| `tz_mode` | str or None | `"naive_utc"` | UTC result policy: `"naive_utc"`, `"aware"`, or `"schema"`. See [Time zones](/docs/integrations/language-clients/python/advanced-querying#time-zones). |
| `show_clickhouse_errors` | bool or None | `True` | Include server error details and symbolic exception names in client exceptions. |
| `proxy_path` | str | `""` | Path prefix added to the server URL when routing through a proxy. |
| `form_encode_query_params` | bool | `False` | Always place query parameters in the form-encoded request body. Large non-binary parameter payloads are moved automatically even when this is false. |
| `rename_response_column` | str or None | `None` | Column renaming strategy: `"remove_prefix"`, `"to_camelcase"`, `"to_camelcase_without_prefix"`, `"to_underscore"`, or `"to_underscore_without_prefix"`. |
The async factory also accepts `connector_limit=100`, `connector_limit_per_host=20`, and `keepalive_timeout=30.0` to configure its aiohttp connection pool. It does not accept `pool_mgr`. The synchronous chDB backend accepts `path` and `chdb_options`; see [Embedded chDB backend](#embedded-chdb-backend).
HTTPS/TLS arguments
| Parameter | Type | Default | Description |
| ------------------ | ----------- | ------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `verify` | bool or str | `True` | Validate the server certificate and hostname. `verify="proxy"` enables proxy TLS mode. |
| `ca_cert` | str or None | `None` | CA bundle path. Use `"certifi"` to select the bundle shipped by the `certifi` package. |
| `client_cert` | str or None | `None` | PEM client certificate, including intermediates when required. |
| `client_cert_key` | str or None | `None` | Private key path when the key is not included in `client_cert`. |
| `server_host_name` | str or None | `None` | TLS certificate/SNI hostname when it differs from `host`, such as through a tunnel or private endpoint. |
| `tls_mode` | str or None | `None` | `"mutual"` uses ClickHouse mutual TLS authentication. `"proxy"` and `"strict"` send the certificate at the TLS layer without enabling ClickHouse certificate authentication headers. The default `None` behaves as `"mutual"` when a client certificate is provided. |
Settings argument
Finally, the `settings` argument to `get_client` is used to pass additional ClickHouse settings to the server for each client request. Note that in most cases, users with *readonly*=*1* access can't alter settings sent with a query, so ClickHouse Connect will drop such settings in the final request and log a warning. The following settings apply only to HTTP queries/sessions used by ClickHouse Connect, and aren't documented as general ClickHouse settings.
| Setting | Description |
| ------------------------- | ------------------------------------------------------------------------------------------------------------------- |
| `buffer_size` | Server-side HTTP response buffer size in bytes. |
| `session_id` | Session ID used to associate related requests. Required for temporary tables and session state. |
| `compress` | Ask the server to compress an HTTP response. Normally managed by the client compression option. |
| `decompress` | Tell the server to decompress the request body. Used for pre-compressed raw inserts. |
| `quota_key` | Quota key associated with the request. |
| `session_check` | Ask the server to validate that a session exists. |
| `session_timeout` | Session inactivity timeout in seconds. |
| `wait_end_of_query` | Buffer the complete response on the server. The client sets this when needed for non-streaming summary information. |
| `query_id` | Explicit query ID for the request. |
| `client_protocol_version` | Native-format client protocol capability level. Normally negotiated automatically. |
| `role` | ClickHouse role to use for the request/session. |
For other ClickHouse settings that can be sent with each query, see [the ClickHouse documentation](/docs/reference/settings/session-settings).
Client creation examples
* Without any parameters, a ClickHouse Connect client will connect to the default HTTP port on `localhost` with the default user and no password:
```python theme={null}
import clickhouse_connect
client = clickhouse_connect.get_client()
print(client.server_version)
```
* Connecting to a secure (HTTPS) external ClickHouse server
```python theme={null}
import clickhouse_connect
client = clickhouse_connect.get_client(
host="play.clickhouse.com",
secure=True,
port=443,
username="play",
password="clickhouse",
)
print(client.command("SELECT timezone()"))
```
* Connecting with a session ID and other custom connection parameters and ClickHouse settings.
```python theme={null}
import clickhouse_connect
client = clickhouse_connect.get_client(
host="play.clickhouse.com",
username="play",
password="clickhouse",
port=443,
secure=True,
session_id="example_session_1",
connect_timeout=15,
database="github",
settings={"distributed_ddl_task_timeout": 300},
)
print(client.database)
# Output: github
```
Embedded chDB backend
Install `clickhouse-connect[chdb]` to use the experimental in-process chDB backend. It exposes the synchronous client query, insert, streaming, and Arrow methods:
```python theme={null}
import clickhouse_connect
with clickhouse_connect.get_client(interface="chdb") as client:
result = client.query("SELECT sum(number) FROM numbers(10)")
print(result.first_row)
# Output: (45,)
```
The default is an in-memory database. Pass `path="/data/my_chdb"` or use `dsn="chdb:///data/my_chdb"` for persistent storage. The backend allows one engine path per process and does not support `get_async_client` or external data.
Client lifecycle and best practices
Creating a ClickHouse Connect client is an expensive operation that involves establishing a connection, retrieving server metadata, and initializing settings. Follow these best practices for optimal performance:
Core principles
* **Reuse clients**: Create clients once at application startup and reuse them throughout the application lifetime
* **Avoid frequent creation**: Don't create a new client for each query or request
* **Clean up properly**: Always close clients when shutting down to release connection pool resources
* **Share when possible**: A single client can handle many concurrent queries through its connection pool (see threading notes below)
Basic patterns
Reuse a single client:
```python theme={null}
import clickhouse_connect
# Create once at startup
client = clickhouse_connect.get_client(
host="my-host",
username="default",
password="password",
)
# Reuse for all queries
for i in range(1000):
result = client.query("SELECT count() FROM users")
# Close on shutdown
client.close()
```
Avoid creating clients repeatedly:
```python theme={null}
# BAD: Creates 1000 clients with expensive initialization overhead
for i in range(1000):
client = clickhouse_connect.get_client(
host="my-host",
username="default",
password="password",
)
result = client.query("SELECT count() FROM users")
client.close()
```
Multi-threaded applications
Client instances are **NOT thread-safe** when using session IDs. By default, clients have an auto-generated session ID, and concurrent queries within the same session will raise a `ProgrammingError`.
To share a client across threads safely:
```python theme={null}
import clickhouse_connect
import threading
# Option 1: Disable sessions (recommended for shared clients)
client = clickhouse_connect.get_client(
host="my-host",
username="default",
password="password",
autogenerate_session_id=False,
)
def worker(thread_id):
# All threads can now safely use the same client
result = client.query(f"SELECT {thread_id}")
print(f"Thread {thread_id}: {result.result_rows[0][0]}")
threads = [threading.Thread(target=worker, args=(i,)) for i in range(10)]
for t in threads:
t.start()
for t in threads:
t.join()
client.close()
```
**Alternative for sessions:** If you need sessions (e.g., for temporary tables), create a separate client per thread:
```python theme={null}
def worker(thread_id):
# Each thread gets its own client with isolated session
client = clickhouse_connect.get_client(
host="my-host",
username="default",
password="password",
)
client.command("CREATE TEMPORARY TABLE temp (id UInt32) ENGINE = Memory")
# ... use temp table ...
client.close()
```
Proper cleanup
Always close clients at shutdown. Note that `client.close()` disposes the client and closes pooled HTTP connections only when the client owns its pool manager (for example, when created with custom TLS/proxy options). For the default shared pool, use `client.close_connections()` to proactively clear sockets; otherwise, connections are reclaimed automatically via idle expiration and at process exit.
```python theme={null}
client = clickhouse_connect.get_client(
host="my-host",
username="default",
password="password",
)
try:
result = client.query("SELECT 1")
finally:
client.close()
```
Or use a context manager:
```python theme={null}
with clickhouse_connect.get_client(
host="my-host",
username="default",
password="password",
) as client:
result = client.query("SELECT 1")
```
When to use multiple clients
Multiple clients are appropriate for:
* **Different servers**: One client per ClickHouse server or cluster
* **Different credentials**: Separate clients for different users or access levels
* **Different databases**: When you need to work with multiple databases
* **Isolated sessions**: When you need separate sessions for temporary tables or session-specific settings
* **Per-thread isolation**: When threads need independent sessions (as shown above)
Common method arguments
Several client methods use one or both of the common `parameters` and `settings` arguments. These keyword arguments are described below.
Parameters argument
ClickHouse Connect Client `query*` and `command` methods accept an optional `parameters` keyword argument used for binding Python expressions to a ClickHouse value expression. Two sorts of binding are available.
Server-side binding
ClickHouse supports [server-side binding](/docs/concepts/features/interfaces/client#cli-queries-with-parameters) for query values. The bound value is sent separately from the query as an HTTP parameter. ClickHouse Connect uses this mode when it detects an expression of the form `{:}`. Pass the values as a Python dictionary.
* Server-side binding with Python dictionary, DateTime value, and string value
```python theme={null}
import datetime
my_date = datetime.datetime(2022, 10, 1, 15, 20, 5)
parameters = {
"table": "my_table",
"v1": my_date,
"v2": "a string with a single quote'",
}
client.query(
"SELECT * FROM {table:Identifier} "
"WHERE date >= {v1:DateTime} AND string ILIKE {v2:String}",
parameters=parameters,
)
```
This is equivalent to:
```sql theme={null}
SELECT *
FROM my_table
WHERE date >= '2022-10-01 15:20:05'
AND string ILIKE 'a string with a single quote\''
```
Server-side binding is supported for `SELECT` queries. It doesn't work for `ALTER`, `DELETE`, `INSERT`, or other statement types.
Client-side binding
ClickHouse Connect also supports client-side parameter binding, which can allow more flexibility in generating templated SQL queries. For client-side binding, the `parameters` argument should be a dictionary or a sequence. Client-side binding uses the Python ["printf" style](https://docs.python.org/3/library/stdtypes.html#old-string-formatting) string formatting for parameter substitution.
Note that unlike server-side binding, client-side binding doesn't work for database identifiers such as database, table, or column names, since Python-style formatting can't distinguish between the different types of strings, and they need to be formatted differently (backticks or double quotes for database identifiers, single quotes for data values).
* Example with Python Dictionary, DateTime value and string escaping
```python theme={null}
import datetime
my_date = datetime.datetime(2022, 10, 1, 15, 20, 5)
parameters = {"v1": my_date, "v2": "a string with a single quote'"}
client.query(
"SELECT * FROM my_table "
"WHERE date >= %(v1)s AND string ILIKE %(v2)s",
parameters=parameters,
)
```
This generates the following query on the server:
```sql theme={null}
SELECT *
FROM my_table
WHERE date >= '2022-10-01 15:20:05'
AND string ILIKE 'a string with a single quote\''
```
* Example with Python Sequence (Tuple), Float64, and IPv4Address
```python theme={null}
import ipaddress
parameters = (35200.44, ipaddress.IPv4Address(0x443d04fe))
client.query(
"SELECT * FROM some_table WHERE metric >= %s AND ip_address = %s",
parameters=parameters,
)
```
This generates the following query on the server:
```sql theme={null}
SELECT *
FROM some_table
WHERE metric >= 35200.44
AND ip_address = '68.61.4.254'
```
For a server-side `{value:DateTime64(precision)}` placeholder, the declared type preserves sub-second precision automatically, including inside `Array` and `Tuple` hints.
Client-side `%s` binding has no declared type. Wrap a `datetime` in `DT64Param` when it must render with sub-second precision:
```python theme={null}
from datetime import datetime
from clickhouse_connect.driver.binding import DT64Param
query = "SELECT toDateTime64(%s, 6)"
parameters = [DT64Param(datetime.now())]
client.query(query, parameters=parameters)
```
For backward compatibility, a dictionary parameter name ending in `_64` also requests DateTime64 formatting when that exact suffixed name is not present in the query.
Settings argument
All the key ClickHouse Connect Client "insert" and "select" methods accept an optional `settings` keyword argument to pass ClickHouse server [user settings](/docs/reference/settings/session-settings) for the included SQL statement. The `settings` argument should be a dictionary. Each item should be a ClickHouse setting name and its associated value. Note that values will be converted to strings when sent to the server as query parameters.
As with client level settings, ClickHouse Connect will drop any settings that the server marks as *readonly*=*1*, with an associated log message. Settings that apply only to queries via the ClickHouse HTTP interface are always valid. Those settings are described under the `get_client` [API](#settings-argument).
Example of using ClickHouse settings:
```python theme={null}
settings = {
"merge_tree_min_rows_for_concurrent_read": 65535,
"session_id": "session_1234",
"use_skip_indexes": False,
}
client.query(
"SELECT event_type, sum(timeout) "
"FROM event_errors WHERE event_time > '2022-08-01'",
settings=settings,
)
```
Client `command` method
Use `Client.command` for statements that don't return a tabular dataset, or for queries that return one primitive value or one row. Depending on the response, it returns a string, integer, sequence of strings, or `QuerySummary`. A read that produces an empty result set returns an empty string.
| Parameter | Type | Default | Description |
| ------------------- | ---------------- | ---------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| cmd | str | *Required* | A ClickHouse SQL statement that returns a single value or a single row of values. |
| parameters | dict or sequence | *None* | See [parameters description](#parameters-argument). |
| data | str or bytes | *None* | Optional data to include with the command as the POST body. |
| settings | dict | *None* | See [settings description](#settings-argument-1). |
| use\_database | bool | True | Use the client database (specified when creating the client). False means the command will use the default ClickHouse server database for the connected user. |
| external\_data | ExternalData | *None* | An `ExternalData` object containing file or binary data to use with the query. See [Advanced Queries (External Data)](/docs/integrations/language-clients/python/advanced-querying#external-data) |
| transport\_settings | dict | *None* | Optional dictionary of HTTP headers to include with this request. Each key-value pair is added as an HTTP header (e.g., `{'X-Custom-Header': 'value'}`). Useful for proxy authentication, request tracing, or passing headers required by intermediate infrastructure. |
Command examples
DDL statements
```python theme={null}
import clickhouse_connect
client = clickhouse_connect.get_client()
# Create a table. A successful DDL returns QuerySummary.
summary = client.command(
"CREATE TABLE test_command "
"(col_1 String, col_2 DateTime) "
"ENGINE MergeTree ORDER BY tuple()"
)
print(summary.query_id())
# Show table definition
result = client.command("SHOW CREATE TABLE test_command")
print(result)
# Output:
# CREATE TABLE default.test_command
# (
# `col_1` String,
# `col_2` DateTime
# )
# ENGINE = MergeTree
# ORDER BY tuple()
# Drop table
client.command("DROP TABLE test_command")
```
Simple queries returning single values
```python theme={null}
import clickhouse_connect
client = clickhouse_connect.get_client()
# Single value result
count = client.command("SELECT count() FROM system.tables")
print(count)
# Server version
version = client.command("SELECT version()")
print(version)
```
Commands with parameters
```python theme={null}
import clickhouse_connect
client = clickhouse_connect.get_client()
# Using client-side parameters
table_name = "system"
result = client.command(
"SELECT count() FROM system.tables WHERE database = %(db)s",
parameters={"db": table_name}
)
# Using server-side parameters
result = client.command(
"SELECT count() FROM system.tables WHERE database = {db:String}",
parameters={"db": "system"}
)
```
Commands with settings
```python theme={null}
import clickhouse_connect
client = clickhouse_connect.get_client()
# Execute command with specific settings
result = client.command(
"OPTIMIZE TABLE large_table FINAL",
settings={"optimize_throw_if_noop": 1}
)
```
Client `query` method
`Client.query` retrieves a tabular dataset in ClickHouse Native format and returns a `QueryResult`. The complete result is materialized when a result property is accessed. Use a streaming method for results that should not be held in memory.
| Parameter | Type | Default | Description |
| -------------------- | ---------------- | -------------- | ----------------------------------------------------------------------------------------------------------------------------------------- |
| `query` | str | Required | ClickHouse query that returns a tabular result, most often `SELECT` or `DESCRIBE`. May be omitted when supplied by `context`. |
| `parameters` | dict or sequence | `None` | See [Parameters argument](#parameters-argument). |
| `settings` | dict | `None` | See [Settings argument](#settings-argument-1). |
| `query_formats` | dict | `None` | Read format by ClickHouse type. See [Read formats](/docs/integrations/language-clients/python/advanced-querying#read-formats). |
| `column_formats` | dict | `None` | Read format by result column, including nested type format mappings. |
| `encoding` | str | `None` | String column encoding. Defaults to UTF-8. |
| `use_none` | bool | `True` | Return `None` for SQL NULL. When false, return the type's default null value. NumPy/Pandas methods choose performance-oriented defaults. |
| `column_oriented` | bool | `False` | Orient the result as columns instead of rows. |
| `use_numpy` | bool | `False` | Read compatible result columns into NumPy arrays inside the `QueryResult`. Prefer `query_np` when the desired result is one NumPy matrix. |
| `max_str_len` | int | `0` | With `use_numpy`, use a fixed-width Unicode dtype for String columns up to this length. Zero uses object arrays. |
| `context` | `QueryContext` | `None` | Reusable query context. Explicit method arguments override context values. |
| `query_tz` | str or `tzinfo` | `None` | Timezone applied to all `DateTime` and `DateTime64` result columns. |
| `column_tzs` | dict | `None` | Per-column timezone mapping. |
| `external_data` | `ExternalData` | `None` | External file or binary data. See [External data](/docs/integrations/language-clients/python/advanced-querying#external-data). |
| `transport_settings` | dict | `None` | HTTP headers added to this request. |
| `tz_mode` | str | Client default | Per-query override for `"naive_utc"`, `"aware"`, or `"schema"` timezone handling. |
Query examples
Basic query
```python theme={null}
import clickhouse_connect
client = clickhouse_connect.get_client()
# Simple SELECT query
result = client.query(
"SELECT number, toString(number) AS label FROM numbers(3)"
)
# Access results as rows
for row in result.result_rows:
print(row)
# Output:
# (0, '0')
# (1, '1')
# (2, '2')
# Access column names and types
print(result.column_names)
# Output: ('number', 'label')
print([col_type.name for col_type in result.column_types])
# Output: ['UInt64', 'String']
```
Accessing query results
```python theme={null}
import clickhouse_connect
client = clickhouse_connect.get_client()
result = client.query("SELECT number, toString(number) AS str FROM system.numbers LIMIT 3")
# Row-oriented access (default)
print(result.result_rows)
# Output: [(0, '0'), (1, '1'), (2, '2')]
# Column-oriented access
print(result.result_columns)
# Output: [[0, 1, 2], ['0', '1', '2']]
# Named results (list of dictionaries)
for row_dict in result.named_results():
print(row_dict)
# Output:
# {'number': 0, 'str': '0'}
# {'number': 1, 'str': '1'}
# {'number': 2, 'str': '2'}
# First row as dictionary
print(result.first_item)
# Output: {'number': 0, 'str': '0'}
# First row as tuple
print(result.first_row)
# Output: (0, '0')
```
Query with client-side parameters
```python theme={null}
import clickhouse_connect
client = clickhouse_connect.get_client()
# Using dictionary parameters (printf-style)
query = "SELECT * FROM system.tables WHERE database = %(db)s AND name LIKE %(pattern)s"
parameters = {"db": "system", "pattern": "%query%"}
result = client.query(query, parameters=parameters)
# Using tuple parameters
query = "SELECT * FROM system.tables WHERE database = %s LIMIT %s"
parameters = ("system", 5)
result = client.query(query, parameters=parameters)
```
Query with server-side parameters
```python theme={null}
import clickhouse_connect
client = clickhouse_connect.get_client()
# Server-side binding (more secure, better performance for SELECT queries)
query = "SELECT * FROM system.tables WHERE database = {db:String} AND name = {tbl:String}"
parameters = {"db": "system", "tbl": "query_log"}
result = client.query(query, parameters=parameters)
```
Query with settings
```python theme={null}
import clickhouse_connect
client = clickhouse_connect.get_client()
# Pass ClickHouse settings with the query
result = client.query(
"SELECT sum(number) FROM numbers(1000000)",
settings={
"max_block_size": 100000,
"max_execution_time": 30
}
)
```
The `QueryResult` object
The base `query` method returns a `QueryResult` object with the following public properties:
* `result_rows` -- Result matrix oriented as rows.
* `result_columns` -- Result matrix oriented as columns.
* `result_set` -- `result_rows` or `result_columns`, according to the query orientation.
* `column_names` -- Tuple of result column names.
* `column_types` -- Tuple of `ClickHouseType` objects.
* `row_count` -- Number of materialized result rows.
* `query_id` -- Query ID reported or generated for the request. An empty string means none was available.
* `summary` -- Dictionary decoded from the `X-ClickHouse-Summary` response header.
* `first_item` -- First row as a dictionary, or `None` for an empty result.
* `first_row` -- First row as a sequence, or `None` for an empty result.
* `column_block_stream`, `row_block_stream`, and `rows_stream` -- Internal stream contexts. Use the corresponding client streaming methods instead.
See [Streaming queries](/docs/integrations/language-clients/python/advanced-querying#streaming-queries) for the supported `StreamContext` APIs.
Consuming query results with NumPy, Pandas or Arrow
ClickHouse Connect provides specialized query methods for NumPy, Pandas, and Arrow data formats. For detailed information on using these methods, including examples, streaming capabilities, and advanced type handling, see [Advanced Querying (NumPy, Pandas and Arrow Queries)](/docs/integrations/language-clients/python/advanced-querying#numpy-pandas-and-arrow-queries).
Client streaming query methods
For streaming large result sets, ClickHouse Connect provides multiple streaming methods. See [Advanced Queries (Streaming Queries)](/docs/integrations/language-clients/python/advanced-querying#streaming-queries) for details and examples.
Client `insert` method
For the common use case of inserting multiple records into ClickHouse, there is the `Client.insert` method. It takes the following parameters:
| Parameter | Type | Default | Description |
| -------------------- | --------------------------- | --------------- | ----------------------------------------------------------------------------------------------------------------------- |
| `table` | str | Required | Target table. A database-qualified name is permitted. May be omitted when supplied by `context`. |
| `data` | Sequence of Sequences | Required | Row-oriented or column-oriented data matrix. May be supplied later through an `InsertContext`. |
| `column_names` | str or Sequence\[str] | `"*"` | Ordered columns. `"*"` runs a metadata query to discover every insertable column. |
| `database` | str or None | Client database | Target database when `table` is not qualified. |
| `column_types` | Sequence\[`ClickHouseType`] | `None` | Explicit column types. Avoids the metadata query when supplied. |
| `column_type_names` | Sequence\[str] | `None` | Explicit ClickHouse type names. Alternative to `column_types`. |
| `column_oriented` | bool | `False` | Interpret `data` as columns instead of rows. |
| `settings` | dict | `None` | See [Settings argument](#settings-argument-1). |
| `context` | `InsertContext` | `None` | Reusable insert context. See [InsertContexts](/docs/integrations/language-clients/python/advanced-inserting#insertcontexts). |
| `transport_settings` | dict | `None` | HTTP headers added to this request. |
This method returns `QuerySummary`. Its `summary` dictionary contains values reported by the server. `written_rows` is a convenience property, while `written_bytes()` and `query_id()` return the corresponding values. An insert failure raises an exception.
For specialized insert methods that work with Pandas DataFrames, PyArrow Tables, and Arrow-backed DataFrames, see [Advanced Inserting (Specialized Insert Methods)](/docs/integrations/language-clients/python/advanced-inserting#specialized-insert-methods).
A NumPy array is a valid Sequence of Sequences and can be used as the `data` argument to the main `insert` method, so a specialized method isn't required.
Examples
The examples below assume an existing table `users` with schema `(id UInt32, name String, age UInt8)`.
Basic row-oriented insert
```python theme={null}
import clickhouse_connect
client = clickhouse_connect.get_client()
# Row-oriented data: each inner list is a row
data = [
[13, "user_1", 25],
[79, "user_2", 30],
]
client.insert("users", data, column_names=["id", "name", "age"])
```
Column-oriented insert
```python theme={null}
import clickhouse_connect
client = clickhouse_connect.get_client()
# Column-oriented data: each inner list is a column
data = [
[13, 79], # id column
["user_1", "user_2"], # name column
[25, 30], # age column
]
client.insert("users", data, column_names=["id", "name", "age"], column_oriented=True)
```
Insert with explicit column types
```python theme={null}
import clickhouse_connect
client = clickhouse_connect.get_client()
# Useful when you want to avoid a DESCRIBE query to the server
data = [
[13, "user_1", 25],
[79, "user_2", 30],
]
client.insert(
"users",
data,
column_names=["id", "name", "age"],
column_type_names=["UInt32", "String", "UInt8"],
)
```
Insert into specific database
```python theme={null}
import clickhouse_connect
client = clickhouse_connect.get_client()
data = [
[13, "user_1", 25],
[79, "user_2", 30],
]
# Insert into a table in a specific database
client.insert(
"users",
data,
column_names=["id", "name", "age"],
database="production",
)
```
File inserts
For inserting data directly from files into ClickHouse tables, see [Advanced Inserting (File Inserts)](/docs/integrations/language-clients/python/advanced-inserting#file-inserts).
Raw API
For advanced use cases requiring direct access to ClickHouse HTTP interfaces without type transformations, see [Advanced Usage (Raw API)](/docs/integrations/language-clients/python/advanced-usage#raw-api).
Python DB-API 2.0
The `clickhouse_connect.dbapi` module implements the PEP 249 connection and cursor interface. It declares API level 2.0, `threadsafety=2`, and `paramstyle="pyformat"`.
```python theme={null}
from clickhouse_connect import dbapi
connection = dbapi.connect(
host="localhost",
username="default",
password="password",
database="default",
)
cursor = connection.cursor()
try:
cursor.execute(
"SELECT name FROM system.tables "
"WHERE database = %(database)s ORDER BY name LIMIT 5",
{"database": "system"},
)
print(cursor.description)
print(cursor.fetchall())
finally:
cursor.close()
connection.close()
```
`Cursor.execute` and `Cursor.executemany` accept an additional `settings` keyword argument for ClickHouse settings. `executemany` uses the driver's Native bulk-insert path for compatible `INSERT ... VALUES` statements with a materialized sequence of rows. `fetchone`, `fetchmany`, and `fetchall` consume the current materialized result.
ClickHouse does not provide traditional transactions through this HTTP interface. `Connection.commit()` and `Connection.rollback()` are no-ops. The [session ID concurrency rules](/docs/integrations/language-clients/python/advanced-usage#managing-clickhouse-session-ids) still apply when a connection is shared.
Utility classes and functions
The following modules provide additional public helpers used by client applications.
The installed package version is exposed as the string `clickhouse_connect.__version__`.
Exceptions
Custom exceptions, including the DB-API 2.0 exception hierarchy, are defined in `clickhouse_connect.driver.exceptions`. `DatabaseError` and `OperationalError` expose a numeric `code` attribute with the ClickHouse error code and a `name` attribute with the symbolic name such as `UNKNOWN_TABLE`, so applications can branch on `exc.code` instead of parsing the message. `code` is set even when `show_clickhouse_errors` is disabled, while `name` requires it. Both are `None` when unavailable, such as on transport errors. A connection failure partway through reading a query result raises `StreamFailureError` rather than returning a truncated result.
ClickHouse SQL utilities
The functions and the DT64Param class in the `clickhouse_connect.driver.binding` module can be used to properly build and escape ClickHouse SQL queries. Similarly, the functions in the `clickhouse_connect.driver.parser` module can be used to parse ClickHouse datatype names.
Multithreaded, multiprocess, and async/event driven use cases
For information on using ClickHouse Connect in multithreaded, multiprocess, and async/event-driven applications, see [Advanced Usage (Multithreaded, multiprocess, and async/event driven use cases)](/docs/integrations/language-clients/python/advanced-usage#multithreaded-multiprocess-and-asyncevent-driven-use-cases).
AsyncClient
For native asyncio usage, see [Advanced Usage (AsyncClient)](/docs/integrations/language-clients/python/advanced-usage#asyncclient).
Managing ClickHouse session IDs
For information on managing ClickHouse session IDs in multi-threaded or concurrent applications, see [Advanced Usage (Managing ClickHouse Session IDs)](/docs/integrations/language-clients/python/advanced-usage#managing-clickhouse-session-ids).
Customizing the HTTP connection pool
For information on customizing the HTTP connection pool for large multi-threaded applications, see [Advanced Usage (Customizing the HTTP connection pool)](/docs/integrations/language-clients/python/advanced-usage#customizing-the-http-connection-pool).