[TOC]

一、client方式

1.1 基础用法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import clickhouse_driver

# 使用 HTTP 连接方式连接 ClickHouse 数据库
client = clickhouse_driver.Client(
    host='your_host',
    port='your_port',
    user='your_user',
    password='your_password',
    database='your_database'
)

# 执行 SQL 查询语句
results = client.execute('SELECT * FROM your_table')

# 处理查询结果
print(results)

1.2 防止SQL注入

  • 查询操作，防止SQL注入，in查询语法
1
2
3
4
5
6
7
8
9
10
11
ck_client = get_slb_client()
query_sql = "select time, sum(in_bits), sum(out_bits) from slb_monitor_data_all where " \
            "listen_id = %(listen_id)s and vm_id in (%(vm_ids)s) and time >= %(start_time)s and " \
            "time < %(end_time)s group by time order by time;"
params = {
    'listen_id': listen_obj.id,
    'vm_ids': [v.id for v in vm_objs],
    'start_time': start_time,
    'end_time': end_time,
}
res = ck_client.execute(query_sql, params)

1.3 插入操作

1
2
3
4
5
6
7
8
9
insert_list = [
	{'vm_id': 'vm_id', 'listen_id': 'listen_id' ...},
	{'vm_id': 'vm_id', 'listen_id': 'listen_id' ...},
	...
]
insert_sql = "insert into slb_monitor_data_local (vm_id, listen_id, time, active_conn, in_active_conn, " \
             "new_conn, loss_conn, all_conn, in_pkts, out_pkts, in_bits, out_bits, all_in_pkts, all_out_pkts, " \
             "all_in_bytes, all_out_bytes) values"
ck_client.execute(insert_sql, insert_list)

二、connect

2.1 基础用法一

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import clickhouse_driver

# 使用 TCP 连接方式连接 ClickHouse 数据库
connection = clickhouse_driver.connect(
    host='your_host',
    port='your_port',
    user='your_user',
    password='your_password',
    database='your_database'
)

# 获取游标对象
cursor = connection.cursor()

# 执行 SQL 查询语句
cursor.execute('SELECT * FROM your_table')

# 处理查询结果
results = cursor.fetchall()

# 关闭游标和连接
cursor.close()
connection.close()

2.2 基础用法二

1
2
3
4
5
6
7
8
9
10
11
12
import clickhouse_driver

connection = clickhouse_driver.connect(host='your_host', port='your_port')

# 使用 with 语句块，确保程序结束时自动关闭游标和连接
with connection.cursor() as cursor:
    # 执行 SQL 查询语句
    cursor.execute('SELECT * FROM your_table')
    # 处理查询结果
    results = cursor.fetchall()

# 在 with 语句块结束后，游标和连接会自动关闭