ClickHouse Python Client

How to install the ClickHouse Python driver, and connect to your Altinity.Cloud ClickHouse Cluster from a Python program or a Python console.

26 January 2023 · Read time 4 min

Overview - Python ClickHouse Client

This section covers the installation and use of the Python ClickHouse client.
After installation, you will be able to run use ClickHouse queries on any platform that will run Python3.

  • You must first have completed Creating Tables and Adding Data.
  • You have copied the Connection Details from your cluster.
  • Python version 3.7 (or later) is installed (this page is tested using Python 3.11.1)

More information


Installing the ClickHouse Python driver

Install ClickHouse drivers using the Python 3 PIP package installer:

pip install clickhouse-driver

Example Python program

The following Python program demonstrates:

  • Importing the clickhouse-driver library (that was previously installed by the Python 3 PIP package installer)
  • Connecting to your ClickHouse cluster example-cluster
  • Listing all of the tables that exist in the example-cluster
  • Listing the data in the example table events_local
  • Showing the version number of the Python clickhouse-driver (0.2.5)

Shortcut for experienced users

  • Python program filename: ClickHouse-example.py (copy and paste the code)
  • Update the program strings for the cluster name from Connection Details link on your cluster, such as: your-company-example yourpasswordhere
  • Run the command: python3 ClickHouse-example.py

Instructions

  1. Modify the following in the Python program ClickHouse-example.py as follows:

    • Change the cluster name, replacing your-company-example
    • Change password yourpasswordhere to your own ClickHouse cluster name

  1. Run the program from the terminal or your IDE:

    • python3 ClickHouse-example.py

Code Snippet 1 - ClickHouse-example.py


import clickhouse_driver
print(clickhouse_driver.__version__)

# No return characters:
# Replace your-company-example
# Replace yourpasswordhere 
# client = Client('example-cluster.your-company-example.altinity.cloud', user='admin', password='yourpasswordhere', port=9440, secure='y', verify=False)

# Connect to your cluster
from clickhouse_driver import Client
client = Client('example-cluster.your-company-example.altinity.cloud',
                user='admin',
                password='yourpasswordhere',
                port=9440,
                secure='y',
                verify=False)

# Show tables
tables = client.execute('SHOW TABLES in default')
print(tables)


# Show data
result = client.execute('SELECT * FROM default.events')
print(result)


# Show ClickHouse Python driver version
version = (clickhouse_driver.__version__)
print("ClickHouse Python version: ", version)

Python program response

[('events',), ('events_local',)]
[(datetime.date(2023, 1, 4), 1, 13, 'Example'), (datetime.date(2023, 1, 10), 1, 13, 'Example'), (datetime.date(2023, 1, 10), 1, 14, 'Example')]
ClickHouse Python version:  0.2.5

Python Console

This section shows how you can use the Python console to interactively connect to your ClickHouse cluster on Altinity and send SQL queries.

  • First copy your Connection Details from your cluster you want to communicate with.
  • Bring up the terminal and enter Python console mode, then copy and paste the following commands shown after the Python console prompt »>:
# Get into Python console mode
python3
>>>

# Getting the ClickHouse Python driver version number
>>> import clickhouse_driver
>>> print(clickhouse_driver.__version__)
0.2.5

# Connect to your ClickHouse cluster (replace <HOSTNAME> and <PASSWORD>)
>>> from clickhouse_driver import Client
>>> client = Client('<HOSTNAME>', user='admin', password=<PASSWORD>, port=9440, secure='y', verify=False)

# Confirm that the client object is created
>>> print(client)
<clickhouse_driver.client.Client object at 0x107730910>

# Show all tables
>>> client.execute('SHOW TABLES in default')
[('events',), ('events_local',)]

# Show data
>>> result = client.execute('SELECT * FROM default.events')
>>> print(result)
[(datetime.date(2023, 1, 4), 1, 13, 'Example'), (datetime.date(2023, 1, 10), 1, 13, 'Example'), (datetime.date(2023, 1, 10), 1, 14, 'Example')]

# ClickHouse Version 0.2.5 as of January 2023
version = (clickhouse_driver.__version__)
print("ClickHouse Python version: ", version)
ClickHouse Python version:  0.2.5

Checking your ClickHouse Version from PIP

To check your Python ClickHouse version installation from the terminal, enter:

  • pip show clickhouse-driver
username) ➜  ~ 
username) ➜  ~ pip show clickhouse-driver

        Name:  clickhouse-driver
     Version:  0.2.5
     Summary:  Python driver with native interface for ClickHouse
   Home-page:  https://github.com/mymarilyn/clickhouse-driver
      Author:  Konstantin Lebedev
Author-email:  kostyan.lebedev@gmail.com
     License:  MIT
    Location:  /Users/username/lib/python3.11/site-packages
    Requires:  pytz, tzlocal
 Required-by:

This concludes the Quick Start instructions for how to use the Python3 ClickHouse driver.


Related links


Last modified 2023.01.26: Getting Started rewrite