ClickHouse Connect driver API

**✅ Good: Reuse a single client**

```
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()
```

**❌ Bad: Creating clients repeatedly**

```
# 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()
```

### Multi-threaded applications[​](#multi-threaded-applications "Direct link to Multi-threaded applications")

Note

Client instances are **NOT thread-safe** when using session IDs. By default, clients have an auto-generated session ID, and concurrent queries within the same session will raise a `ProgrammingError`.

To share a client across threads safely:

```
import clickhouse_connect
import threading

# Option 1: Disable sessions (recommended for shared clients)
client = clickhouse_connect.get_client(
    host='my-host',
    username='default',
    password='password',
    autogenerate_session_id=False  # Required for thread safety
)

def worker(thread_id):
    # All threads can now safely use the same client
    result = client.query(f"SELECT {thread_id}")
    print(f"Thread {thread_id}: {result.result_rows[0][0]}")


threads = [threading.Thread(target=worker, args=(i,)) for i in range(10)]
for t in threads:
    t.start()
for t in threads:
    t.join()

client.close()
# Output:
# Thread 0: 0
# Thread 7: 7
# Thread 1: 1
# Thread 9: 9
# Thread 4: 4
# Thread 2: 2
# Thread 8: 8
# Thread 5: 5
# Thread 6: 6
# Thread 3: 3
```

**Alternative for sessions:** If you need sessions (e.g., for temporary tables), create a separate client per thread:

```
def worker(thread_id):
    # Each thread gets its own client with isolated session
    client = clickhouse_connect.get_client(host='my-host', username='default', password='password')
    client.command('CREATE TEMPORARY TABLE temp (id UInt32) ENGINE = Memory')
    # ... use temp table ...
    client.close()
```

### Proper cleanup[​](#proper-cleanup "Direct link to Proper cleanup")

Always close clients at shutdown. Note that `client.close()` disposes the client and closes pooled HTTP connections only when the client owns its pool manager (for example, when created with custom TLS/proxy options). For the default shared pool, use `client.close_connections()` to proactively clear sockets; otherwise, connections are reclaimed automatically via idle expiration and at process exit.

```
client = clickhouse_connect.get_client(host='my-host', username='default', password='password')
try:
    result = client.query('SELECT 1')
finally:
    client.close()
```

Or use a context manager:

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

### When to use multiple clients[​](#when-to-use-multiple-clients "Direct link to When to use multiple clients")

Multiple clients are appropriate for:

- **Different servers**: One client per ClickHouse server or cluster
- **Different credentials**: Separate clients for different users or access levels
- **Different databases**: When you need to work with multiple databases
- **Isolated sessions**: When you need separate sessions for temporary tables or session-specific settings
- **Per-thread isolation**: When threads need independent sessions (as shown above)

## Common method arguments[​](#common-method-arguments "Direct link to Common method arguments")

Several client methods use one or both of the common `parameters` and `settings` arguments. These keyword arguments are described below.

### Parameters argument[​](#parameters-argument "Direct link to Parameters argument")

ClickHouse Connect Client `query*` and `command` methods accept an optional `parameters` keyword argument used for binding Python expressions to a ClickHouse value expression. Two sorts of binding are available.

#### Server-side binding[​](#server-side-binding "Direct link to Server-side binding")

ClickHouse supports [server side binding](/docs/interfaces/client#cli-queries-with-parameters) for most query values, where the bound value is sent separate from the query as an HTTP query parameter. ClickHouse Connect will add the appropriate query parameters if it detects a binding expression of the form `{<name>:<datatype>}`. For server side binding, the `parameters` argument should be a Python dictionary.

- Server-side binding with Python dictionary, DateTime value, and string value

```
import datetime

my_date = datetime.datetime(2022, 10, 1, 15, 20, 5)

parameters = {'table': 'my_table', 'v1': my_date, 'v2': "a string with a single quote'"}
client.query('SELECT * FROM {table:Identifier} WHERE date >= {v1:DateTime} AND string ILIKE {v2:String}', parameters=parameters)
```

This generates the following query on the server:

```
SELECT *
FROM my_table
WHERE date >= '2022-10-01 15:20:05'
  AND string ILIKE 'a string with a single quote\''
```

Note

Server-side binding is only supported (by the ClickHouse server) for `SELECT` queries. It doesn't work for `ALTER`, `DELETE`, `INSERT`, or other types of queries. This may change in the future; see <https://github.com/ClickHouse/ClickHouse/issues/42092>.

#### Client-side binding[​](#client-side-binding "Direct link to Client-side binding")

ClickHouse Connect also supports client-side parameter binding, which can allow more flexibility in generating templated SQL queries. For client-side binding, the `parameters` argument should be a dictionary or a sequence. Client-side binding uses the Python ["printf" style](https://docs.python.org/3/library/stdtypes.html#old-string-formatting) string formatting for parameter substitution.

Note that unlike server-side binding, client-side binding doesn't work for database identifiers such as database, table, or column names, since Python-style formatting can't distinguish between the different types of strings, and they need to be formatted differently (backticks or double quotes for database identifiers, single quotes for data values).

- Example with Python Dictionary, DateTime value and string escaping

```
import datetime

my_date = datetime.datetime(2022, 10, 1, 15, 20, 5)

parameters = {'v1': my_date, 'v2': "a string with a single quote'"}
client.query('SELECT * FROM my_table WHERE date >= %(v1)s AND string ILIKE %(v2)s', parameters=parameters)
```

This generates the following query on the server:

```
SELECT *
FROM my_table
WHERE date >= '2022-10-01 15:20:05'
  AND string ILIKE 'a string with a single quote\''
```

- Example with Python Sequence (Tuple), Float64, and IPv4Address

```
import ipaddress

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

This generates the following query on the server:

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

Note

To bind DateTime64 arguments (ClickHouse types with sub-second precision) requires one of two custom approaches:

- Wrap the Python `datetime.datetime` value in the new DT64Param class, e.g.

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

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

  - If using a dictionary of parameter values, append the string `_64` to the parameter name

  ```
    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]}
  ```

### Settings argument[​](#settings-argument-1 "Direct link to Settings argument")