API¶

This part of the documentation covers basic classes of the driver: Client, Connection and others.

Client¶

class clickhouse_driver.Client(*args, **kwargs)¶

Client for communication with the ClickHouse server. Single connection is established per each connected instance of the client.

Parameters

settings – Dictionary of settings that passed to every query (except for the client settings, see below). Defaults to None (no additional settings). See all available settings in ClickHouse docs.
**kwargs – All other args are passed to the Connection constructor.

The following keys when passed in settings are used for configuring the client itself:

insert_block_size – chunk size to split rows for INSERT. Defaults to 1048576.

strings_as_bytes – turns off string column encoding/decoding.

strings_encoding – specifies string encoding. UTF-8 by default.

use_numpy – Use NumPy for columns reading. New in version
0.2.0.

opentelemetry_traceparent – OpenTelemetry traceparent header as
described by W3C Trace Context recommendation. New in version 0.2.2.

opentelemetry_tracestate – OpenTelemetry tracestate header as
described by W3C Trace Context recommendation. New in version 0.2.2.

quota_key – A string to differentiate quotas when the user have
keyed quotas configured on server. New in version 0.2.3.

input_format_null_as_default – Initialize null fields with
default values if data type of this field is not nullable. Does not work for NumPy. Default: False. New in version 0.2.4.

round_robin – If alt_hosts are provided the query will be
executed on host picked with round-robin algorithm. New in version 0.2.5.

disconnect_connection()¶: Disconnects from the server.

execute(query, params=None, with_column_types=False, external_tables=None, query_id=None, settings=None, types_check=False, columnar=False)¶

Executes query.

Establishes new connection if it wasn’t established yet. After query execution connection remains intact for next queries. If connection can’t be reused it will be closed and new connection will be created.

Parameters

query – query that will be send to server.
params – substitution parameters for SELECT queries and data for INSERT queries. Data for INSERT can be list, tuple or GeneratorType. Defaults to None (no parameters or data).
with_column_types – if specified column names and types will be returned alongside with result. Defaults to False.
external_tables – external tables to send. Defaults to None (no external tables).
query_id – the query identifier. If no query id specified ClickHouse server will generate it.
settings – dictionary of query settings. Defaults to None (no additional settings).
types_check – enables type checking of data for INSERT queries. Causes additional overhead. Defaults to False.
columnar – if specified the result of the SELECT query will be returned in column-oriented form. It also allows to INSERT data in columnar form. Defaults to False (row-like form).

Returns

number of inserted rows for INSERT queries with data. Returning rows count from INSERT FROM SELECT is not supported.
if with_column_types=False: list of tuples with rows/columns.
if with_column_types=True: tuple of 2 elements:
- The first element is list of tuples with rows/columns.
- The second element information is about columns: names and types.

execute_iter(query, params=None, with_column_types=False, external_tables=None, query_id=None, settings=None, types_check=False, chunk_size=1)¶

New in version 0.0.14.

Executes SELECT query with results streaming. See, Streaming results.

Parameters

query – query that will be send to server.
params – substitution parameters for SELECT queries and data for INSERT queries. Data for INSERT can be list, tuple or GeneratorType. Defaults to None (no parameters or data).
with_column_types – if specified column names and types will be returned alongside with result. Defaults to False.
external_tables – external tables to send. Defaults to None (no external tables).
query_id – the query identifier. If no query id specified ClickHouse server will generate it.
settings – dictionary of query settings. Defaults to None (no additional settings).
types_check – enables type checking of data for INSERT queries. Causes additional overhead. Defaults to False.
chunk_size – chunk query results.

Returns

IterQueryResult proxy.

execute_with_progress(query, params=None, with_column_types=False, external_tables=None, query_id=None, settings=None, types_check=False, columnar=False)¶

Executes SELECT query with progress information. See, Selecting data with progress statistics.

Parameters

query – query that will be send to server.
params – substitution parameters for SELECT queries and data for INSERT queries. Data for INSERT can be list, tuple or GeneratorType. Defaults to None (no parameters or data).
with_column_types – if specified column names and types will be returned alongside with result. Defaults to False.
external_tables – external tables to send. Defaults to None (no external tables).
query_id – the query identifier. If no query id specified ClickHouse server will generate it.
settings – dictionary of query settings. Defaults to None (no additional settings).
types_check – enables type checking of data for INSERT queries. Causes additional overhead. Defaults to False.
columnar – if specified the result will be returned in column-oriented form. Defaults to False (row-like form).

Returns

ProgressQueryResult proxy.

classmethod from_url(url)¶

Return a client configured from the given URL.

For example:

