使用python连接clickhouse数据库的两种方式 |
您所在的位置：网站首页 › Python如何连接CRT › 使用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 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 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)) |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |