Skip to main content

aiochlite

Python GitHub License PyPI - Version PyPI - Downloads GitHub Actions Workflow Status codecov GitHub last commit

Lightweight asynchronous ClickHouse client for Python built on aiohttp.

Table of Contents

Features

  • Lightweight - only aiohttp required, plus tzdata on Windows
  • Streaming support - efficient processing of large datasets with .stream()
  • Export formats - raw Parquet / CSV / TSV / JSON / Arrow / ORC payloads via .fetch_format() and .stream_format()
  • External tables - advanced temporary data support
  • Type conversion - automatic conversion between Python and ClickHouse types
  • Type-safe - full type hints coverage
  • Flexible - custom sessions, compression, query settings

Why aiochlite?

A small, pure-Python async client for ClickHouse over HTTP. Results are decoded from RowBinaryWithNamesAndTypes into either Row wrappers (fetch()) or raw tuples (fetch_rows()).

  • One dependency: aiohttp, joined by tzdata on Windows, which ships no timezone database of its own. aiochlite itself ships no compiled extensions.
  • Server-side query parameters: values are sent as ClickHouse param_* and never interpolated into the query text.
  • Fast for pure Python: in the benchmark below, with every client in its default configuration, fetch_rows() keeps up with clickhouse-connect and its compiled C parser on flat columns, and is 2.1x-2.3x faster than aiochclient everywhere. On string-heavy and container-heavy rows it trails clickhouse-connect by 32%-56%.
  • Typed: complete type hints for IDEs and static type checkers.
  • Focused API: ClickHouse over HTTP, without pandas, numpy, Arrow or Polars integrations.
  • Tested on Python 3.12–3.14 against ClickHouse 26.8 LTS, with additional compatibility coverage for ClickHouse 25.8 LTS.

Choosing a client. For DataFrames or column-oriented results, use the official clickhouse-connect — it has a real asyncio client, returns numpy, pandas, Arrow and Polars, and stays ahead on string-heavy and nested schemas. Reach for aiochlite when you want a small async client with one dependency that just returns rows.

Installation

pip install aiochlite

Optionally, pull in aiohttp's own speedups extra (aiodns, Brotli, and zstd support on Python < 3.14):

pip install "aiochlite[aiohttp-speedups]"

It affects connection setup and, with enable_compression=True, the available response encodings. Row decoding is pure Python either way and runs at the same speed.

Quick Start

Basic Connection

from aiochlite import AsyncChClient

# Using context manager (recommended)
async with AsyncChClient(
    url="http://localhost:8123",
    user="default",
    password="",
    database="default"
) as client:
    result = await client.fetch("SELECT 1")

# Or manual connection management
client = AsyncChClient("http://localhost:8123")
try:
    assert await client.ping()
    result = await client.fetch("SELECT 1")
finally:
    await client.close()

Execute Query

await client.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id UInt32,
        name String,
        email String
    ) ENGINE = MergeTree() ORDER BY id
