Working with ClickHouse from Python

Most pipelines talk to ClickHouse from Python. This article covers the client itself: how to connect, the different call styles for statements and queries, how to read a single value out of a result, and how to pass values into a query safely.

Transforming the data you read is a separate topic. The Python section covers that in Data Transformations with Pandas; this article stays on the database boundary.

Mental model#

Python process   ↓  clickhouse-connect client, HTTP :8123ClickHouse server

The clickhouse-connect package provides a client object. Every statement and query goes through that one object. It speaks to ClickHouse over HTTP on port 8123 by default.

Connecting#

from clickhouse_connect import get_client
client = get_client(    host="clickhouse",    port=8123,    username="default",    password="password",)

Use the hostname for your environment. Connection settings and credentials should come from environment variables or configuration, not from literals in the source file. Create the client once and reuse it for the life of the task.

Four ways to talk to ClickHouse#

command() for statements#

Use command() when you are changing the database or running an INSERT ... SELECT, and there is no result you plan to read:

client.command("""    CREATE TABLE IF NOT EXISTS analytics.orders    (        order_id     UInt64,        customer_id  UInt64,        created_at   DateTime,        amount       Float64,        status       String    )    ENGINE = MergeTree    ORDER BY (created_at, order_id)""")
client.command("""    INSERT INTO analytics.completed_orders    SELECT order_id, customer_id, created_at, amount    FROM analytics.orders    WHERE status = 'completed'""")

query() for rows you read in Python#

result = client.query("""    SELECT customer_id, amount    FROM analytics.orders    WHERE status = 'completed'""")
for customer_id, amount in result.result_rows:    ...

result.result_rows is a list of row tuples. result.result_columns is the same data grouped by column instead of by row.

query_df() for a DataFrame#

df = client.query_df("""    SELECT order_id, customer_id, created_at, amount    FROM analytics.orders""")

This is the usual entry point for a transformation. What happens next is Pandas work, covered in the Python section.

insert_df() and insert() for loading#

Write a DataFrame with insert_df(), choosing the columns explicitly:

columns = ["order_id", "customer_id", "created_at", "amount"]
client.insert_df(    "analytics.completed_orders",    column_names=columns,    df=df[columns],)

Write rows you built in Python with insert():

rows = [    (101, 1, "2026-01-04 09:12:00", 1200.0),    (102, 1, "2026-01-04 10:40:00", 850.0),]
client.insert(    "analytics.completed_orders",    data=rows,    column_names=["order_id", "customer_id", "created_at", "amount"],)

Insert in batches. Collecting a few thousand rows and inserting once is far better than inserting one row at a time.

Reading a single value#

A query that returns one row and one column is a scalar. Pull it out of the first column's first value:

result = client.query("SELECT count() FROM analytics.orders")
order_count = result.result_columns[0][0]

Scalars are common at the start of a job: a row count, a maximum id, or the latest timestamp already processed. maxOrNull() returns the maximum of a column, or None when the table is empty:

result = client.query("""    SELECT maxOrNull(created_at)    FROM analytics.completed_orders""")
last_processed = result.result_columns[0][0]
if last_processed is None:    ...  # first run: nothing processed yet

Passing values into a query#

A query often depends on a value computed in Python, such as the watermark above. Do not build the SQL string with an f-string:

# Wrong: breaks on quoting, wrong types, and injectionsql = f"SELECT * FROM analytics.orders WHERE created_at > '{last_processed}'"df = client.query_df(sql)

Pass the value as a query parameter instead. Name the placeholder in the SQL with its type, and provide the value in parameters:

df = client.query_df(    """    SELECT *    FROM analytics.orders    WHERE created_at > {since:DateTime}    """,    parameters={"since": last_processed},)

ClickHouse substitutes the value with correct quoting and type handling. {since:DateTime} declares both the parameter name and its ClickHouse type. The same works for command() and query().

Handling an empty result#

A scheduled job should expect runs with nothing to do:

df = client.query_df(    "SELECT * FROM analytics.orders WHERE created_at > {since:DateTime}",    parameters={"since": last_processed},)
if df.empty:    return

Returning early avoids running transformations and inserts on zero rows.

The round-trip pattern#

Reading, transforming, and writing back is one loop:

ClickHouse   ↓  query_df()Pandas DataFrame   ↓  transform (Python section)DataFrame   ↓  insert_df()ClickHouse

This article covers the first and last steps. The middle step, transforming the DataFrame, is covered in Data Transformations with Pandas, and continuing from the last processed point is covered in Batch and Incremental Processing.

Common mistakes#

Building SQL with f-strings#

String interpolation breaks on quoting and types and allows injection. Use parameters for every value.

Using command() when you need the rows#

command() does not give you a usable result set. Use query() or query_df() to read data.

Inserting the whole DataFrame#

Temporary or reordered columns can reach the target table. Select the column list explicitly in insert_df().

One row per insert#

Each insert creates a storage part. Batch rows before inserting.

Creating a client per call#

Build the client once per task and reuse it.

Quick reference#

See also#