python连接clickhouse常用的三种方式

推荐运行环境

• python 3.8.3
• clickhouse_driver==0.2.3
• clickhouse_sqlalchemy==0.2.0
• sqlalchemy==1.4.32

一、clickhouse_driver连接的两种方式

注意端口都使用tcp端口9000

1.Client

from clickhouse_driver import Client

client = Client(host=host, port=9000, database=database,user=user ,password=pw)
sql = 'SHOW TABLES'
res = client.execute(sql)

2.connect

from clickhouse_driver import connect

#账号:密码@主机名:端口号/数据库
conn = connect(f'clickhouse://{user}:{pw}@{host}:9000/{database}')
cursor = conn.cursor()
cursor.execute('SHOW TABLES')

二、clickhouse_sqlalchemy连接方式

使用较复杂，推荐使用上述两种，注意使用端口为http端口8123。

from clickhouse_sqlalchemy import make_session
from sqlalchemy import create_engine
import pandas as pd

conf = {
    "user": "xxx",
    "password": "xxx",
    "server_host": "xx.xxx.xx.xxx",
    "port": "8123",
    "db": "xxx"
}

connection = 'clickhouse://{user}:{password}@{server_host}:{port}/{db}'.format(**conf)
engine = create_engine(connection, pool_size=100, pool_recycle=3600, pool_timeout=20)

sql = 'SHOW TABLES'

session = make_session(engine)
cursor = session.execute(sql)
try:
    fields = cursor._metadata.keys
    df = pd.DataFrame([dict(zip(fields, item)) for item in cursor.fetchall()])
finally:
    cursor.close()
    session.close()