### Install ClickHouse Connect from Source

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/index.mdx

Follow these steps to clone the repository, optionally install Cython for performance optimizations, and then install the package from the project root.

```bash
git clone https://github.com/ClickHouse/clickhouse-connect.
pip install cython
cd clickhouse-connect
pip install .
```

--------------------------------

### Install dependencies

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/CONTRIBUTING.md

Install required packages and pre-commit hooks.

```bash
python -m pip install --upgrade pip
pip install setuptools wheel
pip install -r tests/test_requirements.txt
pre-commit install
```

--------------------------------

### Sync vs Async Client Setup and Query

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/async-client.md

Compares the synchronous and asynchronous client setup and query execution. The asynchronous version requires `async def` functions, `await` for client acquisition and queries, and `asyncio.run()` to execute.

```python
# Sync version
def sync_example():
    client = clickhouse_connect.get_client(host="localhost")
    result = client.query("SELECT * FROM users LIMIT 10")
    for row in result.result_rows:
        print(row)
    client.close()

# Async version
async def async_example():
    client = await clickhouse_connect.get_async_client(host="localhost")
    result = await client.query("SELECT * FROM users LIMIT 10")
    for row in result.result_rows:
        print(row)
    await client.close()

asyncio.run(async_example())
```

--------------------------------

### Rewrite Session Make Import

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/clickhouse_connect/cc_sqlalchemy/MIGRATING_FROM_CLICKHOUSE_SQLALCHEMY.md

Use standard SQLAlchemy session setup directly. For example, create a Session class using sessionmaker and then instantiate a session.

```python
# Use standard SQLAlchemy session setup
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()
```

--------------------------------

### Install ClickHouse Connect with Asyncio Support

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/README.md

Install the ClickHouse Connect package with the optional async dependency for native asyncio support.

```bash
pip install clickhouse-connect[async]
```

--------------------------------

### Install ClickHouse Connect

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/README.md

Install the ClickHouse Connect package using pip. Optional dependencies like async, pandas, numpy, arrow, and alembic can be installed with specific extras.

```bash
# Basic install
pip install clickhouse-connect

# With async support
pip install clickhouse-connect[async]

# With pandas/numpy/arrow support
pip install clickhouse-connect[pandas]
pip install clickhouse-connect[numpy]
pip install clickhouse-connect[arrow]

# With Alembic migrations
pip install clickhouse-connect[alembic]
```

--------------------------------

### Install ClickHouse Connect with Alembic Support

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/README.md

Install the ClickHouse Connect package with the optional Alembic extra for schema migrations.

```bash
pip install clickhouse-connect[alembic]
```

--------------------------------

### Install ClickHouse Connect

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/README.md

Install the ClickHouse Connect package using pip. Ensure Python 3.10 or higher is used.

```bash
pip install clickhouse-connect
```

--------------------------------

### Standard SQLAlchemy Session Setup

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/clickhouse_connect/cc_sqlalchemy/MIGRATING_FROM_CLICKHOUSE_SQLALCHEMY.md

Replace clickhouse-sqlalchemy's make_session with standard SQLAlchemy sessionmaker for creating sessions.

```python
from sqlalchemy.orm import sessionmaker

# Assuming 'engine' is your SQLAlchemy engine instance
Session = sessionmaker(bind=engine)
session = Session()

```

--------------------------------

### Example: Describe Table and Convert to ColumnDef

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/types.md

Demonstrates how to execute a DESCRIBE TABLE command and process its output. The result can be converted into a tuple of ColumnDef objects for structured access.

```python
result = client.command("DESCRIBE TABLE users")
# Returns tuple of lines, can be converted to ColumnDef
# Or use client.describe_table() if available for structured result
```

--------------------------------

### create_async_client()

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/async-client.md

Creates an async ClickHouse Connect client. This function must be awaited and requires the 'aiohttp' extra to be installed.

```APIDOC
## create_async_client()

### Description
Creates an async ClickHouse Connect client. This is an async function and must be awaited.

### Method
`async def`

### Parameters

#### Standard Parameters (same as `create_client()`)
- **host** (str | None) - Hostname of the ClickHouse server.
- **username** (str | None) - Username for authentication.
- **password** (str) - Password for authentication.
- **access_token** (str | None) - Access token for authentication.
- **token_provider** (Callable[[], str | Awaitable[str]] | None) - A function or awaitable that provides an access token.
- **database** (str) - The database to connect to.
- **interface** (str | None) - The interface to use.
- **port** (int) - Port of the ClickHouse server.
- **secure** (bool | str) - Whether to use a secure connection.
- **dsn** (str | None) - Data Source Name.
- **settings** (dict[str, Any] | None) - Additional connection settings.
- **headers** (dict[str, str] | None) - Custom headers for the connection.
- **generic_args** (dict[str, Any] | None) - Generic arguments for the connection.

#### Async-Specific Parameters
- **connector_limit** (int) - Optional - Maximum total connections to the server. Defaults to 100.
- **connector_limit_per_host** (int) - Optional - Maximum connections per host. Defaults to 20.
- **keepalive_timeout** (float) - Optional - Idle keepalive timeout in seconds. Defaults to 30.0.

### Returns
- `AsyncClient` - An instance of the asynchronous client.

### Raises
- `ImportError` - if `aiohttp` is not installed.
- `ProgrammingError` - if invalid parameters are provided.

### Example
```python
import asyncio
import clickhouse_connect

