Python clickhouse-driver 類庫使用學習總結

實踐環境

python3 .9.13

clickhouse-driver 0.2.9

實踐操作

# -*- coding:utf-8 -*-

import clickhouse_driver

if __name__ == '__main__':
    host = '192.168.88.131'
    port = 9000 # 注意，不能使用預設的8123
    username = 'testacc'
    password = 'test1234'
    database = 'default'

    # 連線方式1
    # conn = clickhouse_driver.connect(database = database,
    #                                  user = username,
    #                                  password=password,
    #                                  host = host,
    #                                  port = port)

    # 連線方式2
    connection_str = f'clickhouse://{username}:{password}@{host}:{port}/{database}'
    conn = clickhouse_driver.connect(connection_str)
    cursor = conn.cursor()

    cursor.execute('SHOW TABLES')
    res = cursor.fetchall()
    print(res) # 輸出形如 [('table1',), ('test',)]

    # 刪除表
    cursor.execute('DROP TABLE IF EXISTS test')
    print(cursor.fetchall()) # 輸出:[]

    cursor.execute('CREATE TABLE test (x Int32) ENGINE = Memory')
    print(cursor.fetchall()) # 輸出:[]
    #
    cursor.executemany('INSERT INTO test (x) VALUES', [{'x': 100}])
    print(cursor.rowcount) # 獲取execute* 產生記錄數 輸出：1
    #
    cursor.executemany('INSERT INTO test (x) VALUES', [[200]])
    print(cursor.rowcount)  # 輸出：1
    #
    cursor.execute('INSERT INTO test (x) SELECT * FROM system.numbers LIMIT %(limit)s', {'limit': 3})
    print(cursor.rowcount)  # 輸出：1

    cursor.execute('SELECT sum(x) AS sum_value FROM test')
    print(cursor.rowcount)  # 輸出：1
    print(cursor.columns_with_types) # 獲取查詢列名及型別，輸出：[('sum_value', 'Int64')]

    cursor.execute('SELECT * FROM test')
    print(cursor.rowcount)  # 輸出：5
    print(cursor.columns_with_types) # 輸出：[('x', 'Int32')]

    res = cursor.fetchall()
    print(res) # 輸出：[(100,), (200,), (0,), (1,), (2,)]

    print(cursor.fetchone()) #輸出：None

    #############################
    cursor.execute('SELECT * FROM test')
    print(cursor.fetchone())  # 輸出：(100,)

    # 僅取3條
    print(cursor.fetchmany(3))  # 輸出：[(200,), (0,), (1,)]

    #############################
    cursor.execute('SELECT * FROM test')
    print(cursor.fetchone())  # 輸出：(100,)

    print(cursor.fetchall())  # 輸出：[(200,), (0,), (1,), (2,)]

    cursor.close()
    conn.close()

說明：

conn = clickhouse_driver.connect(connection_str)

connection_str：

'clickhouse://[{username}:{password}@]{host}[:{port}][/{database}]'

其中，{database}預設為default

參考連結

https://pypi.org/project/clickhouse-driver/#description

https://clickhouse-driver.readthedocs.io/en/latest/dbapi.html#clickhouse_driver.dbapi.connect

https://clickhouse-driver.readthedocs.io/en/latest/dbapi.html#cursor