使用 python连接操作clickhouse数据库

1. 安装包

pip install clickhouse-driver[lz4]

2. 封装

from clickhouse_driver.dbapi import connect

class DBServer:
    def __init__(self,host,port,user,password,database):
        self.conn = connect(host=host,
                port=port,
                user=user,
                password=password,
                database=database,
                compression=True)
        self.cursor = self.conn.cursor()
        
    def fetchall(self,sql):
        self.cursor.execute(sql)
        return self.cursor.fetchall()
        
        
    
    def fetchmany(self,sql,size):
        self.cursor.execute(sql)
        return self.cursor.fetchmany(size)    
        
    
    def fetchone(self,sql):
        self.cursor.execute(sql)
        return self.cursor.fetchone()
        
    def execute(self,sql):
        try:
            self.cursor.execute(sql)
            self.conn.commit()
        except:
            self.conn.rollback()

    
    def update(self,sql):
        try:
            self.cursor.execute(sql)
            self.conn.commit()
        except:
            self.conn.rollback()
            
            
    def delete(self,sql):
        try:
            self.cursor.execute(sql)
            self.conn.commit()
        except:
            self.conn.rollback()
    
    def close(self):
        self.cursor.close()
        self.conn.close()

3. 测试

# from clickhouse_driver import Client

# client = Client(host="192.168.247.128",
#                 port='9000',
#                 user='ellis',
#                 password='ellis',
#                 database='test',
#                 compression=True)
# result = client.execute('SHOW DATABASES')

# print(result)

'''
注意 这里的端口号对应的是tcp_port
'''


from DBServer.dbServer import DBServer

server = DBServer("192.168.247.128",'9000','ellis','ellis','test')

server.delete("alter table test.flink_user_2 delete where id =-1")

print(server.fetchall("select * from flink_user_2 where id =-1"))


server.execute("insert into test.flink_user_2(id,age,name)  values(-1,-1,'nage')")
print(server.fetchall("select * from flink_user_2 where id =-1"))


server.update("alter table test.flink_user_2 UPDATE name='sa' WHERE name='nage'")
print(server.fetchall("select * from flink_user_2 where id =-1"))

注意
端口号是配置文件中的tcp_port
删除更新数据也是异步的
官网