async def main():
    # Create async client
    client = await clickhouse_connect.get_async_client(
        host="localhost",
        username="default",
        password=""
    )
    
    # Use client in async context
    result = await client.query("SELECT * FROM users LIMIT 10")
    print(result.result_rows)
    
    # Close client
    await client.close()

# Run
asyncio.run(main())
```
```

--------------------------------

### Get Client Setting

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/client.md

Gets a client-level configuration option. Returns the setting value as a string, or None if the setting is not currently defined.

```python
def get_client_setting(self, key: str) -> str | None
```

--------------------------------

### Create and Use AsyncClient

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/async-client.md

Demonstrates how to create an asynchronous ClickHouse client, execute a query, and close the client within an asyncio event loop. Ensure the 'aiohttp' extra is installed.

```python
import asyncio
import clickhouse_connect

async def main():
    # Create async client
    client = await clickhouse_connect.get_async_client(
        host="localhost",
        username="default",
        password=""
    )
    
    # Use client in async context
    result = await client.query("SELECT * FROM users LIMIT 10")
    print(result.result_rows)
    
    # Close client
    await client.close()

# Run
asyncio.run(main())
```

--------------------------------

### Example CSV Output Content

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/advanced-usage.mdx

This is an example of the content generated in the output.csv file when using the CSVWithNames format.

```csv
"number","number_as_str"
0,"0"
1,"1"
2,"2"
3,"3"
4,"4"
```

--------------------------------

### Complete ETL Pipeline Example

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/EXAMPLES.md

This snippet demonstrates a full ETL pipeline, including extracting data from a source, transforming it, and loading it into ClickHouse. It also includes verification steps and table creation.

```python
import clickhouse_connect
import pandas as pd
from datetime import datetime

def etl_pipeline():
    """Complete ETL pipeline example"""
    
    client = clickhouse_connect.get_client(host="localhost")
    
    try:
        # EXTRACT: Read from source system
        print("Extracting data...")
        source_data = fetch_from_external_api()  # Your data source
        df = pd.DataFrame(source_data)
        
        # TRANSFORM: Process data
        print("Transforming data...")
        df['processed_at'] = datetime.now()
        df['id'] = df['id'].astype('int32')
        df['status'] = df['status'].str.lower()
        
        # LOAD: Insert into ClickHouse
        print("Loading into ClickHouse...")
        
        # Create table if not exists
        client.command("""
            CREATE TABLE IF NOT EXISTS events (
                id UInt32,
                status String,
                value Float32,
                processed_at DateTime
            ) ENGINE = MergeTree
            ORDER BY (processed_at, id)
        """)
        
        # Insert data
        summary = client.insert_df("events", df)
        
        print(f"✓ Inserted {summary.written_rows()} rows")
        print(f"✓ Loaded {summary.written_bytes()} bytes")
        
        # VERIFY: Check loaded data
        result = client.query("SELECT COUNT(*) FROM events WHERE processed_at = today()")
        count = result.result_rows[0][0]
        print(f"✓ Verified: {count} rows for today")
        
    finally:
        client.close()

def fetch_from_external_api():
    """Example: fetch data from external source"""
    return [
        {"id": 1, "status": "ACTIVE", "value": 100.5},
        {"id": 2, "status": "INACTIVE", "value": 200.75},
        # ... more data
    ]

# Run ETL
etl_pipeline()

```

--------------------------------

### Install tzdata extra for slim Linux containers

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/MIGRATION.md

For slim Linux containers lacking a system tzdb, install the `tzdata` extra to enable timezone handling with `zoneinfo`.

```bash
pip install clickhouse-connect[tzdata]
```

--------------------------------

### Connect and Query using DB-API 2.0

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/client.md

This example demonstrates how to establish a database connection and execute a query using the DB-API 2.0 interface provided by clickhouse_connect.dbapi. It includes fetching and printing results.

```python
import clickhouse_connect.dbapi as dbapi

conn = dbapi.connect(
    host="localhost",
    username="user",
    password="pass",
    database="mydb"
)

cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE age > %(age)s", {"age": 18})
for row in cursor.fetchall():
    print(row)

cursor.close()
conn.close()
```

--------------------------------

### Example ClickHouse CREATE TABLE Output

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/clickhouse_connect/cc_sqlalchemy/alembic/WORKED_EXAMPLE.md

This is an example of the output you might receive when verifying the schema of a table created by Alembic in ClickHouse. Note that exact formatting and default settings may vary.

```sql
CREATE TABLE alembic_demo.events
(
    `id` UInt32,
    `event_name` String,
    `created_at` DateTime64(3, 'UTC') DEFAULT now64(3)
)
ENGINE = MergeTree
ORDER BY id
```

--------------------------------

### Create Default ClickHouse Client

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/driver-api.mdx

Connects to the default HTTP port on localhost with default user and no password. Use this for local development or default setups.

```python
import clickhouse_connect

client = clickhouse_connect.get_client()
print(client.server_version)
# Output: '22.10.1.98'
```

--------------------------------

### Create Async ClickHouse Client

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/client.md

Creates an asynchronous ClickHouse Connect HTTP client. Requires the `aiohttp` extra to be installed. The `token_provider` can be a sync or async callable.

```python
import asyncio
import clickhouse_connect

async def main():
    client = await clickhouse_connect.get_async_client(
        host="localhost",
        username="user",
        password="pass"
    )
    result = await client.query("SELECT 1")
    print(result.result_rows)

asyncio.run(main())
```

--------------------------------

