ClickHouse Connect driver API - ClickHouse Documentation

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.
`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.
`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.

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.

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:

import clickhouse_connect

client = clickhouse_connect.get_client()
print(client.server_version)

Connecting to a secure (HTTPS) external ClickHouse server

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.

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:

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:

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:

# 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:

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:

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.

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:

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 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 {<name>:<datatype>}. Pass the values as a Python dictionary.

Server-side binding with Python dictionary, DateTime value, and string value

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:

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 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

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:

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

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:

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:

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 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. Example of using ClickHouse settings:

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.
data	str or bytes	None	Optional data to include with the command as the POST body.
settings	dict	None	See settings description.
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)
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

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

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

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

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.
`settings`	dict	`None`	See Settings argument.
`query_formats`	dict	`None`	Read format by ClickHouse type. See 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.
`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

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

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

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

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

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 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).

Client streaming query methods

For streaming large result sets, ClickHouse Connect provides multiple streaming methods. See Advanced Queries (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.
`context`	`InsertContext`	`None`	Reusable insert context. See 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).

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

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

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

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

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).

Raw API

For advanced use cases requiring direct access to ClickHouse HTTP interfaces without type transformations, see 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".

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 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).

AsyncClient

For native asyncio usage, see 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).

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).

​Client initialization

​Connection arguments

​HTTPS/TLS arguments

​Settings argument

​Client creation examples

​Embedded chDB backend

​Client lifecycle and best practices

​Core principles

​Basic patterns

​Multi-threaded applications

​Proper cleanup

​When to use multiple clients

​Common method arguments

​Parameters argument

​Server-side binding

​Client-side binding

​Settings argument

​Client command method

​Command examples

​DDL statements

​Simple queries returning single values

​Commands with parameters

​Commands with settings

​Client query method

​Query examples

​Basic query

​Accessing query results

​Query with client-side parameters

​Query with server-side parameters

​Query with settings

​The QueryResult object

​Consuming query results with NumPy, Pandas or Arrow

​Client streaming query methods

​Client insert method

​Examples

​Basic row-oriented insert

​Column-oriented insert

​Insert with explicit column types

​Insert into specific database

​File inserts

​Raw API

​Python DB-API 2.0

​Utility classes and functions

​Exceptions

​ClickHouse SQL utilities

​Multithreaded, multiprocess, and async/event driven use cases

​AsyncClient

​Managing ClickHouse session IDs

​Customizing the HTTP connection pool

Client initialization

Connection arguments

HTTPS/TLS arguments

Settings argument

Client creation examples

Embedded chDB backend

Client lifecycle and best practices

Core principles

Basic patterns

Multi-threaded applications

Proper cleanup

When to use multiple clients

Common method arguments

Parameters argument

Server-side binding

Client-side binding

Settings argument

Client `command` method

Command examples

DDL statements

Simple queries returning single values

Commands with parameters

Commands with settings

Client `query` method

Query examples

Basic query

Accessing query results

Query with client-side parameters

Query with server-side parameters

Query with settings

The `QueryResult` object

Consuming query results with NumPy, Pandas or Arrow

Client streaming query methods

Client `insert` method

Examples

Basic row-oriented insert

Column-oriented insert

Insert with explicit column types

Insert into specific database

File inserts

Raw API

Python DB-API 2.0

Utility classes and functions

Exceptions

ClickHouse SQL utilities

Multithreaded, multiprocess, and async/event driven use cases

AsyncClient

Managing ClickHouse session IDs

Customizing the HTTP connection pool