clickhouse-connect

clickhouse-connect is ClickHouse’s official core database driver for Python, built for high-throughput analytical workloads. It connects over ClickHouse’s HTTP interface for maximum compatibility and returns query results directly as Pandas DataFrames (numpy- or arrow-backed), NumPy arrays, PyArrow Tables, or Polars DataFrames, avoiding an extra conversion step for data-science and BI workflows.

Beyond the raw client, the package ships a lightweight SQLAlchemy Core dialect (with Alembic migration support) and is the connector Apache Superset uses out of the box for ClickHouse data sources, making it the standard bridge between ClickHouse and the Python data ecosystem.

What You Get

• A core HTTP-based ClickHouse client for executing queries and inserts from Python
• Native result conversion to Pandas DataFrames (NumPy- or Arrow-backed), NumPy arrays, PyArrow Tables, and Polars DataFrames
• A SQLAlchemy Core dialect supporting SELECT/JOIN, ARRAY JOIN, FINAL, SAMPLE, VALUES, and lightweight DELETE, compatible with both SQLAlchemy 1.4 and 2.x
• Alembic migration support with ClickHouse-specific table engines (MergeTree, ReplacingMergeTree) and column-placement/settings features
• The official connector used by Apache Superset for ClickHouse data sources

Common Use Cases

• Running analytical queries from a Python data pipeline and loading results directly into a Pandas or Polars DataFrame for analysis
• Connecting Apache Superset dashboards to a ClickHouse data source
• Managing ClickHouse schema changes through SQLAlchemy Core + Alembic migrations in a Python-managed data warehouse
• Bulk-inserting NumPy arrays or PyArrow Tables into ClickHouse tables for ETL workloads

Under The Hood

Architecture: The clickhouse_connect/driver/ package implements the HTTP-based client (connection pooling, query execution, streaming result parsing), datatypes/ handles ClickHouse’s rich column type system and its mapping to Python/NumPy/Arrow types, cc_sqlalchemy/ implements the SQLAlchemy Core dialect (compiler, type mapping, Alembic integration), and driverc/ contains Cython-accelerated modules (excluded from the ruff/ type-checking config) for performance-critical row/column parsing paths. Tech Stack: Python 3.10+, built with setuptools plus cython (3.1.x) for the compiled acceleration modules, packaged as prebuilt wheels per-platform via cibuildwheel; optional dependencies bring in pandas 2.0+, PyArrow, Polars, and SQLAlchemy 1.4/2.x depending on which integrations a consumer needs. Code Quality: Enforces ruff linting (line-length 140, targeting py310) and mypy type checking (with the Cython-compiled driver modules explicitly excluded from mypy since they lack stubs), plus a substantial tests/ suite and a documented MIGRATION.md for the 1.0 breaking-change upgrade path — signals of a maturing, actively-governed driver. API Design: The core query API returns typed results matching the requested output format (DataFrame, NumPy, Arrow, Polars) directly, minimizing manual conversion code, while the SQLAlchemy dialect is explicitly scoped to Core usage rather than full ORM support (no UPDATE compilation, foreign-key reflection, or cascades) — a deliberate tradeoff favoring Superset/Core compatibility over full ORM parity.