### Get and Use AsyncClient

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/advanced-usage.mdx

Instantiate an AsyncClient using get_async_client and execute a query within an asyncio event loop. This is suitable for non-blocking I/O operations.

```python
import asyncio

import clickhouse_connect

async def main():
    client = await clickhouse_connect.get_async_client()
    result = await client.query("SELECT name FROM system.databases LIMIT 1")
    print(result.result_rows)
    # Output:
    # [('INFORMATION_SCHEMA',)]

asyncio.run(main())
```

--------------------------------

### ExternalData Usage Example

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/types.md

Demonstrates how to create and use the ExternalData class to send temporary table data with a query.

```python
ext_data = ExternalData(
    table_name="my_external_table",
    columns=["col1", "col2"],
    column_types=["UInt32", "String"],
    data=[[1, "a"], [2, "b"]]
)

result = client.query(
    "SELECT * FROM my_external_table WHERE col1 > 1",
    external_data=ext_data
)
```

--------------------------------

### Check Server Version for Feature Compatibility

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/README.md

Provides an example of how to conditionally execute queries based on the ClickHouse server version using the `min_version` method. This allows for using newer features or providing fallbacks for older versions.

```python
if client.min_version("22.8"):
    # Use features available in 22.8+
    result = client.query("SELECT * FROM TABLE WHERE ...")
else:
    # Fallback for older versions
    result = client.query("SELECT * FROM TABLE WHERE ...")
```

--------------------------------

### Basic Async Query

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/EXAMPLES.md

Demonstrates how to establish an asynchronous client connection and execute a simple query.

```python
import asyncio
import clickhouse_connect

async def main():
    # Create async client
    client = await clickhouse_connect.get_async_client(
        host="localhost",
        username="default"
    )
    
    try:
        # Execute query
        result = await client.query("SELECT * FROM users LIMIT 10")
        print(result.result_rows)
        
    finally:
        await client.close()

asyncio.run(main())
```

--------------------------------

### Create Project Directory

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/clickhouse_connect/cc_sqlalchemy/alembic/WORKED_EXAMPLE.md

Create a new directory for the Alembic demo project and navigate into it.

```bash
mkdir alembic_demo
cd alembic_demo
```

--------------------------------

### Create and Use QueryContext

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/advanced-querying.mdx

Demonstrates how to create a QueryContext with initial query and parameters, then use it to execute a query. It also shows how to update a single parameter and re-execute the query.

```python
client.create_query_context(query='SELECT value1, value2 FROM data_table WHERE key = {k:Int32}',
                            parameters={'k': 2},
                            column_oriented=True)
result = client.query(context=qc)
assert result.result_set[1][0] == 'second_value2'
nc.set_parameter('k', 1)
result = test_client.query(context=qc)
assert result.result_set[1][0] == 'first_value2'
```

--------------------------------

### Create ClickHouse Client with Connection Parameters

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/README.md

Demonstrates how to create a ClickHouse client using `clickhouse_connect.get_client()`. It shows essential parameters for host, port, authentication, database, security, compression, timeouts, session ID, and query defaults.

```python
client = clickhouse_connect.get_client(
    # Host/Port
    host="localhost",           # Default: localhost
    port=8123,                 # Default: 8123 (http) or 8443 (https)
    
    # Authentication (choose one)
    username="default",        # Username (with password)
    password="",               # Password
    # OR
    access_token="jwt_token",  # JWT token (ClickHouse Cloud)
    # OR
    token_provider=get_token,  # Callable returning JWT token
    
    # Database
    database="mydb",           # Default database
    
    # Security
    secure=True,               # Use HTTPS
    verify=True,               # Verify TLS certificate
    ca_cert="/path/to/ca.pem", # Custom CA certificate
    
    # Compression
    compress="lz4",            # "lz4", "zstd", "brotli", "gzip" or True
    
    # Timeouts
    connect_timeout=10,        # TCP connect timeout (seconds)
    send_receive_timeout=300,  # Read timeout (seconds)
    
    # Session
    session_id="uuid",         # ClickHouse session ID
    
    # Query defaults
    query_limit=0,             # Default LIMIT (0 = no limit)
)
```

--------------------------------

### Execute a Basic SELECT Query

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/driver-api.mdx

Demonstrates how to establish a client connection and execute a simple SELECT query. Shows how to access results as rows, column names, and column types.

```python
import clickhouse_connect

client = clickhouse_connect.get_client() 

# Simple SELECT query
result = client.query("SELECT name, database FROM system.tables LIMIT 3")

# Access results as rows
for row in result.result_rows:
    print(row)
# Output:
# ('CHARACTER_SETS', 'INFORMATION_SCHEMA')
# ('COLLATIONS', 'INFORMATION_SCHEMA')
# ('COLUMNS', 'INFORMATION_SCHEMA')

# Access column names and types
print(result.column_names)
# Output: ("name", "database")
print([col_type.name for col_type in result.column_types])
# Output: ['String', 'String']
```

--------------------------------

### Execute Query and Get PyArrow Table

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/async-client.md

Use `query_arrow()` to execute a SQL query and get the results as a PyArrow Table. This is useful for interoperability with other Arrow-compatible libraries.

```python
table = await client.query_arrow("SELECT * FROM users")
print(table.schema)
```

--------------------------------

### Create, Show, and Drop Table with Client.command

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/driver-api.mdx

Demonstrates using the command method for Data Definition Language (DDL) operations like creating, showing the definition of, and dropping tables.

