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’)¶

Timezone support is new in version 0.0.11.

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.

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}]
... )
>>> 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]}]
... )
>>> 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')}
... ])
>>> 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"}
... ])
>>> 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.