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, bytearray, bytes. See note below.

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

String column is encoded/decoded using UTF-8 encoding.

String column can be returned without decoding. 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 UTF-8 encoding, it will return as bytes.

During INSERT, if strings_as_bytes setting is not specified and string cannot be encoded with UTF-8, 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: 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.