ClickHouse Connect Cheat Sheet

ClickHouse Connect cheat sheet

clickhouse-connect is ClickHouse's official Python driver, talking to the server over HTTP(S). Make a client with get_client(), then query() for rows, query_df() for a pandas DataFrame, insert() / insert_df() to write, and command() for DDL. It has first-class pandas, NumPy, and Arrow support, streaming for huge result sets, an async client, and powers the ClickHouse SQLAlchemy dialect & Superset connector. Targets clickhouse-connect 1.x, Python 3.9+.

connect & query insert DDL & types streaming & async config & ecosystem gotcha ★ most common

Verified 2026-08-31 against the official docs at clickhouse.com/docs/integrations/python & the GitHub repo (clickhouse-connect 1.7.2, 2026-08-20; Python 3.9+). Uses the HTTP interface (port 8123, or 8443 for HTTPS) — not the native TCP protocol (that's clickhouse-driver).

Outline

One get_client(), then query* to read and insert* to write — each with row, DataFrame, NumPy, and Arrow variants. Stream large results; use command() for DDL.

1Install & get_clientHTTP driver

pip install clickhouse-connect★
Optional extras: [numpy], [pandas], [arrow], [async], [sqlalchemy]. Python 3.9+.
import clickhouse_connect client = clickhouse_connect.get_client( host="localhost", port=8123, username="default", password="")★
The entry point. Default HTTP port is 8123; for TLS use secure=True (port 8443).
client = clickhouse_connect.get_client( host="<id>.clickhouse.cloud", secure=True, password="...")
ClickHouse Cloud: secure=True + host from the console. Add database= to set the default DB.
client.ping() · client.server_version
Verify the connection. client.close() when done (or use it as a context manager).

2Query rows.query()

res = client.query("SELECT id, name FROM t WHERE id > 10")★
Returns a QueryResult. res.result_rows = list of row tuples; res.column_names = the headers.
for row in res.result_rows: print(row) # (id, name)★
Iterate rows as tuples. res.result_columns gives column-oriented data instead.
client.query("SELECT count() FROM t").result_rows[0][0]
Pull a single scalar. res.summary has rows-read / bytes-read stats.
rows = client.query_np("SELECT * FROM t") # NumPy array
query_np returns a NumPy structured array — efficient for numeric analytics.

3Query to DataFrame / Arrowanalytics

df = client.query_df("SELECT * FROM t WHERE ts >= today()")★
query_df returns a pandas DataFrame directly — the most common analytics path. Needs the pandas extra.
tbl = client.query_arrow("SELECT * FROM t") # pyarrow.Table★
query_arrow returns an Arrow Table — zero-copy handoff to Polars/DuckDB/Parquet.
for df in client.query_df_stream("SELECT * FROM big"): process(df)
Stream results as a sequence of DataFrame blocks — constant memory over huge tables (card 9).

6command() & DDLstatements

client.command(""" CREATE TABLE t (id UInt32, name String, ts DateTime) ENGINE = MergeTree ORDER BY (ts, id)""")★
command() runs DDL/DCL and statements that don't return a result set. MergeTree + ORDER BY is the standard table shape.
client.command("DROP TABLE IF EXISTS t") client.command("TRUNCATE TABLE t") · "OPTIMIZE TABLE t FINAL"
Lifecycle statements. command returns a scalar/summary for single-value queries.
n = client.command("SELECT count() FROM t")
command is a convenient shortcut when you expect exactly one value back.

7Parameters & settingssafe SQL

client.query("SELECT * FROM t WHERE id = {id:UInt32}", parameters={"id": 42})★
Server-side bound parameters with {name:Type} placeholders — safe against SQL injection. Types are ClickHouse types.
client.query(sql, settings={"max_execution_time":60, "max_threads":4})★
Pass any ClickHouse query setting per call (timeouts, memory limits, formats). Set defaults on get_client(settings=...).
client.query(sql, query_id="my-job-1")
Tag a query to find/kill it in system.processes.

8Types & formatsmapping

# UInt/Int/Float, String, DateTime(64), Date, Decimal, UUID, IPv4/6
Rich scalar types map to Python natively. Nullable(T) → None; LowCardinality(T) is transparent.
# Array(T) -> list · Map(K,V) -> dict · Tuple -> tuple · Nested
Composite types round-trip to the obvious Python containers.
client.query(sql, column_formats={"ts": "int"})
Override how a column is materialized (e.g. datetimes as ints/strings, UUIDs as strings) via column_formats/query_formats.
# DateTime is timezone-aware — mind the server/session TZgotcha
ClickHouse stores DateTime in UTC and applies a column/session timezone on read — be explicit about TZ to avoid off-by-hours bugs.

Worth memorizing

get_client(host=, port=8123)secure=True -> 8443 (HTTPS)

query().result_rows / .column_names

query_df / query_arrowDataFrame / Arrow Table out

query_npNumPy structured array

insert(table, rows, column_names=)list of row lists

insert_df / insert_arrowbulk-load a DataFrame / Arrow

batch bigtiny inserts make parts — avoid them

command()DDL + single-value queries

{name:Type} + parameters=safe bound params

settings={}per-query ClickHouse settings

query_df_stream / *_streambounded memory over big tables

get_async_client()awaitable, same API

ClickHouse Connect cheat sheet

Outline

Connect & query

Insert

DDL & types

Streaming, async & ecosystem

Connect & Query

Insert

DDL & Types

Streaming & Async

Config & Ecosystem

Worth memorizing