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 forINSERT
. Defaults to1048576
.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 version0.2.0.
opentelemetry_traceparent
– OpenTelemetry traceparent header asdescribed by W3C Trace Context recommendation. New in version 0.2.2.
opentelemetry_tracestate
– OpenTelemetry tracestate header asdescribed by W3C Trace Context recommendation. New in version 0.2.2.
quota_key
– A string to differentiate quotas when the user havekeyed quotas configured on server. New in version 0.2.3.
input_format_null_as_default
– Initialize null fields withdefault 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
– Ifalt_hosts
are provided the query will beexecuted on host picked with round-robin algorithm. New in version 0.2.5.
namedtuple_as_json
– Controls named tuple and nested typesdeserialization. To interpret these column alongside with
allow_experimental_object_type=1
as Python tuple setnamedtuple_as_json
toFalse
. Default: True. New in version 0.2.6.
server_side_params
– Species on which side query parametersshould be rendered into placeholders. Default: False. Means that parameters are rendered on driver’s side. New in version 0.2.7.
- 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 toNone
(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 toNone
(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 toNone
(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, replace_nonwords=True)¶
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).replace_nonwords – boolean to replace non-words in column names to underscores. Defaults to
True
.
- 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, tcp_keepalive=False, client_revision=None)¶
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 to9440
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 toFalse
.tcp_keepalive – enables TCP keepalive on established connection. If is set to
True`
system keepalive settings are used. You can also specify custom keepalive setting with tuple:(idle_time_sec, interval_sec, probes)
. Defaults toFalse
.client_revision – can be used for client version downgrading. Defaults to
None
.
- disconnect()¶
Closes connection between server and client. Frees resources: e.g. closes socket.
QueryResult¶
ProgressQueryResult¶
IterQueryResult¶
- class clickhouse_driver.result.IterQueryResult(packet_generator, with_column_types=False)¶
Provides iteration over returned data by chunks (streaming by chunks).