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

使用 clickhouse_driver 模块，读写 clickhouse 

1.clickhouse_driver 不支持 占位符的写法

2.data 里面的数据，类型要和数据库里面的类型一致，如果不一致，这里就要强制转换为一致

3.records是一个list，里面的每个元素是一个元组

 

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

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

from clickhouse_driver import Client
import pandas as pd
import numpy as np


ch_connInfo = '172.16.xx.xxx;user;password;dbname;utf8;9000'

db_clickhouse_info = ch_connInfo.split(";")
if len(db_clickhouse_info) == 6:
    ip = db_clickhouse_info[0]
    user = db_clickhouse_info[1]
    password = db_clickhouse_info[2]
    db = db_clickhouse_info[3]
    port = int(db_clickhouse_info[5])
db_ch_writer = Client(host=ip, port=port, database=db, user=user, password=password)

dt=20230607
date = pd.to_datetime(str(dt)[0:4] + '-' + str(dt)[4:6] + '-' + str(dt)[6:8])


sql1 = 'insert into HENRY_TEST20230607 (HOLDER_CODE, HOLDER_NAME, HOLD_DATE_NEW, ADD_CNSC_DAYS, HOLD_VALUE, ISVALID)  values '

records = [ ('000024','a24',date, 14, 4.234, 1),
            ('000025', 'a25', date, 14, 4.234, 1),
        ("000026","a16", date, 14, 4.234, 1),
        ("000027","a17", date, 14, 4.234, 1)]

"""
def execute(self, query, params=None, with_column_types=False,
            external_tables=None, query_id=None, settings=None,
            types_check=False, columnar=False):
"""

ret1 = db_ch_writer.execute(sql1,records)
print(ret1)

sql2 = f"""select * from HENRY_TEST20230607 where HOLD_DATE_NEW=date'{date}'"""
# ret2 是一个tuple,ret[0] 存放数据, ret[2] 存放属性名称
ret2 = db_ch_writer.execute(sql2,with_column_types=True)
print(ret2)

# ret3  是一个list
ret3 = db_ch_writer.execute(sql2)
print(ret3)

print("------------------------end")


"""
在测试环境的clickhouse库，创建一个表，包含了string,DateTime,Int64,Float64,数据类型
 ip; user; password; default_db; port 
'172.16.xx.xxx;user;password;dbname;9000'

CREATE TABLE db_center.HENRY_TEST20230607
(    
    `HOLDER_CODE` String,
    `HOLDER_NAME` String,
    `HOLD_DATE_NEW` DateTime,    
    `ADD_CNSC_DAYS` Nullable(Int64),
    `HOLD_VALUE` Nullable(Float64),
    `ISVALID` Int64,
    `CREATETIME` DateTime DEFAULT toDateTime(now()),
    `UPDATETIME` DateTime DEFAULT toDateTime(now())
)
ENGINE = ReplacingMergeTree
PARTITION BY toYYYYMM(HOLD_DATE_NEW)
ORDER BY (HOLDER_CODE)
SETTINGS index_granularity = 8192;


"""