""")

Insert Data

# Insert dictionaries
data = [
    {"id": 1, "name": "Alice", "email": "alice@example.com"},
    {"id": 2, "name": "Bob", "email": "bob@example.com"},
]
await client.insert("users", data)

# Insert tuples
data = [
    (3, "Charlie", "charlie@example.com"),
    (4, "Diana", "diana@example.com"),
]
await client.insert("users", data, column_names=["id", "name", "email"])

# Insert with settings
await client.insert(
    "users",
    [{"id": 5, "name": "Eve", "email": "eve@example.com"}],
    settings={"max_insert_block_size": 100000}
)

Rows are serialized and sent as the request goes out, so any iterable or async iterable works — including one that never holds the whole dataset:

async def rows_from(source):
    async for record in source:
        yield {"id": record.id, "name": record.name}

await client.insert("users", rows_from(source))

The first row decides whether the batch is read as dicts or as tuples.

Fetch Results

# Fetch all rows
rows = await client.fetch("SELECT * FROM users")
for row in rows:
    print(f"ID: {row.id}, Name: {row.name}, Email: {row.email}")

# Fetch one row
row = await client.fetchone("SELECT * FROM users WHERE id = 1")
if row:
    print(row.name)  # Attribute access
    print(row["name"])  # Dictionary-style access
    print(row.first())  # Get first column value

# Fetch single value
count = await client.fetchval("SELECT count() FROM users")
print(f"Total users: {count}")

# Iterate over results (for large datasets)
async for row in client.stream("SELECT * FROM users"):
    print(row.name)

Rows are decoded in full as they arrive. If a query selects many more columns than you actually read — a wide SELECT * where only a few fields are used — lazy_decode=True decodes each cell on first access instead:

client = AsyncChClient("http://localhost:8123", lazy_decode=True)

In the benchmark shapes it started paying off once fewer than about a third of the selected columns were read, and cost up to 45% when all of them were. Where it breaks even depends on the column types and on how expensive the skipped ones are to decode, so leave it off unless your access pattern clearly matches — and measure your own query.

Export Formats

Get the raw server payload in any ClickHouse output format — Parquet, CSV, TSV, JSON, Arrow, ORC and more. Both methods return raw bytes exactly as produced by the server (decode text formats yourself).

# Whole result at once
parquet = await client.fetch_format("SELECT * FROM users", "Parquet")
csv = (await client.fetch_format("SELECT * FROM users", "CSVWithNames")).decode()

# Chunked streaming for large result sets
with open("users.parquet", "wb") as f:
    async for chunk in client.stream_format("SELECT * FROM users", "Parquet"):
        f.write(chunk)

# Query parameters, settings and external tables work as usual
ndjson = await client.fetch_format(
    "SELECT * FROM users WHERE id > {id:UInt32}",
    "JSONEachRow",
    params={"id": 10},
)

Supported formats (ExportFormat):

Group Formats
Columnar / binary Parquet, Arrow, ArrowStream, ORC, Avro, Native, RowBinary, RowBinaryWithNames, RowBinaryWithNamesAndTypes
Separated values CSV, CSVWithNames, CSVWithNamesAndTypes, TSV, TSVWithNames, TSVWithNamesAndTypes, TabSeparated, TabSeparatedWithNames, TabSeparatedWithNamesAndTypes, TSKV, Values
JSON JSON, JSONStrings, JSONCompact, JSONColumns, JSONEachRow, JSONStringsEachRow, JSONObjectEachRow, JSONCompactEachRow, JSONCompactEachRowWithNames, JSONCompactEachRowWithNamesAndTypes
Human-readable XML, Markdown, Vertical, Pretty, PrettyCompact

Any other output format the server accepts can still be passed at runtime (type checkers will flag it).

Query Parameters

# Basic types
result = await client.fetch(
    "SELECT * FROM users WHERE id = {id:UInt32}",
    params={"id": 1}
)

# Lists and tuples (arrays)
result = await client.fetch(
    "SELECT * FROM users WHERE id IN {ids:Array(UInt32)}",
    params={"ids": [1, 2, 3]}  # or tuple: (1, 2, 3)
)

# Datetime and date
from datetime import datetime, date

result = await client.fetch(
    "SELECT * FROM events WHERE created_at > {dt:DateTime} AND date = {d:Date}",
    params={
        "dt": datetime(2025, 12, 14, 15, 30, 45),
        "d": date(2025, 12, 14)
    }
)

# UUID
from uuid import UUID

result = await client.fetch(
    "SELECT * FROM users WHERE uuid = {uid:UUID}",
    params={"uid": UUID("550e8400-e29b-41d4-a716-446655440000")}
)

# Decimal
from decimal import Decimal

result = await client.fetch(
    "SELECT * FROM products WHERE price > {price:Decimal(10, 2)}",
    params={"price": Decimal("99.99")}
)

# Nested arrays and maps
result = await client.fetch(
    "SELECT {matrix:Array(Array(Int32))} AS matrix, {data:Map(String, Int32)} AS data",
    params={
        "matrix": [[1, 2], [3, 4]],
        "data": {"a": 1, "b": 2}
    }
)

Supported parameter types:

  • Basic: int, float, str, bool, None
  • Collections: list, tuple, dict
  • Date/Time: datetime, date, timedelta
  • Special: UUID, Decimal, bytes, enum.Enum

Microseconds are kept, so a DateTime column rejects a value that has them — use DateTime64 or .replace(microsecond=0).

See Type Conversion for full type mapping details.

Query Settings

rows = await client.fetch(
    "SELECT * FROM users",
    settings={
        "max_execution_time": 60,
        "max_block_size": 10000
    }
)

External Tables

from aiochlite import ExternalTable

external_data = {
    "temp_data": ExternalTable(
        structure=[("id", "UInt32"), ("value", "String")],
        data=[
            {"id": 1, "value": "foo"},
            {"id": 2, "value": "bar"},
        ]
    )
}

result = await client.fetch(
    """
    SELECT t1.id, t1.name, t2.value
    FROM users t1
    JOIN temp_data t2 ON t1.id = t2.id
    """,
    external_tables=external_data
)

JSON Type

await client.execute("DROP TABLE IF EXISTS json_demo")
await client.execute("CREATE TABLE json_demo (id UInt32, doc JSON) ENGINE = Memory")

await client.insert(
    "json_demo",
    [{"id": 1, "doc": {"a": 1, "b": [True, None, {"c": "x"}]}}],
)

row = await client.fetchone("SELECT id, doc FROM json_demo WHERE id = 1")
print(row["doc"])  # Output: {"a": 1, "b": [True, None, {"c": "x"}]}

Binary Columns

A ClickHouse String is any sequence of bytes, not necessarily UTF-8. Columns decode to str; name the binary ones and they come back as bytes:

row = await client.fetchone(
    "SELECT id, payload, sha FROM blobs LIMIT 1",
    binary_columns=["payload", "sha"],  # or binary_columns="payload"
)
print(row["payload"])  # Output: b'\x00\xff\xfe\x01'
  • Applies at every level of nesting: Array(String) gives list[bytes], Map(String, String) gives dict[bytes, bytes].
  • FixedString(N) keeps its null padding, which str strips.
  • Available on fetch, fetch_rows, fetchone, fetchval, stream and stream_rows. fetch_format / stream_format return the payload undecoded, so there it raises ChArgumentError.
  • Reading a non-UTF-8 column without it raises ChProtocolError listing the columns to name.

Writing works the same way: pass bytes to an insert or a query parameter and they arrive as they are, UTF-8 or not.

await client.insert("blobs", [{"id": 1, "payload": b"\x00\xff\xfe\x01"}])

found = await client.fetchval(
    "SELECT count() FROM blobs WHERE payload = {p:String}",
    params={"p": b"\x00\xff\xfe\x01"},
)

Error Handling

Transport, server and decoding failures all derive from ChClientError, so one handler still catches them all. Invalid arguments still raise ValueError, as anywhere else in Python:

from aiochlite import ChClientError

try:
    await client.execute("SELECT * FROM non_existent_table")
except ChClientError as e:
    print(f"Query failed: {e}")

Catch a subclass when different failures need different handling:

Exception Raised when
ChTransportError The request got no usable answer: refused connection, timeout, truncated response
ChServerError ClickHouse reported an error, in the status or inside a 200 OK body
ChProtocolError The response arrived but could not be decoded in the requested format
ChArgumentError A query option does not fit the query, e.g. binary_columns naming a column it did not select. Also a ValueError

ChServerError carries status, code, query_id and exception_tag:

from aiochlite import ChServerError, ChTransportError

try:
    await client.fetch("SELECT * FROM users")
except ChTransportError:
    ...  # retry only if the operation is idempotent: the server may have run it anyway
except ChServerError as e:
    log.error("query %s failed with code %s: %s", e.query_id, e.code, e)

Custom Session

from aiohttp import ClientSession, ClientTimeout

timeout = ClientTimeout(total=30)
async with ClientSession(timeout=timeout) as session:
    async with AsyncChClient(url="http://localhost:8123", session=session) as client:
        result = await client.fetch("SELECT 1")

A session you pass in stays yours: the client sends its credentials per request rather than adding them to the session headers, and close() leaves the session open for you to close.

Enable Compression

async with AsyncChClient(url="http://localhost:8123", enable_compression=True) as client:
    result = await client.fetch("SELECT * FROM users")

Type Conversion

aiochlite uses ClickHouse’s RowBinaryWithNamesAndTypes for result decoding:

  • fetch, fetchone, fetchval, stream automatically append FORMAT RowBinaryWithNamesAndTypes and decode rows into Python values.
  • Queries passed to these methods must not contain a FORMAT ... clause.
  • Use execute() for statements that don’t return rows.

Automatic type conversion from ClickHouse:

ClickHouse Type Python Type Notes
Numeric
UInt8, UInt16, UInt32, UInt64 int
Int8, Int16, Int32, Int64 int
UInt128, UInt256, Int128, Int256 int
Float32, Float64 float
Decimal(P, S) Decimal Precision preserved
Decimal32(S), Decimal64(S), Decimal128(S), Decimal256(S) Decimal Precision preserved
String
String str bytes via binary_columns
FixedString(N) str Null padding stripped; bytes via binary_columns
Date/Time
Date date
Date32 date A year-0 date, which ClickHouse 26.8 allows and date cannot hold, raises ChProtocolError
DateTime datetime tzinfo only if the type includes a timezone
DateTime64(P) datetime tzinfo only if the type includes a timezone
Time timedelta Signed seconds; supports values beyond 24h
Time64(P) timedelta timedelta is microsecond-precision, so P > 6 is truncated
Special
UUID UUID
IPv4 ipaddress.IPv4Address
IPv6 ipaddress.IPv6Address
Enum8, Enum16 str Enum value name
Bool bool
Composite
Array(T) list Elements converted recursively
Tuple(T1, T2, ...) tuple Elements converted recursively; field names of a named tuple are dropped
Map(K, V) dict Keys and values converted
Modifiers
Nullable(T) T | None Nulls become None
LowCardinality(T) T Transparent wrapper
SimpleAggregateFunction(f, T) T Transparent wrapper
Other
JSON Any json.loads() result
Nothing None Only ever seen as Nullable(Nothing), the type of a bare NULL

Not supported: Variant, Dynamic, the geo types (Point, Ring, Polygon, MultiPolygon, LineString, MultiLineString), Interval*, BFloat16, Nested (with flatten_nested=0), AggregateFunction and QBit. Selecting one raises ChProtocolError.

Python to ClickHouse conversion:

When sending data to ClickHouse (query parameters and inserts), Python types are automatically converted:

  • datetime → YYYY-MM-DD HH:MM:SS
  • date → YYYY-MM-DD
  • timedelta → HH:MM:SS[.ffffff] (signed; suitable for Time / Time64)
  • UUID / Decimal → string representation
  • enum.Enum → its value, itself converted by these rules
  • list → array literal (e.g. [1,2,3])
  • tuple → tuple literal (e.g. (1,2,3))
  • dict → map literal (e.g. {'k':'v'})
  • bytes → the bytes themselves, whether or not they are UTF-8
  • None → NULL
  • bool → 1/0 for query parameters, true/false inside container literals

Benchmarks

Benchmark scripts live in benchmarks/.

Fetch and decode of 100,000 rows, 10 rounds, measured 2026-08-18. Three schemas, because what a column costs to decode depends far more on its shape than on how many columns there are:

  • flat columns — UInt64, DateTime('UTC'), Tuple(String, UInt16), Array(Decimal(10, 2))
  • wide strings — UInt64 and nine String columns
  • nested containers — UInt64, Array(Array(UInt8)), Map(String, Array(UInt8)), Array(Nullable(UInt64))
Client Flat columns Wide strings Nested containers
clickhouse-connect (async) 154.70 ms 76.89 ms 128.84 ms
aiochlite (tuples) 161.16 ms 120.31 ms 169.77 ms
aiochlite (Row) 181.37 ms 156.67 ms 194.29 ms
aiochclient 346.65 ms 271.90 ms 359.80 ms

Versions: aiochlite on main past the 1.7.0 tag, clickhouse-connect 1.7.1, aiochclient 2.7.0, Python 3.14.5, and ClickHouse 26.3.17.110.

All three schemas decode through a loop compiled for them, as does any schema of scalars and of Nullable, Array, Tuple, Map and JSON nested up to four levels deep. A column nested deeper reads through its own closure instead, at the speed it had before.

clickhouse-connect ships compiled C extensions; aiochlite is pure Python on top of aiohttp alone. It stays within 4% of that C parser on flat columns and falls behind by 32% on nested containers and 56% on strings — a string is a length plus a bytes slice per value, with nothing to batch. Against aiochclient it is 2.1x-2.3x faster on all three.

License

MIT License

Copyright (c) 2026 darkstussy

Metadata

Release files for aiochlite 1.8.2

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for aiochlite 1.8.2
File Size Uploaded Provenance Actions
aiochlite-1.8.2.tar.gz 205.9 kB With provenance Details for aiochlite-1.8.2.tar.gz

Built distribution (wheel)

Table of built distributions (wheels) for aiochlite 1.8.2
File Interpreter ABI Platform Actions
aiochlite-1.8.2-py3-none-any.whl Python 3 none any Details for aiochlite-1.8.2-py3-none-any.whl

Total release size: 253.3 kB

Release files / aiochlite-1.8.2.tar.gz

Download URL aiochlite-1.8.2.tar.gz
Size 205.9 kB
Tags Source
SHA-256 checksum
How to use checksums
cda19164bbae80007c7ec30c7b628db72001de9751f3226e4b840a03c12baffe
BLAKE2b-256 checksum
How to use checksums
597de4e3fa196099790d2060590d5e039505a078009d53f53adbd71bfee7b719
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.14

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Sep 2, 2026.

Transparency log

Release files / aiochlite-1.8.2-py3-none-any.whl

Download URL aiochlite-1.8.2-py3-none-any.whl
Size 47.5 kB
Tags Python 3
SHA-256 checksum
How to use checksums
ba80afae07a8605a10381cfe0d038f106a349938413f98e4c953ba73b550cb13
BLAKE2b-256 checksum
How to use checksums
cba7996e047831d68ca0f577ccf22c6d4b05feef491c7da47d0169a536333bab
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.14

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Sep 2, 2026.

Transparency log

Security for aiochlite 1.8.2

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

Full provenance

Both files in this release have been uploaded with provenance metadata.

Select from 2 files

Attestation for aiochlite-1.8.2.tar.gz

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Sep 2, 2026.

Transparency log

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page