Supported types

Each ClickHouse type is deserialized to a corresponding Python type when SELECT queries are prepared. When serializing INSERT queries, clickhouse-driver accepts a broader range of Python types. The following ClickHouse types are supported by clickhouse-driver:

[U]Int8/16/32/64

INSERT types: int, long.

SELECT type: int.

Float32/64

INSERT types: float, int, long.

SELECT type: float.

Date

INSERT types: date, datetime.

SELECT type: date.

DateTime(‘timezone’)/DateTime64(‘timezone’)

Timezone support is new in version 0.0.11. DateTime64 support is new in version 0.1.3.

INSERT types: datetime, int, long.

Integers are interpreted as seconds without timezone (UNIX timestamps). Integers can be used when insertion of datetime column is a bottleneck.

SELECT type: datetime.

Setting use_client_time_zone is taken into consideration.

You can cast DateTime column to integers if you are facing performance issues when selecting large amount of rows.

Due to Python’s current limitations minimal DateTime64 resolution is one microsecond.

String/FixedString(N)

INSERT types: str/basestring, bytes. See note below.

SELECT type: str/basestring, bytes. See note below.

String column is encoded/decoded with encoding specified by strings_encoding setting. Default encoding is UTF-8.

You can specify custom encoding:

>>> settings = {'strings_encoding': 'cp1251'}
>>> rows = client.execute(
...     'SELECT * FROM table_with_strings',
...     settings=settings
... )

Encoding is applied to all string fields in query.

String columns can be returned without any decoding. In this case return values are bytes:

>>> settings = {'strings_as_bytes': True}
>>> rows = client.execute(
...     'SELECT * FROM table_with_strings',
...     settings=settings
... )

If a column has FixedString type, upon returning from SELECT it may contain trailing zeroes in accordance with ClickHouse’s storage format. Trailing zeroes are stripped by driver for convenience.

During SELECT, if a string cannot be decoded with specified encoding, it will return as bytes.

During INSERT, if strings_as_bytes setting is not specified and string cannot be encoded with encoding, a UnicodeEncodeError will be raised.

Enum8/16

INSERT types: Enum, int, long, str/basestring.

SELECT type: str/basestring.

>>> from enum import IntEnum
>>>
>>> class MyEnum(IntEnum):
...     foo = 1
...     bar = 2
...
>>> client.execute('DROP TABLE IF EXISTS test')
[]
>>> client.execute('''
...     CREATE TABLE test
...     (
...         x Enum8('foo' = 1, 'bar' = 2)
...     ) ENGINE = Memory
... ''')
[]
>>> client.execute(
...     'INSERT INTO test (x) VALUES',
...     [{'x': MyEnum.foo}, {'x': 'bar'}, {'x': 1}]
... )
3
>>> client.execute('SELECT * FROM test')
[('foo',), ('bar',), ('foo',)]

For Python 2.7 enum34 package is used.

Currently clickhouse-driver can’t handle empty enum value due to Python’s Enum mechanics. Enum member name must be not empty. See issue and workaround.

Array(T)

INSERT types: list, tuple.

SELECT type: list.

Versions before 0.1.4: SELECT type: tuple.

>>> client.execute('DROP TABLE IF EXISTS test')
[]
>>> client.execute(
...     'CREATE TABLE test (x Array(Int32)) '
...     'ENGINE = Memory'
... )
[]
>>> client.execute(
...     'INSERT INTO test (x) VALUES',
...     [{'x': [10, 20, 30]}, {'x': [11, 21, 31]}]
... )
2
>>> client.execute('SELECT * FROM test')
[((10, 20, 30),), ((11, 21, 31),)]

Nullable(T)

INSERT types: NoneType, T.

SELECT type: NoneType, T.

UUID

INSERT types: str/basestring, UUID.

SELECT type: UUID.

Decimal

New in version 0.0.16.

INSERT types: Decimal, float, int, long.

SELECT type: Decimal.

IPv4/IPv6

New in version 0.0.19.

INSERT types: IPv4Address/IPv6Address, int, long, str/basestring.

SELECT type: IPv4Address/IPv6Address.

>>> from ipaddress import IPv4Address, IPv6Address
>>>
>>> client.execute('DROP TABLE IF EXISTS test')
[]
>>> client.execute(
...     'CREATE TABLE test (x IPv4) '
...     'ENGINE = Memory'
... )
[]
>>> client.execute(
...     'INSERT INTO test (x) VALUES', [
...     {'x': '192.168.253.42'},
...     {'x': 167772161},
...     {'x': IPv4Address('192.168.253.42')}
... ])
3
>>> client.execute('SELECT * FROM test')
[(IPv4Address('192.168.253.42'),), (IPv4Address('10.0.0.1'),), (IPv4Address('192.168.253.42'),)]
>>>
>>> client.execute('DROP TABLE IF EXISTS test')
[]
>>> client.execute(
...     'CREATE TABLE test (x IPv6) '
...     'ENGINE = Memory'
... )
[]
>>> client.execute(
...     'INSERT INTO test (x) VALUES', [
...     {'x': '79f4:e698:45de:a59b:2765:28e3:8d3a:35ae'},
...     {'x': IPv6Address('12ff:0000:0000:0000:0000:0000:0000:0001')},
...     {'x': b"y\xf4\xe6\x98E\xde\xa5\x9b'e(\xe3\x8d:5\xae"}
... ])
3
>>> client.execute('SELECT * FROM test')
[(IPv6Address('79f4:e698:45de:a59b:2765:28e3:8d3a:35ae'),), (IPv6Address('12ff::1'),), (IPv6Address('79f4:e698:45de:a59b:2765:28e3:8d3a:35ae'),)]
>>>

For Python 2.7 ipaddress package is used.

LowCardinality(T)

New in version 0.0.20.

INSERT types: T.

SELECT type: T.

SimpleAggregateFunction(F, T)

New in version 0.0.21.

INSERT types: T.

SELECT type: T.

AggregateFunctions for AggregatingMergeTree Engine are not supported.

Tuple(T1, T2, …)

New in version 0.1.4.

INSERT types: list, tuple.

SELECT type: tuple.