ClickHouse Connect driver API

- 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")

All the key ClickHouse Connect Client "insert" and "select" methods accept an optional `settings` keyword argument to pass ClickHouse server [user settings](/docs/operations/settings/settings) for the included SQL statement. The `settings` argument should be a dictionary. Each item should be a ClickHouse setting name and its associated value. Note that values will be converted to strings when sent to the server as query parameters.

As with client level settings, ClickHouse Connect will drop any settings that the server marks as *readonly*=*1*, with an associated log message. Settings that apply only to queries via the ClickHouse HTTP interface are always valid. Those settings are described under the `get_client` [API](#settings-argument).

Example of using ClickHouse settings:

```
settings = {'merge_tree_min_rows_for_concurrent_read': 65535,
            'session_id': 'session_1234',
            'use_skip_indexes': False}
client.query("SELECT event_type, sum(timeout) FROM event_errors WHERE event_time > '2022-08-01'", settings=settings)
```

## Client `command` Method[​](#client-command-method "Direct link to client-command-method")

Use the `Client.command` method to send SQL queries to the ClickHouse server that don't normally return data or that return a single primitive or array value rather than a full dataset. This method takes the following parameters:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Type Default Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | cmd str *Required* A ClickHouse SQL statement that returns a single value or a single row of values.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | parameters dict or iterable *None* See [parameters description](#parameters-argument).| data str or bytes *None* Optional data to include with the command as the POST body.|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | settings dict *None* See [settings description](#settings-argument).| use\_database bool True Use the client database (specified when creating the client). False means the command will use the default ClickHouse server database for the connected user.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | external\_data ExternalData *None* An `ExternalData` object containing file or binary data to use with the query. See [Advanced Queries (External Data)](/docs/integrations/language-clients/python/advanced-querying#external-data)| transport\_settings dict *None* Optional dictionary of HTTP headers to include with this request. Each key-value pair is added as an HTTP header (e.g., `{'X-Custom-Header': 'value'}`). Useful for proxy authentication, request tracing, or passing headers required by intermediate infrastructure. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

### Command examples[​](#command-examples "Direct link to Command examples")

#### DDL statements[​](#ddl-statements "Direct link to DDL statements")

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

#### Simple queries returning single values[​](#simple-queries-returning-single-values "Direct link to Simple queries returning single values")

```
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"
```

#### Commands with parameters[​](#commands-with-parameters "Direct link to Commands with parameters")

```
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"}
)
```

#### Commands with settings[​](#commands-with-settings "Direct link to Commands with settings")

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

## Client `query` Method[​](#client-query-method "Direct link to client-query-method")

The `Client.query` method is the primary way to retrieve a single "batch" dataset from the ClickHouse server. It utilizes the Native ClickHouse format over HTTP to transmit large datasets (up to approximately one million rows) efficiently. This method takes the following parameters: