clickemy

A SQLAlchemy-style query builder for ClickHouse, backed by clickhouse-connect.

Installation

pip install clickemy

Quick start

from clickemy import create_engine, MetaData, Table, Column, select
from clickemy.types import UInt64, String, UInt32, DateTime

engine = create_engine("clickhouse://default:@localhost:8123/mydb")

metadata = MetaData()

users = Table("users", metadata,
    Column("id", UInt64),
    Column("name", String),
    Column("age", UInt32),
    Column("created_at", DateTime),
)

with engine.begin() as conn:
    rows = conn.execute(
        select(users.c.id, users.c.name)
        .order_by(users.c.id)
        .limit(10)
    ).fetchall()

for row in rows:
    print(row.id, row.name)

Query builder

SELECT columns

select(users.c.id, users.c.name)
# SELECT users.id, users.name FROM users

WHERE

select(users.c.id).where(users.c.age > 18)
# ... WHERE users.age > {p0}

# Multiple conditions are joined with AND
select(users.c.id).where(users.c.age >= 18, users.c.age <= 65)

# Compound expressions
select(users.c.id).where((users.c.age > 18) & (users.c.name != "bot"))
select(users.c.id).where((users.c.name == "Alice") | (users.c.name == "Bob"))

ORDER BY

select(users.c.id).order_by(users.c.created_at.desc())
select(users.c.id).order_by(users.c.name.asc(), users.c.id.desc())

LIMIT / OFFSET

select(users.c.id).limit(20).offset(40)

Raw SQL

with engine.begin() as conn:
    result = conn.execute_raw(
        "SELECT count() FROM users WHERE age > {min_age}",
        params={"min_age": 18},
    )
    print(result.fetchone()[0])

Result access

result = conn.execute(select(users.c.id, users.c.name))

rows = result.fetchall()       # list[Row]
row  = result.fetchone()       # Row | None
rows = result.fetchmany(50)    # list[Row]

# Row supports attribute, key, and index access
row.id
row["name"]
row[0]
row._asdict()   # {"id": 1, "name": "Alice"}

Connection URL

clickhouse://[user[:password]@]host[:port]/database

Example	Notes
clickhouse://localhost/mydb	local, no auth
clickhouse://default:secret@host:8123/mydb	with password
clickhouse://default:secret@host:8443/mydb	ClickHouse Cloud (TLS)

Supported types

UInt8 UInt16 UInt32 UInt64 UInt128 UInt256 Int8 Int16 Int32 Int64 Int128 Int256 Integer Float32 Float64 Float String FixedString(n) Date Date32 DateTime DateTime64(precision) Boolean Nullable(inner)