ClickHouse Python Client
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)
- Altinity Blog: ClickHouse and Python: Getting to know the ClickHouse-driver Client (Feb1.19)
- ClickHouse Driver 0.2.5 (Dec 2023)
- GitHub: clickhouse-driver (Dec 2023)
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:
- Run the command:
Modify the following in the Python program ClickHouse-example.py as follows:
- Change the cluster name, replacing
- Change password
yourpasswordhereto your own ClickHouse cluster name
- Change the cluster name, replacing
Run the program from the terminal or your IDE:
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
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: email@example.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.