clickhouse-connect
Official Python client for ClickHouse over the database's HTTP interface. It handles queries, inserts, streaming results, server-side parameters, compression, TLS, and conversion to Python rows, NumPy, pandas, Polars, and Arrow. Optional extras add a native asyncio client, SQLAlchemy and Alembic integration, or an experimental embedded chDB backend. It is aimed at application data access and analytics tooling, not at administering every ClickHouse feature through an ORM.
The sensible default Python client for a ClickHouse server, especially for DataFrame and Arrow workloads. Avoid treating its SQLAlchemy dialect as a full ORM or its experimental chDB backend as equivalent to a production ClickHouse service.
Use it if
- You want an official ClickHouse client with broad HTTP compatibility, TLS support, and no dependency on the native TCP protocol
- Your workload moves query results between ClickHouse and pandas, Polars, NumPy, or PyArrow
- You need both synchronous application access and a native aiohttp-based async client behind similar query and insert methods
- You use Apache Superset or SQLAlchemy Core and accept a ClickHouse-focused subset of SQLAlchemy behavior
- You want the native ClickHouse TCP protocol rather than HTTP; clickhouse-connect explicitly chooses HTTP for compatibility, while clickhouse-driver is built around the native protocol
- You expect a complete SQLAlchemy ORM: the README says UPDATE compilation, relationship and foreign-key reflection, autoincrement, RETURNING, and cascade operations are not implemented
- You need Python 3.9 or older: version 1.6.0 requires Python 3.10 through Python 3.14
- You rely on exactly-once automatic retries for writes: the official API documentation says retryable reads have a budget, while commands and inserts generally are not retried because replay can duplicate side effects
- You want the embedded chDB mode as a mature drop-in server replacement: version 1.6.0 labels it experimental, permits one engine path per process, and does not support the async client or external data
Setup reality
pip install clickhouse-connect gives the synchronous HTTP client with urllib3, certificate roots, time-zone data where needed, and compression dependencies. Python must be 3.10 or newer. Production configuration still requires a real host, HTTP or HTTPS port, username, password, database, secure and verify choices, and often server_host_name when TLS is reached through an SSH tunnel. Do not disable certificate verification to make a tunnel work; set the original server hostname for TLS validation. Create a client once and reuse its connection pool rather than constructing one for every request, then close it during shutdown. pandas, Polars, NumPy, Arrow, SQLAlchemy, Alembic, aiohttp, and chDB support are optional extras, so examples using query_df, insert_arrow, get_async_client, or interface='chdb' can fail until the matching extra is installed. Async use requires clickhouse-connect[async] and get_async_client must be awaited. Query results are materialized when result properties are accessed, so large data should use a row, column, pandas, or Arrow streaming context manager to ensure the HTTP response closes. Parameters need ClickHouse type declarations for server-side binding, and identifiers still cannot safely be treated as ordinary value parameters. Version 1.0 introduced breaking changes from 0.x, so older tutorials need the migration guide. SQLAlchemy users should stay close to Core and verify dialect coverage before designing ORM-heavy models.
Patterns
Create a reusable HTTPS clientconnect-securely
import os
import clickhouse_connect
client = clickhouse_connect.get_client(
host=os.environ['CLICKHOUSE_HOST'],
port=8443,
username=os.environ['CLICKHOUSE_USER'],
password=os.environ['CLICKHOUSE_PASSWORD'],
database='analytics',
secure=True,
verify=True,
)Keep credentials outside source and reuse this client so its urllib3 connection pool can work.
Execute DDL or a scalar commandrun-command
client.command(
'CREATE TABLE IF NOT EXISTS events '
'(ts DateTime, name String) ENGINE MergeTree ORDER BY ts'
)
version = client.command('SELECT version()')Use command for statements without a tabular result or for a single primitive value; use query for datasets.
Read rows and column namesquery-rows
result = client.query(
'SELECT name, count() AS n FROM events GROUP BY name ORDER BY n DESC'
)
print(result.column_names)
for name, count in result.result_rows:
print(name, count)Accessing result_rows materializes the complete result. Stream when the row count can be large.
Bind typed server-side valuesbind-query-parameters
result = client.query(
'SELECT * FROM events WHERE ts >= {start:DateTime} AND name = {name:String}',
parameters={'start': start_time, 'name': event_name},
)The type inside braces is required for server-side binding. Parameters are for values, not arbitrary table or column identifiers.
Insert Python rowsinsert-rows
rows = [
[created_at, 'signup'],
[updated_at, 'purchase'],
]
client.insert(
'events',
rows,
column_names=['ts', 'name'],
)Column order in every row must match column_names; inserts are not generally retried because a replay can duplicate data.
Insert and query pandas DataFramesroundtrip-pandas
import pandas as pd
df = pd.DataFrame({'ts': timestamps, 'name': names})
client.insert_df('events', df)
latest = client.query_df(
'SELECT ts, name FROM events ORDER BY ts DESC LIMIT 100'
)Install the pandas extra and use pandas 2 or newer. DataFrame column names should match the target table.
Move data with PyArrowroundtrip-arrow
table = client.query_arrow(
'SELECT ts, name FROM events WHERE ts >= {start:DateTime}',
parameters={'start': start_time},
)
client.insert_arrow('events_archive', table)Install clickhouse-connect[arrow]; Arrow schemas and ClickHouse target columns still need compatible names and types.
Stream rows without full materializationstream-large-result
with client.query_row_stream(
'SELECT ts, name FROM events ORDER BY ts'
) as rows:
for ts, name in rows:
process(ts, name)Use the context manager so the HTTP response is closed even if processing raises or stops early.
Return a Polars DataFramequery-as-polars
frame = client.query_df_arrow(
'SELECT toDate(ts) AS day, count() AS events FROM events GROUP BY day',
dataframe_library='polars',
)Install the polars and Arrow extras. dataframe_library is available on the Arrow-backed DataFrame method, not query_df.
Run concurrent async queriesuse-async-client
import asyncio
import clickhouse_connect
async def main():
async with await clickhouse_connect.get_async_client() as client:
a, b = await asyncio.gather(
client.query('SELECT count() FROM events'),
client.query('SELECT count() FROM users'),
)
return a.first_row, b.first_row
asyncio.run(main())Install clickhouse-connect[async]. get_async_client itself is awaitable, and concurrency should still be bounded.
Apply settings to one queryset-query-limits
result = client.query(
'SELECT * FROM events',
settings={
'max_execution_time': 30,
'max_result_rows': 100000,
'result_overflow_mode': 'break',
},
)Readonly users may be unable to change server settings; enforce critical limits in ClickHouse user profiles too.
Use the experimental embedded backendopen-embedded-chdb
import clickhouse_connect
with clickhouse_connect.get_client(
interface='chdb',
path='/var/lib/my-app/chdb',
) as client:
total = client.query('SELECT sum(number) FROM numbers(10)').first_itemInstall clickhouse-connect[chdb]. One engine path is allowed per process, and this backend has no async client or external data support.
Alternatives
| Package | Registry | Pick it when |
|---|---|---|
| clickhouse-driver | PyPI | You specifically want the ClickHouse native TCP protocol and its established synchronous Python driver |
| chdb | PyPI | You need embedded in-process ClickHouse analytics without connecting to a shared server |
| duckdb | PyPI | You want an embedded analytical database with strong local file and DataFrame workflows rather than ClickHouse connectivity |