```python
import clickhouse_connect

client = clickhouse_connect.get_client() 

# Create a table
result = client.command("CREATE TABLE test_command (col_1 String, col_2 DateTime) ENGINE MergeTree ORDER BY tuple()")
print(result)  # Returns QuerySummary with query_id

# Show table definition
result = client.command("SHOW CREATE TABLE test_command")
print(result)
# Output:
# CREATE TABLE default.test_command
# (
#     `col_1` String,
#     `col_2` DateTime
# )
# ENGINE = MergeTree
# ORDER BY tuple()

# Drop table
client.command("DROP TABLE test_command")
```

--------------------------------

### Clone the repository

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/CONTRIBUTING.md

Initial step to obtain the project source code.

```bash
git clone https://github.com/[YOUR_USERNAME]/clickhouse-connect
cd clickhouse-connect
```

--------------------------------

### get_client_setting()

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/async-client.md

Gets a client-level setting. This is a synchronous method.

```APIDOC
## get_client_setting()

### Description
Gets a client-level setting. This is a synchronous method.

### Method
`get_client_setting`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Parameters
* **key** (str) - The key of the setting to retrieve.

### Response
#### Success Response
* **(str | None)** - The value of the setting, or None if not found.
```

--------------------------------

### QueryResult.column_names

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/query-result.md

Get the names of the columns returned by the query in their original order.

```APIDOC
## QueryResult.column_names

### Description
Column names from the query result.

### Returns
`tuple[str, ...]` — ordered column names

### Example
```python
result = client.query("SELECT id, name, email FROM users")
print(result.column_names)  # ('id', 'name', 'email')

# Index lookup
if 'email' in result.column_names:
    email_idx = result.column_names.index('email')
    emails = [row[email_idx] for row in result.result_rows]
```
```

--------------------------------

### Basic ClickHouse Connect Usage

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/README.md

Demonstrates creating a client, executing a SELECT query, printing column names and rows, inserting data, and converting query results to a pandas DataFrame.

```python
import clickhouse_connect

# Create client
client = clickhouse_connect.get_client(
    host="localhost",
    username="default",
    password=""
)

# Execute query
result = client.query("SELECT name, age FROM users LIMIT 10")
print(result.column_names)  # ('name', 'age')
for row in result.result_rows:
    print(row)

# Insert data
client.insert(
    "users",
    data=[[1, "Alice", 30], [2, "Bob", 25]],
    column_names=["id", "name", "age"],
    column_type_names=["UInt32", "String", "UInt8"]
)

# Get as DataFrame
import pandas as pd
df = client.query_df("SELECT * FROM users")

# Close
client.close()
```

--------------------------------

### Insert Pandas DataFrame

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/advanced-inserting.mdx

Use `insert_df` to insert data from a Pandas DataFrame. Ensure you have Pandas installed and imported.

```python
import clickhouse_connect
import pandas as pd

client = clickhouse_connect.get_client()

df = pd.DataFrame({
    "id": [1, 2, 3],
    "name": ["Alice", "Bob", "Joe"],
    "age": [25, 30, 28],
})

client.insert_df("users", df)
```

--------------------------------

### Execute Commands with Parameters using Client.command

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/driver-api.mdx

Illustrates how to pass parameters to SQL commands executed via the command method, supporting both client-side and server-side parameterization.

```python
import clickhouse_connect

client = clickhouse_connect.get_client()

# Using client-side parameters
table_name = "system"
result = client.command(
    "SELECT count() FROM system.tables WHERE database = %(db)s",
    parameters={"db": table_name}
)

# Using server-side parameters
result = client.command(
    "SELECT count() FROM system.tables WHERE database = {db:String}",
    parameters={"db": "system"}
)
```

--------------------------------

### Alembic Autogenerate Output

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/clickhouse_connect/cc_sqlalchemy/alembic/WORKED_EXAMPLE.md

Example output from Alembic's autogenerate command, indicating a detected column addition.

```text
Detected added column 'events.details'
```

--------------------------------

### Execute Query with Client-Side Parameters

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/driver-api.mdx

Shows how to use client-side parameters for queries, supporting both dictionary (printf-style) and tuple parameter formats.

```python
import clickhouse_connect

client = clickhouse_connect.get_client()

# Using dictionary parameters (printf-style)
query = "SELECT * FROM system.tables WHERE database = %(db)s AND name LIKE %(pattern)s"
parameters = {"db": "system", "pattern": "%query%"}
result = client.query(query, parameters=parameters)

# Using tuple parameters
query = "SELECT * FROM system.tables WHERE database = %s LIMIT %s"
parameters = ("system", 5)
result = client.query(query, parameters=parameters)
```

--------------------------------

### Insert Pandas DataFrame

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/async-client.md

Use this method to efficiently insert data from a pandas DataFrame into a ClickHouse table. Ensure pandas is installed.

```python
import pandas as pd

df = pd.DataFrame({
    "id": [1, 2, 3],
    "name": ["Alice", "Bob", "Charlie"]
})

summary = await client.insert_df("users", df)
```

--------------------------------

### Compile Cython extensions

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/CONTRIBUTING.md

Build extensions for performance and register SQLAlchemy entrypoints.

```bash
python setup.py build_ext --inplace
```

```bash
python setup.py develop
```

--------------------------------

### Handling ClickHouseError

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/errors.md

Catch the base ClickHouseError to handle any ClickHouse-specific exceptions. This example demonstrates accessing the error code, name, and message.

