clickhouse读写数据库1-- pymysql 模块

 

 这里使用pymysql模块，
PyMySQL 连接 ClickHouse 时
需要指定正确的端口号才能成功建立连接，
而默认的端口号是 9004

1. 使用 pymysql模块向 clickhouse写数据的时候，占位符 必须都是 "%s"

2.tuple里面，数据类型要强制转为 str

3.records 是一个list,每个元素是一个tuple

 

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

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


import pymysql
import pandas as pd
import numpy as np


#  这里使用pymysql模块，PyMySQL 连接 ClickHouse 时需要指定正确的端口号才能成功建立连接，而默认的端口号是 9004
ch_db_connInfo = "172.16.xxx.xxx;xxx;xxx;xxx;utf8;9004"


dbconnInfo = ch_db_connInfo.split(";")
if len(dbconnInfo) == 6:
    ch_ip = dbconnInfo[0]
    ch_user = dbconnInfo[1]
    ch_password = dbconnInfo[2]
    ch_db = dbconnInfo[3]
    ch_charset = dbconnInfo[4]
    ch_port = int(dbconnInfo[5])

ch_conn = pymysql.connect(host=ch_ip, port=ch_port, user=ch_user, password=ch_password, db=ch_db, charset='utf8')
ch_cursor = ch_conn.cursor(cursor=pymysql.cursors.DictCursor)

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

## 执行一次查询
sql1=f"""select * from HENRY_TEST20230607 """
# execute 返回的是数据条数
res1 = ch_cursor.execute(sql1)
# fetchall 得到的是一个list
res2 = ch_cursor.fetchall()
print(res1)
print(res2)

## 执行一次插入操作.
# 插入1条数据
sql2 = """INSERT INTO db_center.HENRY_TEST20230607 
(HOLDER_CODE, HOLDER_NAME, HOLD_DATE_NEW, ADD_CNSC_DAYS, HOLD_VALUE, ISVALID) 
VALUES('000003', 'a3', date'2023-06-07', 1, 0.123456, 1) """
# VALUES('000002', 'a2', date'2023-06-07', 1, 0.123456, 1)
res3 = ch_cursor.execute(sql2)
print(res3)

# 插入多条数据...values 后面占位符全部是 %s,在传递数值的时候，都转为 str
sql4 = 'insert into HENRY_TEST20230607 (HOLDER_CODE, HOLDER_NAME, HOLD_DATE_NEW, ADD_CNSC_DAYS, HOLD_VALUE, ISVALID)  values (%s,%s,%s,%s,%s,%s) '
records = []
data1 = ('000014','a14',str(date), str(14), str(4.234), str(1))
data2 = ('000015','a15', str(date), str(15), str(5.234), str(1))
data3 = ("000016","a16", str(date), str(16), str(6.234), str(1))
data4 = ("000017","a17", str(date), str(17), str(7.234), str(1))
# records里面放的是一个tuple
records.append(data1)
records.append(data2)
records.append(data3)
records.append(data4)
res4 = ch_cursor.executemany(sql4,records)

print(res4)
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;
"""