ClickHouse Connect

Warnings

• breaking The parameter `apply_server_timezone` in client and query methods was renamed to `tz_source` in v0.14.0.
  Fix: Replace `apply_server_timezone=True` (or similar) with `tz_source='auto'` or other valid options for timezone handling.
• breaking Version 0.13.0 introduced a native write path for the `Variant` data type. Previously, values were stringified; now they are serialized using their native ClickHouse types client-side, which changes how `Variant` columns store data.
  Fix: Review insertion logic for `Variant` columns. Ensure that your application expects native type serialization. If using older ClickHouse server versions with `Variant`, compatibility issues may arise.
• breaking The legacy executor-based asynchronous client (`AsyncClient(client=...)`) and related parameters (`executor_threads`, `executor`, `pool_mgr`) have been removed. The native aiohttp-based async client is now standard.
  Fix: Migrate to `clickhouse_connect.get_async_client()` or `create_async_client()`, which uses `aiohttp`. Ensure `aiohttp` is installed via `pip install clickhouse-connect[async]`.
• deprecated Python 3.9 support is deprecated and will be removed in version 1.0. Python 3.8 is End-of-Life (EOL) and no longer officially tested or supported; wheels are not built for 3.8 AARCH64 versions.
  Fix: Upgrade to Python 3.10 or higher. The library officially tests against Python 3.10 through 3.14.
• gotcha Optional dependencies (e.g., `numpy`, `pandas`, `pyarrow`, `polars`, `sqlalchemy`) are lazy-loaded. If you intend to use features relying on these, you must install them explicitly using the `[extra]` syntax (e.g., `pip install clickhouse-connect[pandas]`).
  Fix: Always install necessary extras for features like Pandas DataFrame integration, even if the base `clickhouse-connect` package is already present.
• gotcha For ClickHouse server versions 22.8 and 22.10+, there is an internal serialization format incompatibility for experimental JSON. Using multiple clients with mixed 22.8/22.9 and 22.10+ server versions will break if JSON support is enabled. Pandas 1.x support is also deprecated and will be dropped in 1.0.
  Fix: For JSON, use separate Python interpreters for different ClickHouse server versions if mixed JSON usage is required. For Pandas, upgrade to Pandas 2.x or later.
• gotcha When creating a DBAPI Connection or SQLAlchemy DSN, unrecognized keyword arguments or query parameters will now raise an exception instead of being passed as ClickHouse server settings. Server settings should be prefixed with `ch_`.
  Fix: Prefix any ClickHouse server-specific settings passed as keyword arguments or query parameters with `ch_` (e.g., `ch_max_rows_to_read=1000`).

Install

• pip install clickhouse-connect Install core library
• pip install "clickhouse-connect[pandas,numpy,sqlalchemy,polars,arrow,async]" Install with common optional dependencies

Imports

• get_client

  import clickhouse_connect
  client = clickhouse_connect.get_client(...)

  This is the primary way to instantiate a client connection.

Quickstart

This quickstart demonstrates how to establish a connection to a ClickHouse server, create a table, insert data, and query it. It includes an example using the `query_df` method for Pandas DataFrames, which requires the `pandas` optional dependency. Credentials are loaded from environment variables for secure usage.

import clickhouse_connect
import os

host = os.environ.get('CH_HOST', 'localhost')
port = int(os.environ.get('CH_PORT', 8123)) # Use 8443 for TLS/Cloud
username = os.environ.get('CH_USER', 'default')
password = os.environ.get('CH_PASSWORD', '')
database = os.environ.get('CH_DB', 'default')

try:
    client = clickhouse_connect.get_client(
        host=host, 
        port=port,
        username=username,
        password=password,
        database=database,
        secure= (port == 8443) # Automatically use TLS for 8443
    )
    
    # Test connection
    client.ping()
    print(f"Successfully connected to ClickHouse at {host}:{port}")

    # Create a table
    client.command(
        "CREATE TABLE IF NOT EXISTS my_test_table (",
        "    id UInt64,",
        "    name String,",
        "    value Float64",
        ") ENGINE MergeTree ORDER BY id"
    )
    print("Table 'my_test_table' created or already exists.")

    # Insert data
    data_to_insert = [
        [1, 'Alpha', 100.1],
        [2, 'Beta', 200.2],
        [3, 'Gamma', 300.3]
    ]
    client.insert('my_test_table', data_to_insert, column_names=['id', 'name', 'value'])
    print("Data inserted into 'my_test_table'.")

    # Query data
    result = client.query('SELECT * FROM my_test_table ORDER BY id')
    print("Query Results:")
    for row in result.result_set:
        print(row)

    # Example with Pandas (requires 'pandas' extra)
    try:
        import pandas as pd
        df = client.query_df('SELECT * FROM my_test_table')
        print("\nPandas DataFrame Results:")
        print(df)
    except ImportError:
        print("\nSkipping Pandas example: 'pandas' not installed. Install with `pip install clickhouse-connect[pandas]`")

finally:
    if 'client' in locals():
        client.close()
        print("Connection closed.")
except Exception as e:
    print(f"An error occurred: {e}")