```python
try:
    client.query("SELECT * FROM nonexistent_table")
except clickhouse_connect.driver.exceptions.Error as e:
    print(f"Code: {e.code}, Name: {e.name}")
    print(f"Message: {e.args[0]}")
```

--------------------------------

### Configure Compression for ClickHouse Connect Client

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/configuration.md

Set compression for queries and inserts. Use True for auto (lz4), a string for specific algorithms (lz4, zstd, brotli, gzip, deflate), or False to disable.

```python
# Auto compression (LZ4)
client = clickhouse_connect.get_client(host="localhost", compress=True)
```

```python
# Specific compression
client = clickhouse_connect.get_client(host="localhost", compress="zstd")
```

```python
# No compression
client = clickhouse_connect.get_client(host="localhost", compress=False)
```

--------------------------------

### Get Row Count

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/dbapi.md

Accesses the number of rows affected by the last query or the number of rows in the result set for SELECT statements.

```python
cursor.execute("INSERT INTO users VALUES (1, 'Alice')")
print(f"Inserted {cursor.rowcount} rows")

cursor.execute("SELECT * FROM users")
print(f"Selected {cursor.rowcount} rows")
```

--------------------------------

### Async ClickHouse Connect Usage

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/README.md

Shows how to create an asynchronous client, execute queries, and stream large results using async iterators. Ensure asyncio is imported.

```python
import asyncio
import clickhouse_connect

async def main():
    # Create async client
    client = await clickhouse_connect.get_async_client(
        host="localhost",
        username="default"
    )
    
    # Execute query
    result = await client.query("SELECT * FROM users LIMIT 10")
    print(result.result_rows)
    
    # Stream large result
    async for row_block in await client.query_row_block_stream("SELECT * FROM huge_table"):
        for row in row_block:
            print(row)
    
    await client.close()

asyncio.run(main())
```

--------------------------------

### ClickHouse DB-API Connection Class

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/dbapi.md

Defines the DB-API 2.0 Connection object for ClickHouse. No setup is required beyond importing the class.

```python
class Connection:
    """DB-API 2.0 Connection to ClickHouse"""
```

--------------------------------

### Get Column Metadata

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/EXAMPLES.md

Retrieve schema information for a table without fetching data. This allows inspection of column names, types, and nullability.

```python
# Get schema information
result = client.query("SELECT * FROM users LIMIT 0")  # No data, just schema

for col_name, col_type in zip(result.column_names, result.column_types):
    print(f"{col_name}: {col_type.name}")
    
    # Check if nullable
    if col_type.nullable:
        print(f"  (nullable)")
```

--------------------------------

### Basic DB-API Connection and Query

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/dbapi.md

Demonstrates establishing a connection to ClickHouse using DB-API, executing a query with parameter substitution, and fetching results. Ensure connection parameters are correctly configured.

```python
import clickhouse_connect.dbapi as dbapi

# Connect
conn = dbapi.connect(
    host="localhost",
    database="mydb",
    username="user",
    password="pass"
)

try:
    cursor = conn.cursor()
    
    # Execute query
    cursor.execute("SELECT * FROM users WHERE age > %(min_age)s", {"min_age": 18})
    
    # Fetch results
    rows = cursor.fetchall()
    for row in rows:
        print(row)
    
    cursor.close()
finally:
    conn.close()
```

--------------------------------

### Retrieving Column Names

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/query-result.md

Gets the ordered tuple of column names from the query result. Useful for understanding the structure of the data and for index-based lookups.

```python
result = client.query("SELECT id, name, email FROM users")
print(result.column_names)  # ('id', 'name', 'email')

# Index lookup
if 'email' in result.column_names:
    email_idx = result.column_names.index('email')
    emails = [row[email_idx] for row in result.result_rows]
```

--------------------------------

### Execute Commands with Settings using Client.command

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/driver-api.mdx

Demonstrates how to apply specific ClickHouse settings to a command execution using the command method's settings parameter.

```python
import clickhouse_connect

client = clickhouse_connect.get_client()

# Execute command with specific settings
result = client.command(
    "OPTIMIZE TABLE large_table FINAL",
    settings={"optimize_throw_if_noop": 1}
)
```

--------------------------------

### Add Column with ClickHouse Settings

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/clickhouse_connect/cc_sqlalchemy/alembic/WORKED_EXAMPLE.md

Use `op.add_column` to add a new column to a table. This example demonstrates setting placement with `clickhouse_after` and specifying `alter_sync` in `clickhouse_settings`.

```python
from alembic import op
from sqlalchemy import Column, text
from clickhouse_connect.cc_sqlalchemy import types

op.add_column(
    "events",
    Column(
        "payload",
        types.String(),
        server_default=text("'{}'"),
        clickhouse_after="id",
    ),
    schema="alembic_demo",
    if_not_exists=True,
    clickhouse_settings={"alter_sync": 2},
)
```

--------------------------------

### ClickHouse Connect Client Cleanup with Context Manager

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/driver-api.mdx

Utilize the `with` statement for automatic client management, ensuring the client is properly closed even if errors occur. This is a concise and safe way to handle client lifecycle.

```python
with clickhouse_connect.get_client(host='my-host', username='default', password='password') as client:
    result = client.query('SELECT 1')
```

--------------------------------

### Stream Pandas DataFrames with Context Manager

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/advanced-querying.mdx

Use the query_df_stream method to get query results as Pandas DataFrames. The StreamContext object can be used in a deferred fashion.

