ClickHouse Connect driver API

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

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

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.

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

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.

Parameter names must be ClickHouse ASCII BareWord names. The driver accepts $ at the start, inside, or at the end of the name when the server would accept it, such as {$tenant_id:String}. A dictionary key that starts and ends with $ and has a buffer value such as bytes, bytearray, or memoryview is reserved for ClickHouse Connect’s raw binary parameter convention. If such a key is used for a non-binary server-side parameter, keep it to a single {name:Type} placeholder. Repeated $tag$ names can be parsed by ClickHouse as heredoc markers.

Use Python None for nullable values. Nested None values are supported inside Array and Tuple parameters, and inside Map literals when dict_parameter_format is set to "map".

• 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\''

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'

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.

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.

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:

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

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". The module also provides the PEP 249 type constructors Date, Time, Timestamp, and Binary, and the DateFromTicks, TimeFromTicks, and TimestampFromTicks functions.

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 additional settings and query_formats keyword arguments. settings passes ClickHouse settings. query_formats applies read formats by ClickHouse type when a statement returns rows, using the same mapping as Client.query. Cursor.execute also accepts the keyword-only pyformat_encoded argument. Its default True follows the DB-API pyformat contract. The SQLAlchemy dialect sets it to False when the statement compiler emitted raw percent signs, so applications normally should not set it. 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.

Cursor.description derives null_ok from each result column type. Non-nullable types report False, and nullable types report True, including Nullable wrappers, Variant, and Dynamic. None means the nullability is unknown. When a query that starts with SELECT or WITH, ignoring leading comments, returns no rows and no column metadata, the cursor runs a LIMIT 0 metadata query to populate description. If that metadata query fails, description is left empty.

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 error detail (True or "scrub"). Both are None when unavailable, such as on transport errors. Use show_clickhouse_errors="scrub" when end users should see SQL errors without host or server version information. The setting also controls mid-stream StreamFailureError messages and generic transport messages. It governs str(exc) only. Transport errors are still attached as __cause__, and tracebacks can contain the original host, URL, or library error text.

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