python連接clickhouse數(shù)據(jù)庫(kù)的兩種方式小結(jié)

第python連接clickhouse數(shù)據(jù)庫(kù)的兩種方式小結(jié)目錄python連接clickhouse數(shù)據(jù)庫(kù)主要針對(duì)clickhouse_driver的使用進(jìn)行簡(jiǎn)要介紹python將數(shù)據(jù)寫(xiě)入clickhouse

python連接clickhouse數(shù)據(jù)庫(kù)

在Python中獲取系統(tǒng)信息的一個(gè)好辦法是使用psutil這個(gè)第三方模塊。

顧名思義，psutil=processandsystemutilities，它不僅可以通過(guò)一兩行代碼實(shí)現(xiàn)系統(tǒng)監(jiān)控，還可以跨平臺(tái)使用。

主要針對(duì)clickhouse_driver的使用進(jìn)行簡(jiǎn)要介紹

第一步：

通過(guò)pipinstallclickhouse_driver安裝clickhouse_driver

第二步：

方法一：使用clickhouse_driver包中的Client類(lèi),通過(guò)實(shí)例化一個(gè)客戶端進(jìn)行對(duì)數(shù)據(jù)庫(kù)的增刪改查操作

fromclickhouse_driverimportClient

fromdatetimeimportdatetime

importpsutil

host_name='4'

client=Client(host=host_name,database='default',user='default',password='自己設(shè)的密碼',send_receive_timeout=20,port=55666)

now=datetime.now()

time_stamp=now.strftime('%a%b%d%H:%M:%SCST%Y')#TueApr0615:32:55CST2025class'str'

create_at=datetime.now().strftime('%Y-%m-%d%H:%M:%S')

disk_io=psutil.disk_io_counters()

net_io=_io_counters()

chart_name=["磁盤(pán)IO","網(wǎng)絡(luò)IO"]

metric_name1=["讀(數(shù)量)","寫(xiě)(數(shù)量)","讀(字節(jié))","寫(xiě)(字節(jié))","讀(時(shí)間)","寫(xiě)(時(shí)間)"]

metric_name2=["發(fā)送字節(jié)數(shù)","接收字節(jié)數(shù)","發(fā)送包數(shù)","接收包"]

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]

foriinchart_name:

ifiis"磁盤(pán)IO":

forjinmetric_name1:

sql="insertintoclickhouse_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)

elifiis"網(wǎng)絡(luò)IO":

forjinmetric_name2:

sql="insertintoclickhouse_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("成功寫(xiě)入數(shù)據(jù)")

exceptExceptionase:

print(str(e))

方法二：使用clickhouse_driver包中的connect函數(shù),通過(guò)實(shí)例化一個(gè)客戶端進(jìn)行對(duì)數(shù)據(jù)庫(kù)的增刪改查操作

fromdatetimeimportdatetime

importpsutil

fromclickhouse_driverimportconnect

host_name='4'

#賬號(hào):密碼@主機(jī)名:端口號(hào)/數(shù)據(jù)庫(kù)

conn=connect('clickhouse://default:自己設(shè)的密碼@'+host_name+':55666/default')

cursor=conn.cursor()

now=datetime.now()

time_stamp=now.strftime('%a%b%d%H:%M:%SCST%Y')#TueApr0615:32:55CST2025class'str'

create_at=datetime.now().strftime('%Y-%m-%d%H:%M:%S')

disk_io=psutil.disk_io_counters()

net_io=_io_counters()

chart_name=["磁盤(pán)IO","網(wǎng)絡(luò)IO"]

metric_name1=["讀(數(shù)量)","寫(xiě)(數(shù)量)","讀(字節(jié))","寫(xiě)(字節(jié))","讀(時(shí)間)","寫(xiě)(時(shí)間)"]

metric_name2=["發(fā)送字節(jié)數(shù)","接收字節(jié)數(shù)","發(fā)送包數(shù)","接收包"]

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]

foriinchart_name:

ifiis"磁盤(pán)IO":

forjinmetric_name1:

sql="insertintoclickhouse_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)

elifiis"網(wǎng)絡(luò)IO":

forjinmetric_name2:

sql="insertintoclickhouse_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("成功寫(xiě)入數(shù)據(jù)"