```python
df_stream = client.query_df_stream('SELECT * FROM hits')
column_names = df_stream.source.column_names
with df_stream:
    for df in df_stream:
        <do something with the pandas DataFrame>
```

--------------------------------

### Avoid Repeatedly Creating ClickHouse Connect Clients

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/driver-api.mdx

Creating a new client for each query is inefficient due to expensive initialization overhead. This pattern should be avoided.

```python
# BAD: Creates 1000 clients with expensive initialization overhead
for i in range(1000):
    client = clickhouse_connect.get_client(host='my-host', username='default', password='password')
    result = client.query('SELECT count() FROM users')
    client.close()
```

--------------------------------

### Execute Query and Get NumPy Array

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/async-client.md

Use `query_np()` to execute a SQL query and obtain the results as a NumPy structured array. This is efficient for numerical computations.

```python
arr = await client.query_np("SELECT id, value FROM data LIMIT 1000")
print(arr['id'])
```

--------------------------------

### Initialize Alembic

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/clickhouse_connect/cc_sqlalchemy/alembic/WORKED_EXAMPLE.md

Initialize Alembic in the project directory. This command generates the necessary Alembic configuration files and directories.

```bash
alembic init alembic
```

--------------------------------

### Rewrite Declarative Base Import

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/clickhouse_connect/cc_sqlalchemy/MIGRATING_FROM_CLICKHOUSE_SQLALCHEMY.md

Use standard SQLAlchemy declarative setup. For a version-generic form across SQLAlchemy 1.4 and 2.x, import 'declarative_base' from 'sqlalchemy.orm'.

```python
from sqlalchemy.orm import declarative_base

Base = declarative_base()
```

--------------------------------

### Execute a Query with ClickHouse Settings

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/EXAMPLES.md

Demonstrates how to apply ClickHouse server-side settings to a query, such as setting a maximum execution time or a limit on rows to read.

```python
# Set query timeout
result = client.query(
    "SELECT COUNT(*) FROM huge_table",
    settings={
        "max_execution_time": 30,  # 30 second timeout
        "max_rows_to_read": 1000000  # Safety limit
    }
)
```

--------------------------------

### Insert Pandas DataFrame into ClickHouse Table

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/client.md

Insert data from a pandas DataFrame into a ClickHouse table. Ensure pandas is installed. Column names are automatically inferred from the DataFrame.

```python
import pandas as pd

df = pd.DataFrame({
    "id": [1, 2, 3],
    "name": ["Alice", "Bob", "Charlie"],
    "age": [30, 25, 28]
})

client.insert_df("my_table", df)
```

--------------------------------

### query_np()

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/client.md

Executes a SQL query and returns the results as a NumPy structured array. Requires NumPy to be installed. Supports query parameters, settings, and options for NumPy conversion.

```APIDOC
## query_np()

### Description
Executes query and returns results as a NumPy structured array.

### Method
`query_np`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Parameters
- **query** (str | bytes) - Required - SQL query
- **parameters** (dict[str, Any] | None) - Optional - Query parameters
- **settings** (dict[str, Any] | None) - Optional - ClickHouse settings
- **use_numpy** (bool | None) - Optional - Force NumPy conversion
- **kwargs** - Additional arguments passed to `create_query_context()`

### Returns
`numpy.ndarray` (structured array with named fields)

### Raises
- `ImportError` — if numpy not installed
- `DataError` — if query contains variable-length string columns without `max_str_len` parameter

### Request Example
```python
arr = client.query_np("SELECT id, value FROM data WHERE id < 1000")
print(arr.dtype)  # [('id', '<i8'), ('value', '<f8')]
print(arr['id'])  # array([1, 2, 3, ...])
```
```

--------------------------------

### Reuse a Single ClickHouse Connect Client

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/driver-api.mdx

Create a client once at application startup and reuse it for all subsequent queries to avoid expensive initialization overhead. Ensure the client is closed on application shutdown.

```python
import clickhouse_connect

# Create once at startup
client = clickhouse_connect.get_client(host='my-host', username='default', password='password')

# Reuse for all queries
for i in range(1000):
    result = client.query('SELECT count() FROM users')

# Close on shutdown
client.close()
```

--------------------------------

### Client-side Binding with Sequence (Tuple)

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/driver-api.mdx

Perform client-side binding using a Python sequence (tuple) for parameter substitution. This example demonstrates binding Float64 and IPv4Address types.

```python
import ipaddress

parameters = (35200.44, ipaddress.IPv4Address(0x443d04fe))
client.query('SELECT * FROM some_table WHERE metric >= %s AND ip_address = %s', parameters=parameters)
```

```sql
SELECT *
FROM some_table
WHERE metric >= 35200.44
  AND ip_address = '68.61.4.254\''
```

--------------------------------

### Connection & Configuration Methods

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/README.md

Methods for creating, configuring, and managing client connections, including setting access tokens and closing resources.

```APIDOC
## Connection & Configuration

### Description
Methods for creating, configuring, and managing client connections, including setting access tokens and closing resources.

### Methods
- `create_client()`: Create synchronous client.
- `create_async_client()`: Create async client (requires aiohttp).
- `set_client_setting()`: Set client-level option.
- `get_client_setting()`: Get client-level option.
- `set_access_token()`: Update JWT token.
- `close()`: Close client and cleanup resources.

See [Configuration](configuration.md) for all parameters and [Client API](api-reference/client.md) for methods.
```