clickhouse://[user:password]@localhost:9000/default
clickhouses://[user:password]@localhost:9440/default

Three URL schemes are supported:: clickhouse:// creates a normal TCP socket connection clickhouses:// creates a SSL wrapped TCP socket connection

Any additional querystring arguments will be passed along to the Connection class’s initializer.

insert_dataframe(query, dataframe, external_tables=None, query_id=None, settings=None)¶

New in version 0.2.0.

Inserts pandas DataFrame with specified query.

Parameters

query – query that will be send to server.
dataframe – pandas DataFrame.
external_tables – external tables to send. Defaults to None (no external tables).
query_id – the query identifier. If no query id specified ClickHouse server will generate it.
settings – dictionary of query settings. Defaults to None (no additional settings).

Returns

number of inserted rows.

query_dataframe(query, params=None, external_tables=None, query_id=None, settings=None)¶

New in version 0.2.0.

Queries DataFrame with specified SELECT query.

Parameters

query – query that will be send to server.
params – substitution parameters. Defaults to None (no parameters or data).
external_tables – external tables to send. Defaults to None (no external tables).
query_id – the query identifier. If no query id specified ClickHouse server will generate it.
settings – dictionary of query settings. Defaults to None (no additional settings).

Returns

pandas DataFrame.

substitute_params(query, params, context)¶

Substitutes parameters into a provided query.

For example:

client = Client(...)

substituted_query = client.substitute_params(
    query='SELECT 1234, %(foo)s',
    params={'foo': 'bar'},
    context=client.connection.context
)

# prints: SELECT 1234, 'bar'
print(substituted_query)

Connection¶

class clickhouse_driver.connection.Connection(host, port=None, database='', user='default', password='', client_name='python-driver', connect_timeout=10, send_receive_timeout=300, sync_request_timeout=5, compress_block_size=1048576, compression=False, secure=False, verify=True, ssl_version=None, ca_certs=None, ciphers=None, keyfile=None, certfile=None, server_hostname=None, alt_hosts=None, settings_is_important=False)¶

Represents connection between client and ClickHouse server.

Parameters

host – host with running ClickHouse server.
port – port ClickHouse server is bound to. Defaults to 9000 if connection is not secured and to 9440 if connection is secured.
database – database connect to. Defaults to 'default'.
user – database user. Defaults to 'default'.
password – user’s password. Defaults to '' (no password).
client_name – this name will appear in server logs. Defaults to 'python-driver'.
connect_timeout – timeout for establishing connection. Defaults to 10 seconds.
send_receive_timeout – timeout for sending and receiving data. Defaults to 300 seconds.
sync_request_timeout – timeout for server ping. Defaults to 5 seconds.
compress_block_size – size of compressed block to send. Defaults to 1048576.
compression –
specifies whether or not use compression. Defaults to False. Possible choices:
- True is equivalent to 'lz4'.
- 'lz4'.
- 'lz4hc' high-compression variant of 'lz4'.
- 'zstd'.
secure – establish secure connection. Defaults to False.
verify – specifies whether a certificate is required and whether it will be validated after connection. Defaults to True.
ssl_version – see ssl.wrap_socket() docs.
ca_certs – see ssl.wrap_socket() docs.
ciphers – see ssl.wrap_socket() docs.
keyfile – see ssl.wrap_socket() docs.
certfile – see ssl.wrap_socket() docs.
server_hostname – Hostname to use in SSL Wrapper construction. Defaults to None which will send the passed host param during SSL initialization. This param may be used when connecting over an SSH tunnel to correctly identify the desired server via SNI.
alt_hosts – list of alternative hosts for connection. Example: alt_hosts=host1:port1,host2:port2.
settings_is_important – False means unknown settings will be ignored, True means that the query will fail with UNKNOWN_SETTING error. Defaults to False.

disconnect()¶: Closes connection between server and client. Frees resources: e.g. closes socket.

QueryResult¶

class clickhouse_driver.result.QueryResult(packet_generator, with_column_types=False, columnar=False)¶

Stores query result from multiple blocks.

get_result()¶

Returns: stored query result.

ProgressQueryResult¶

class clickhouse_driver.result.ProgressQueryResult(*args, **kwargs)¶

Stores query result and progress information from multiple blocks. Provides iteration over query progress.

get_result()¶

Returns: stored query result.

IterQueryResult¶

class clickhouse_driver.result.IterQueryResult(packet_generator, with_column_types=False)¶: Provides iteration over returned data by chunks (streaming by chunks).

clickhouse-driver

Navigation

Related Topics

API¶

Client¶

Connection¶

QueryResult¶

ProgressQueryResult¶

IterQueryResult¶