Python3 使用 clickhouse-connect 操作 clickhouse

'''
版本：
Python 3.7 x86
clickhouse 24.6.1.3573
clickhouse-connect        0.6.22
'''

代码一：

# pip install clickhouse-connectimport clickhouse_connect# 准备参数
host = "192.168.1.112"
port = 8123
username = "default"
password = "123456"
database = "default"client = clickhouse_connect.get_client(host=host,port=port,database=database,username=username,password=password,)# To run a ClickHouse SQL command, use the client command method:
client.command('CREATE TABLE IF NOT EXISTS new_table (key UInt32, value String, metric Float64) ENGINE MergeTree ORDER BY key')# To insert batch data, use the client insert method with a two-dimensional array of rows and values:
row1 = [1000, 'String Value 1000', 5.233]
row2 = [2000, 'String Value 2000', -107.04]
data = [row1, row2]
client.insert('new_table', data, column_names=['key', 'value', 'metric'])# To retrieve data using ClickHouse SQL, use the client query method:
result = client.query('SELECT max(key), avg(metric) FROM new_table')
print(result.result_rows)
print(result.row_count)
for data in result.result_rows:print(data)result = client.query('SELECT version()')
print(result.result_rows)'''
输出：
[(2000, -50.90350000000001)]
1
(2000, -50.90350000000001)
[('24.6.1.3573',)]
'''

代码二：

# pip install clickhouse-connect==0.6.22# 建立连接
import clickhouse_connect# 准备参数
host = "192.168.1.112"
port = 8123
username = "default"
password = "123456"
database = "default"# 建立连接
client  = clickhouse_connect.get_client(host=host,port=port,database=database,username=username,password=password,
)print(client)
print(client.server_version)# 删除表
client.command('drop table if exists user')# 创建表
# client.command('CREATE TABLE user (id UInt32, name String, age UInt32) ENGINE MergeTree ORDER BY id')# 新增数据
table = "user"
client.command(f'CREATE TABLE {table} (id UInt32, name String, age UInt32) ENGINE MergeTree ORDER BY id')# 新增数据 方式一 (19.xx 版本报错)
zs = [1, '张三', 23]
ls = [2, '李四', 24]
ww = [3, '王五', 35]
data = [zs, ls, ww]
columns = ["id", "name", "age"]
client.insert(table, data, column_names=columns)# 新增数据 方式二
client.query(f'insert into user (id, name, age) VALUES {tuple(zs)},{tuple(ls)},{tuple(ww)}')# 根据ID查询数据
id = 1
result = client.query(f'SELECT * FROM {table} where id={id}')
print(result.result_rows)# 查询所有数据
result = client.query(f'SELECT * FROM {table}')
print(result.result_rows)# 根据ID修改数据
id = 1
client.command(f"ALTER TABLE {table} UPDATE name='张三333' where id={id}")
# time.sleep(1)# 根据ID删除数据
id = 1
client.command(f"ALTER TABLE {table} DELETE WHERE id={id}")# 分页查询数据
result = client.query(f'SELECT * FROM {table} limit 2 offset 0')
print(result.result_rows)# 排序查询数据
result = client.query(f'SELECT id FROM {table} order by age desc limit 2 offset 0')
print(result.result_rows)
print(type(result.result_rows))
print(result.row_count)# 删除数据库表
# client.command(f'drop table if exists {table}')'''
输出：
<clickhouse_connect.driver.httpclient.HttpClient object at 0x00E0DC70>
24.6.1.3573
[(1, '张三', 23), (1, '张三', 23)]
[(1, '张三', 23), (2, '李四', 24), (3, '王五', 35), (1, '张三', 23), (2, '李四', 24), (3, '王五', 35)]
[(1, '张三333', 23), (2, '李四', 24)]
[(3, '王五', 35), (3, '王五', 35)]
<class 'list'>
2
'''

'''
参考：
https://blog.csdn.net/weixin_44786530/article/details/128094447
https://clickhouse.com/docs/en/integrations/python
https://pypi.org/project/clickhouse-connect/
https://mp.weixin.qq.com/s?__biz=MzA5NjQ1ODYwNg==&mid=2659081458&idx=1&sn=95ca7c1adf0eba1421ccd6422b80e1be&chksm=8b253ef3bc52b7e5b98f0419071fc8a2d6dcc8b6d20c6d50ffeba3cfb02a79246d9611421d11&mpshare=1&srcid=0606ecmYCO4FuLilOwvc7m7T&sharer_shareinfo=9c182e686cfe331385095c34d65701ab&sharer_shareinfo_first=9c182e686cfe331385095c34d65701ab&from=singlemessage&scene=1&subscene=10000&sessionid=1717747371&clicktime=1717751317&enterid=1717751317&ascene=1&fasttmpl_type=0&fasttmpl_fullversion=7239299-zh_CN-zip&fasttmpl_flag=0&realreporttime=1717751317382&devicetype=android-31&version=28003156&nettype=3gnet&abtest_cookie=AAACAA%3D%3D&lang=zh_CN&countrycode=CN&exportkey=n_ChQIAhIQH4h%2Fk168vKXNgPX%2FZxvclRLpAQIE97dBBAEAAAAAAB03CLwTsbYAAAAOpnltbLcz9gKNyK89dVj0wy6ZQsWlWt6X%2BkfuORNeZIBVrmstO04DrL%2FxXNP%2Fp02eyj%2B8qQ2WiFZtFUqM99gLP2VbHQQHM%2FEy156wLLedPLkNrqMlyLG5RA%2B0p0pI7FNDlrQfS%2B1lCQpFKQues%2FV4kZpXZWEXJX%2BHDYkk1DMygykOhU%2Bq%2FMMNp1weD0T2COJe51joz3MOH3ZV1sevgSJhajIh58YHdp6kh6cC5BvI0jL8LvlWXWft2GKrivr36tEVkKXQHUMoGimJXwiYY%2Fd1Zndn&pass_ticket=kKrK%2FLOHNWxyOWDkqhpS1yHTm7uiHLqhE%2BnnXOTE%2BWIMedYdokcCq7%2BZGMl0r6ce&wx_header=3
'''