ClickHouse Pool

Warnings

• breaking In version 0.4.0, the direct methods `get_conn()` and `put_conn()` on the pool were renamed to `pull()` and `push()` respectively. While the recommended approach is now `pool.get_client()` with a context manager, older code directly using these methods will break.
  Fix: Update direct calls from `pool.get_conn()` to `pool.pull()` and `pool.put_conn()` to `pool.push()`, or ideally, refactor to use the `with pool.get_client() as client:` context manager pattern.
• breaking Version 0.6.0 introduced a breaking change by updating its Python requirement to Python 3.9 or newer. Installations on older Python versions (e.g., 3.8 or below) will fail or not be able to upgrade.
  Fix: Ensure your Python environment is version 3.9 or higher before installing or upgrading to `clickhouse-pool` v0.6.0+.
• gotcha Previous versions (prior to 0.5.3) had a connection pool bug that could lead to improper connection handling. Users on older versions might experience unexpected connection issues.
  Fix: Upgrade to version 0.5.3 or newer to benefit from the connection pool bug fix.
• gotcha The `ChPool` is configured with `connections_min` and `connections_max` parameters. If the number of concurrent client requests exceeds `connections_max`, a `ChPoolError.TooManyConnections` exception will be raised. This is intended behavior for a bounded pool but must be handled.
  Fix: Properly size `connections_max` based on your application's concurrency needs. Implement error handling for `ChPoolError.TooManyConnections` to gracefully manage peak loads or queue requests.
• gotcha Connections acquired from the pool via `get_client()` should ideally be managed within a `with` statement. If manually acquired (e.g., not using a context manager), ensure `pool.cleanup()` is called at the end of your application's lifecycle to properly close all connections and prevent resource leaks.
  Fix: Always use `with pool.get_client() as client:` for automatic connection management. In scenarios where manual acquisition is necessary, pair every `client = pool.get_client()` with a corresponding `pool.put_client(client)` and ensure `pool.cleanup()` is called when the pool is no longer needed.

Install

• pip install clickhouse-pool Install latest version

Imports

• ChPool

  from clickhouse_pool import ChPool

Quickstart

This quickstart demonstrates how to initialize a `ChPool`, acquire a client using a context manager, execute a simple query, and ensure the pool is cleaned up. Connection parameters can be passed directly or via environment variables.

import os
from clickhouse_pool import ChPool

# Configure connection details. For production, use environment variables or a config file.
host = os.environ.get('CLICKHOUSE_HOST', 'localhost')
port = int(os.environ.get('CLICKHOUSE_PORT', 9000)) # Native protocol port
user = os.environ.get('CLICKHOUSE_USER', 'default')
password = os.environ.get('CLICKHOUSE_PASSWORD', '')
database = os.environ.get('CLICKHOUSE_DB', 'default')

# Initialize the connection pool
# connections_min and connections_max can be adjusted for your workload
pool = ChPool(
    host=host, 
    port=port, 
    user=user, 
    password=password, 
    database=database,
    connections_min=5,
    connections_max=10
)

try:
    with pool.get_client() as client:
        # Execute a query
        result = client.execute("SELECT number, 'hello' FROM system.numbers LIMIT 5")
        print("Query Result:", result)

        # Example of an insert
        # client.execute("CREATE TABLE IF NOT EXISTS my_table (id UInt64, value String) ENGINE = Memory")
        # client.execute("INSERT INTO my_table VALUES", [(1, 'test1'), (2, 'test2')])
        # print("Data inserted.")

        # result_insert = client.execute("SELECT * FROM my_table")
        # print("Inserted Data:", result_insert)

except Exception as e:
    print(f"An error occurred: {e}")
finally:
    # Always close all connections in the pool once you're done with it
    pool.cleanup()
    print("Connection pool cleaned up.")