aiochlite
Lightweight asynchronous ClickHouse client for Python built on aiohttp.
Table of Contents
Features
- Lightweight - only aiohttp required, plus
tzdataon 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 bytzdataon 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 withclickhouse-connectand its compiled C parser on flat columns, and is 2.1x-2.3x faster thanaiochclienteverywhere. On string-heavy and container-heavy rows it trailsclickhouse-connectby 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)giveslist[bytes],Map(String, String)givesdict[bytes, bytes]. FixedString(N)keeps its null padding, whichstrstrips.- Available on
fetch,fetch_rows,fetchone,fetchval,streamandstream_rows.fetch_format/stream_formatreturn the payload undecoded, so there it raisesChArgumentError. - Reading a non-UTF-8 column without it raises
ChProtocolErrorlisting 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,streamautomatically appendFORMAT RowBinaryWithNamesAndTypesand 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:SSdate→YYYY-MM-DDtimedelta→HH:MM:SS[.ffffff](signed; suitable forTime/Time64)UUID/Decimal→ string representationenum.Enum→ its value, itself converted by these ruleslist→ 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-8None→NULLbool→1/0for query parameters,true/falseinside 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 —
UInt64and nineStringcolumns - 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)
| 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)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| 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 logRelease 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 logSecurity 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.
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 logA 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.