--------------------------------

### Stream Individual Query Rows

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/client.md

Use `query_rows_stream` to get a generator yielding individual row tuples. This method is ideal when you need to process each row independently as it arrives.

```python
for row in client.query_rows_stream("SELECT id, name FROM users"):
    user_id, user_name = row
    process_user(user_id, user_name)
```

--------------------------------

### Retrieving ClickHouse Query ID

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/query-result.md

Gets the unique ClickHouse query ID for the result. This ID can be used to track the query in system logs for debugging and monitoring purposes.

```python
result = client.query("SELECT * FROM users")
qid = result.query_id
print(f"Query ID: {qid}")

# Later, check query logs
logs = client.query(f"SELECT * FROM system.query_log WHERE query_id = '{qid}'")
```

--------------------------------

### Create a Table

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/index.mdx

Execute a SQL CREATE TABLE command using the client's command method. This is useful for setting up your database schema.

```python
client.command('CREATE TABLE new_table (key UInt32, value String, metric Float64) ENGINE MergeTree ORDER BY key')
```

--------------------------------

### Execute Simple Queries Returning Single Values with Client.command

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/driver-api.mdx

Shows how to use the command method to retrieve single scalar values or simple results from the server, such as row counts or server versions.

```python
import clickhouse_connect

client = clickhouse_connect.get_client()

# Single value result
count = client.command("SELECT count() FROM system.tables")
print(count)
# Output: 151

# Server version
version = client.command("SELECT version()")
print(version)
# Output: "25.8.2.29"
```

--------------------------------

### query_df()

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/client.md

Executes a SQL query and returns the results as a pandas DataFrame. Requires pandas 2.0+ to be installed. Accepts query parameters, settings, and options for extended data types.

```APIDOC
## query_df()

### Description
Executes query and returns results as a pandas DataFrame. Requires pandas 2.0+.

### Method
`query_df`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Parameters
- **query** (str | bytes) - Required - SQL query
- **parameters** (dict[str, Any] | None) - Optional - Query parameters
- **settings** (dict[str, Any] | None) - Optional - ClickHouse settings
- **use_extended_dtypes** (bool | None) - Optional - Use pandas extension dtypes (nullable types)
- **kwargs** - Additional arguments passed to `create_query_context()`

### Returns
`pandas.DataFrame`

### Raises
- `ImportError` — if pandas not installed
- `DatabaseError` — on query errors

### Request Example
```python
import pandas as pd

df = client.query_df("SELECT * FROM users LIMIT 100")
print(df.dtypes)
print(df.head())
```
```

--------------------------------

### Binding DateTime64 Arguments

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/driver-api.mdx

Custom approaches for binding DateTime64 arguments with sub-second precision using the DT64Param class. Examples show both server-side and client-side binding with dictionaries and lists.

```python
query = 'SELECT {p1:DateTime64(3)}'  # Server-side binding with dictionary
parameters={'p1': DT64Param(dt_value)}
```

```python
query = 'SELECT %s as string, toDateTime64(%s,6) as dateTime' # Client-side binding with list 
parameters=['a string', DT64Param(datetime.now())]
```

```python
query = 'SELECT {p1:DateTime64(3)}, {a1:Array(DateTime(3))}'  # Server-side binding with dictionary
parameters={'p1_64': dt_value, 'a1_64': [dt_value1, dt_value2]}
```

--------------------------------

### Execute Query with Custom Settings

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/driver-api.mdx

Explains how to pass custom ClickHouse settings, such as `max_block_size` and `max_execution_time`, directly with the query execution.

```python
import clickhouse_connect

client = clickhouse_connect.get_client()

# Pass ClickHouse settings with the query
result = client.query(
    "SELECT sum(number) FROM numbers(1000000)",
    settings={
        "max_block_size": 100000,
        "max_execution_time": 30
    }
)
```

--------------------------------

### Configure Timezone Source for Sync Client

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/configuration.md

Set the timezone source for DateTime columns when using a synchronous client. This example demonstrates how to explicitly use the server's timezone.

```python
# Use server timezone
client = clickhouse_connect.get_client(
    host="localhost",
    tz_source="server"
)
```

--------------------------------

### Execute a Simple SELECT Query

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/EXAMPLES.md

Demonstrates how to connect to ClickHouse, execute a simple SELECT query, and access the results as column names and rows. Remember to close the client connection when done.

```python
import clickhouse_connect

# Create client
client = clickhouse_connect.get_client(host="localhost")

# Execute query
result = client.query("SELECT id, name, age FROM users LIMIT 10")

# Access results
print(result.column_names)     # ('id', 'name', 'age')
print(result.result_rows)      # [(1, 'Alice', 30), (2, 'Bob', 25), ...]

# Iterate rows
for id_val, name, age in result.result_rows:
    print(f"{name}: {age} years old")

client.close()
```

--------------------------------

### Stream Query Results as Column Blocks

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/client.md

Use `query_column_block_stream` to get a generator yielding column-oriented blocks. Each block is a list of columns, suitable for processing data column by column.

```python
for column_block in client.query_column_block_stream("SELECT * FROM huge_table"):
    # column_block is a list of columns: [[col1_vals], [col2_vals], ...]
    process_block(column_block)
```

--------------------------------

### Manage Docker environment

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/CONTRIBUTING.md

Control Docker-based ClickHouse instances for testing.

```bash
export CLICKHOUSE_CONNECT_TEST_DOCKER=0
```

```bash
docker compose up -d
```

