ClickHouse Driver

Warnings

• breaking Python version support has changed significantly. Versions 0.2.0 dropped Python 2 support. Version 0.2.9 dropped support for Python 3.6, 3.7, and 3.8. The current minimum supported Python version is 3.9.
  Fix: Ensure your Python environment is 3.9 or higher. For older Python versions, use an older `clickhouse-driver` version (e.g., <0.2.9 for Python 3.8).
• breaking Default connection parameters for `Client` changed in version 0.2.4. Specifically, `secure` now defaults to `False`, `port` defaults to `9000` (native protocol), and `compression` defaults to `False`. Previously, `secure` might have defaulted to `True` or different port values depending on prior library usage assumptions.
  Fix: Explicitly set `secure=True` if you require TLS/SSL. Always specify `port` if you are not using the default native protocol (9000) or HTTP protocol (8123, which is handled by a different client usually).
• gotcha The `clickhouse-driver` client does not include built-in connection pooling. For applications requiring high concurrency or frequent connections, you will need to implement a connection pool manually or use an external library.
  Fix: Implement a connection pooling mechanism using libraries like `queue` or `DBUtils` in Python, or use an ORM/framework that provides connection pooling capabilities for database drivers.
• gotcha Always use parameterization for inserting data or executing queries with user-provided values to prevent SQL injection vulnerabilities. Direct string formatting of queries is insecure.
  Fix: Pass data as a list of tuples to `client.execute()` for `INSERT` statements, e.g., `client.execute('INSERT INTO my_table VALUES', [(1, 'a'), (2, 'b')])`. For `SELECT` or `UPDATE` with parameters, use the `%s` placeholder and pass a tuple/list for the parameters: `client.execute('SELECT * FROM users WHERE id = %s', (user_id,))`.

Install

• pip install clickhouse-driver Install stable version

Imports

• Client

  from clickhouse_driver import Client

• errors

  from clickhouse_driver import errors

  Exceptions are directly available under `clickhouse_driver.errors`.

Quickstart

This quickstart demonstrates how to connect to a ClickHouse server using the `Client`, execute simple queries, create a table, insert data using parameterization, and retrieve data. It emphasizes proper connection management and basic CRUD operations. Environment variables are used for connection details for better security and flexibility.

import os
from clickhouse_driver import Client

# Ensure ClickHouse is running, e.g., with Docker:
# docker run -d --name clickhouse-server -p 8123:8123 -p 9000:9000 --ulimit nofile=262144:262144 yandex/clickhouse-server

host = os.environ.get('CLICKHOUSE_HOST', 'localhost')
port = int(os.environ.get('CLICKHOUSE_PORT', '9000')) # Default native protocol port
user = os.environ.get('CLICKHOUSE_USER', 'default')
password = os.environ.get('CLICKHOUSE_PASSWORD', '')

client = Client(host=host, port=port, user=user, password=password)

try:
    # Execute a simple query
    result = client.execute('SELECT 1 + 1 AS two')
    print(f"Query result: {result}")

    # Create a table and insert data
    client.execute('DROP TABLE IF EXISTS test_data')
    client.execute('CREATE TABLE test_data (id UInt64, name String) ENGINE = Memory')
    
    data_to_insert = [(1, 'Alice'), (2, 'Bob'), (3, 'Charlie')]
    client.execute('INSERT INTO test_data VALUES', data_to_insert)
    print(f"Inserted {len(data_to_insert)} rows.")
    
    # Select data
    selected_data = client.execute('SELECT id, name FROM test_data ORDER BY id')
    print(f"Selected data: {selected_data}")

finally:
    client.disconnect()
    print("Disconnected from ClickHouse.")