python连接clickhouse数据库的两种方式小结
作者:挽手等风起 时间:2024-01-24 19:25:02
python连接clickhouse数据库
在Python中获取系统信息的一个好办法是使用psutil这个第三方模块。
顾名思义,psutil = process and system utilities,它不仅可以通过一两行代码实现系统监控,还可以跨平台使用。
主要针对clickhouse_driver的使用进行简要介绍
第一步:
通过pip install clickhouse_driver 安装 clickhouse_driver
第二步:
方法一:使用clickhouse_driver 包中的Client类,通过实例化一个客户端进行对数据库的增删改查操作
from clickhouse_driver import Client
from datetime import datetime
import psutil
host_name = '192.168.50.94'
client = Client(host=host_name,database='default',user='default',password='自己设的密码',send_receive_timeout=20,port=55666)
now = datetime.now()
time_stamp = now.strftime('%a %b %d %H:%M:%S CST %Y')# Tue Apr 06 15:32:55 CST 2021 <class 'str'>
create_at = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
disk_io = psutil.disk_io_counters()
net_io = psutil.net_io_counters()
chart_name = ["磁盘IO","网络IO"]
metric_name1 = ["读(数量)","写(数量)", "读(字节)", "写(字节)", "读(时间)", "写(时间)"]
metric_name2 = ["发送字节数","接收字节数","发送包数","接收包"]
metric_value1 = [disk_io.read_count,disk_io.write_count,disk_io.read_bytes,disk_io.write_bytes,disk_io.read_time,disk_io.write_time]
metric_value2 = [net_io.bytes_sent,net_io.bytes_recv,net_io.packets_sent,net_io.packets_recv]
try:
for i in chart_name:
if i is "磁盘IO":
for j in metric_name1:
sql = "insert into clickhouse_host_metrics777(time_stamp,host_name, chart_name, metric_name,metric_value,create_at) " \
"values('%s','%s','%s','%s','%s','%s')" % \
(time_stamp, host_name, i, j, metric_value1[metric_name1.index(j)], create_at)
res = client.execute(sql)
elif i is "网络IO":
for j in metric_name2:
sql = "insert into clickhouse_host_metrics777(time_stamp,host_name, chart_name, metric_name,metric_value,create_at) " \
"values('%s','%s','%s','%s','%s','%s')" % \
(time_stamp, host_name, i, j, metric_value2[metric_name2.index(j)], create_at)
res = client.execute(sql)
print("成功写入数据")
except Exception as e:
print(str(e))
方法二:使用clickhouse_driver 包中的connect函数,通过实例化一个客户端进行对数据库的增删改查操作
from datetime import datetime
import psutil
from clickhouse_driver import connect
host_name = '192.168.50.94'
#账号:密码@主机名:端口号/数据库
conn = connect('clickhouse://default:自己设的密码@'+host_name+':55666/default')
cursor = conn.cursor()
now = datetime.now()
time_stamp = now.strftime('%a %b %d %H:%M:%S CST %Y')# Tue Apr 06 15:32:55 CST 2021 <class 'str'>
create_at = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
disk_io = psutil.disk_io_counters()
net_io = psutil.net_io_counters()
chart_name = ["磁盘IO","网络IO"]
metric_name1 = ["读(数量)","写(数量)", "读(字节)", "写(字节)", "读(时间)", "写(时间)"]
metric_name2 = ["发送字节数","接收字节数","发送包数","接收包"]
metric_value1 = [disk_io.read_count,disk_io.write_count,disk_io.read_bytes,disk_io.write_bytes,disk_io.read_time,disk_io.write_time]
metric_value2 = [net_io.bytes_sent,net_io.bytes_recv,net_io.packets_sent,net_io.packets_recv]
try:
for i in chart_name:
if i is "磁盘IO":
for j in metric_name1:
sql = "insert into clickhouse_host_metrics777(time_stamp,host_name, chart_name, metric_name,metric_value,create_at) values('%s','%s','%s','%s','%s','%s')" % \
(time_stamp, host_name, i, j, metric_value1[metric_name1.index(j)], create_at)
# res = client.execute(sql)
res = cursor.execute(sql)
elif i is "网络IO":
for j in metric_name2:
sql = "insert into clickhouse_host_metrics777(time_stamp,host_name, chart_name, metric_name,metric_value,create_at) values('%s','%s','%s','%s','%s','%s')" % \
(time_stamp, host_name, i, j, metric_value2[metric_name2.index(j)], create_at)
res = cursor.execute(sql)
cursor.close()
print("成功写入数据")
except Exception as e:
print(str(e))
python将数据写入clickhouse
from clickhouse_driver import Client
# connect ClickHouse
client = Client(host= ,port= ,user= ,database= , password=)
# 得到table1中查询的数据导入table2中(database2中应该事先建立对应的table2表)
query_ck_sql = """ SELECT *
FROM database1.table1
WHERE date = today() """
# 导入数据到临时表
try:
# 导入数据
client.execute("insert into {official_table_db}.{official_all_table_name} \
{query_ck_sql}".format(
official_table_db = database2,
official_table_name = table2,
query_ck_sql = query_ck_sql)
,types_check = True)
except Exception as e:
print str(e)
来源:https://blog.csdn.net/qq_36697196/article/details/115479096
标签:python,连接,clickhouse
0
投稿
猜你喜欢
Vue Ref全家桶具体用法详解
2024-04-27 15:49:06
python文本进度条实例
2023-05-15 17:18:16
如何利用PyQt5美化你的GUI界面
2023-06-18 19:58:55
PL/SQL 类型格式转换
2009-02-26 11:07:00
用C语言操作MySQL数据库的通用方法
2024-01-12 17:22:45
Git的基础文件操作初始化查看添加提交示例教程
2023-12-25 19:43:21
完美解决SQL server2005中插入汉字变成问号的问题
2024-01-24 20:59:59
Hive-SQL查询连续活跃登录用户思路详解
2024-01-22 08:29:47
小程序实现授权登陆的解决方案
2024-04-17 10:03:28
SQL语句单引号与双引号的使用方法
2024-01-22 11:35:09
pygame游戏之旅 按钮上添加文字的方法
2022-09-28 23:41:39
实现SQL Server 原生数据从XML生成JSON数据的实例代码
2024-01-16 01:46:03
mysql5.6.8源码安装过程
2024-01-22 02:53:41
利用python写api接口实战指南
2021-03-29 06:07:18
Python DataFrame.groupby()聚合函数,分组级运算
2022-02-08 07:23:09
Python可视化神器pyecharts绘制雷达图
2021-09-30 14:50:48
Python Web框架Flask中使用百度云存储BCS实例
2023-09-14 21:53:44
python 爬取影视网站下载链接
2022-07-30 00:59:42
CREATE FUNCTION sqlserver用户定义函数
2012-08-21 10:57:42
Golang中互斥锁和读写互斥锁原理及示例代码
2024-05-29 22:06:52