clickhouse读写数据库3-- clickhouse_driver 模块

这是用 百度的 文心一言 生成的代码。
刚开始2次都是错误的，
明确指出 clickhouse_driver 没有占位符，
 让AI重新生成。
重新生成了2次之后，才得到正确代码

 

 

#!/usr/bin/env python
# -*- coding: utf-8 -*-

# author:henry
# desc:整理 clickhouse 读写的范例，方便日后读写clickhouse库
# Date:20230607
import clickhouse_driver


## 这是用 百度的 文心一言 生成的代码。刚开始2次都是错误的，明确指出 clickhouse_driver 没有占位符， 让AI重新生成。重新生成了2次之后，才得到正确代码
# 创建连接 '172.16.xx.xxx;user;password;dbname;utf8;9000'

host = '172.16.xx.xx'
user = 'user'
password = 'password'
db_name = 'dbname'

# 连接clickhouse数据库
conn = clickhouse_driver.connect(
    host= host,
    port=9000,
    user= user,
    password= password,
    database= db_name
)

# 创建游标
cursor = conn.cursor()

# 插入数据的SQL语句，使用占位符
sql = "INSERT INTO HENRY_TEST2_20230607 (col1, col2, col3) VALUES "

# 待插入的数据，每个元素是一个元组，包含三个值
data1 = {'col1':4, 'col2':'henry1', 'col3':11}
data2 = {'col1':5, 'col2':'henry2', 'col3':22}
data3 = {'col1':6, 'col2':'henry3', 'col3':33}

records = []
records.append(data1)
records.append(data2)
records.append(data3)


"""
def executemany(self, operation, seq_of_parameters):
    Prepare a database operation (query or command) and then execute it
    against all parameter sequences found in the sequence
    `seq_of_parameters`.

    :param operation: query or command to execute.
    :param seq_of_parameters: sequences or mappings for execution.
    :return: None
    
def execute(self, operation, parameters=None):
    Prepare and execute a database operation (query or command).

    :param operation: query or command to execute.
    :param parameters: sequence or mapping that will be bound to
                       variables in the operation.
    :return: None
"""
# 使用executemany方法批量插入数据. execute 也可以插入数据
cursor.executemany(sql, records)
#cursor.execute(sql, records)

# 提交事务
conn.commit()

# 关闭连接
conn.close()

"""
CREATE TABLE db_center.HENRY_TEST2_20230607
(    
    `col1` Int64,
    `col2` String,
    `col3` Int64
)
ENGINE = ReplacingMergeTree
ORDER BY (col2)
SETTINGS index_granularity = 8192;

"""