--------------------------------

### Execute Query and Get Pandas DataFrame

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/async-client.md

Use `query_df()` to execute a SQL query and receive the results directly as a pandas DataFrame. This is convenient for data analysis and manipulation tasks.

```python
df = await client.query_df("SELECT * FROM users")
print(df.dtypes)
```

--------------------------------

### Accessing Result Set in Specified Format

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/query-result.md

Retrieves query results in the format specified by the `column_oriented` parameter during the query. Use this to get results as rows or columns based on your needs.

```python
# Row-oriented (default)
result = client.query("SELECT * FROM users", column_oriented=False)
# result.result_set == result.result_rows
for row in result.result_set:
    process_row(row)

# Column-oriented
result = client.query("SELECT * FROM users", column_oriented=True)
# result.result_set == result.result_columns
for column in result.result_set:
    process_column(column)
```

--------------------------------

### create_client()

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/_autodocs/api-reference/client.md

Creates a synchronous ClickHouse Connect HTTP client. This function allows for various connection configurations including host, authentication (username/password, token, token provider), database, interface, port, security settings, DSN, server settings, and custom headers.

```APIDOC
## create_client()

### Description
Creates a synchronous ClickHouse Connect HTTP client. This function allows for various connection configurations including host, authentication (username/password, token, token provider), database, interface, port, security settings, DSN, server settings, and custom headers.

### Method
Python Function

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

#### Function Parameters
- **host** (str | None) - Optional - Hostname or IP address of ClickHouse server
- **username** (str | None) - Optional - ClickHouse username; if not set, uses default user
- **password** (str) - Required - Password for username
- **access_token** (str | None) - Optional - JWT access token for ClickHouse Cloud; mutually exclusive with username/password
- **token_provider** (Callable[[], str] | None) - Optional - Callable returning JWT token; called on init and refresh; mutually exclusive with access_token and username/password
- **database** (str) - Required - Default database for connection
- **interface** (str | None) - Optional - Must be "http" or "https"; defaults based on port/secure
- **port** (int) - Required - ClickHouse HTTP/HTTPS port; defaults to 8123 (http) or 8443 (https)
- **secure** (bool | str) - Required - Use HTTPS/TLS; "true" enables TLS, overrides interface/port inference
- **dsn** (str | None) - Optional - Data Source Name (URL format); other params override DSN values
- **settings** (dict[str, Any] | None) - Optional - ClickHouse server settings for all queries
- **headers** (dict[str, str] | None) - Optional - Custom HTTP headers sent with every request
- **generic_args** (dict[str, Any] | None) - Optional - Internal; used for DB-API connection string parsing

**Additional kwargs** (HTTP client specific):
- `compress` (bool | str) - compression mode ("lz4", "zstd", "brotli", "gzip", or True for auto)
- `query_limit` (int) - default LIMIT on returned rows (0 = no limit)
- `connect_timeout` (int) - connection timeout in seconds
- `send_receive_timeout` (int) - read timeout in seconds
- `client_name` (str | None) - client name prepended to User-Agent header
- `verify` (bool | str) - verify TLS certificate ("proxy" for proxy cert verification)
- `ca_cert` (str | None) - path to CA certificate in .pem format, or "certifi"
- `client_cert` (str | None) - path to TLS client certificate in .pem format
- `client_cert_key` (str | None) - path to TLS client certificate private key
- `session_id` (str | None) - ClickHouse session ID
- `pool_mgr` (PoolManager | None) - custom urllib3 PoolManager for connection pooling
- `http_proxy` (str | None) - HTTP proxy address
- `https_proxy` (str | None) - HTTPS proxy address
- `server_host_name` (str | None) - server hostname for TLS certificate validation (useful with SSH tunnels)
- `tz_source` (Literal["auto", "server", "local"]) - how to determine fallback timezone for DateTime columns
- `tz_mode` (Literal["naive_utc", "aware", "schema"]) - timezone handling for UTC DateTime columns
- `autogenerate_session_id` (bool | None) - override "autogenerate_session_id" setting
- `form_encode_query_params` (bool) - send query parameters as form data instead of URL params

### Request Example
```python
import clickhouse_connect

# Basic connection
client = clickhouse_connect.get_client(host="localhost")

# With authentication
client = clickhouse_connect.get_client(
    host="clickhouse.example.com",
    username="user",
    password="pass",
    database="mydb",
    secure=True
)

# From DSN
client = clickhouse_connect.get_client(
    dsn="clickhousedb://user:pass@host:8443/mydb"
)

# With ClickHouse Cloud token
client = clickhouse_connect.get_client(
    host="abc123.clickhouse.cloud",
    access_token="my_jwt_token",
    secure=True
)
```

### Response
#### Success Response (Client Instance)
- Returns a `Client` instance (HttpClient).

#### Response Example
```python
# Example of a returned Client instance (type hint)
client: clickhouse_connect.driver.client.Client
```

### Raises
- `ProgrammingError` — if both token auth and username/password auth are provided; if invalid interface specified
```

--------------------------------

### Connect to ClickHouse Cloud Service

Source: https://github.com/clickhouse/clickhouse-connect/blob/main/docs/index.mdx

Connect to a ClickHouse Cloud service using TLS. This example requires a specific port (8443) and your cloud service's hostname, username, and password.

```python
import clickhouse_connect

client = clickhouse_connect.get_client(host='HOSTNAME.clickhouse.cloud', port=8443, username='default', password='your password')
```