使用python来对clickhouse进行操作
1.clickhouse-driver (mymarilyn/clickhouse-driver: ClickHouse Python Driver with native interface support (github.com))
纯客户端:
>>> from clickhouse_driver import Client >>> >>> client = Client('localhost') >>> >>> client.execute('SHOW TABLES') [('test',)] >>> client.execute('DROP TABLE IF EXISTS test') [] >>> client.execute('CREATE TABLE test (x Int32) ENGINE = Memory') [] >>> client.execute( ... 'INSERT INTO test (x) VALUES', ... [{'x': 100}] ... ) 1 >>> client.execute('INSERT INTO test (x) VALUES', [[200]]) 1 >>> client.execute( ... 'INSERT INTO test (x) ' ... 'SELECT * FROM system.numbers LIMIT %(limit)s', ... {'limit': 3} ... ) [] >>> client.execute('SELECT sum(x) FROM test') [(303,)]
使用数据库接口:
>>> from clickhouse_driver import connect >>> >>> conn = connect('clickhouse://localhost') >>> cursor = conn.cursor() >>> >>> cursor.execute('SHOW TABLES') >>> cursor.fetchall() [('test',)] >>> cursor.execute('DROP TABLE IF EXISTS test') >>> cursor.fetchall() [] >>> cursor.execute('CREATE TABLE test (x Int32) ENGINE = Memory') >>> cursor.fetchall() [] >>> cursor.executemany( ... 'INSERT INTO test (x) VALUES', ... [{'x': 100}] ... ) >>> cursor.rowcount 1 >>> cursor.executemany('INSERT INTO test (x) VALUES', [[200]]) >>> cursor.rowcount 1 >>> cursor.execute( ... 'INSERT INTO test (x) ' ... 'SELECT * FROM system.numbers LIMIT %(limit)s', ... {'limit': 3} ... ) >>> cursor.rowcount 0 >>> cursor.execute('SELECT sum(x) FROM test') >>> cursor.fetchall() [(303,)]
链接的使用使用的参数:
host: 地址
port: 端口
user: 用户名
password: 密码
database: 数据库名称
send_receive_timeout: 超时时间