clickhouse_orm使用之操作数据库

本文用代码示范了用clickhouse_orm对clickhouse数据库的几种操作，包括连接clickhouse数据库，建表，删表，查询，更新，插入，删除。

from infi.clickhouse_orm.database import Database
from infi.clickhouse_orm.engines import MergeTree
from infi.clickhouse_orm.fields import UInt64Field, StringField
from infi.clickhouse_orm.models import Model
from config import CLICKHOUSE_INFO

CLICKHOUSE_URL = 'http://localhost:6666/?mutations_sync=1'
CLICKHOUSE_HOST = '127.0.0.1'
CLICKHOUSE_DB = 'test'
db = Database(**CLICKHOUSE_INFO)

class Test(Model):
    @classmethod
    def table_name(cls):
        return 'test6'
    id = UInt64Field()
    name=StringField()
    def __init__(self, id, name):
        self.id = id
        self.name=name
    engine = MergeTree(partition_key=('id',), order_by=('id',))

    @classmethod
    def add_record(cls,id,name):
        record = cls(id,name)
        db.insert([record,])
        print('插入成功')

CLICKHOUSE_INFO = {"db_name": CLICKHOUSE_DB, "db_url": CLICKHOUSE_URL}
db = Database(**CLICKHOUSE_INFO)

#db.drop_table(Test) #删除表
db.create_table(Test) #建表

a={"id":'1', "name":'zhang'}
t=Test(**a)  #实例化表数据

# obj = Test.objects_in(db).aggregate(max_id='max(id)')  # 找出表中最大id实现自增id
# t=Test(id=obj[0].max_id+1,name='huang')  #实例化表数据

db.insert([t,])  #插入
sel=Test.objects_in(db).filter(id=1)#查询
for p in sel:
    print(p.name)  #访问查询出来的数据
#sel.update(name='zhang')#更新
#sel.delete()#删除

下面介绍直接在python后台代码执行SQL语句，对数据库clickhouse数据库进行操作。

from clickhouse_driver import Client
from infi.clickhouse_orm.database import Database
from config import CLICKHOUSE_INFO

CLICKHOUSE_URL = 'http://localhost:8123/?mutations_sync=1'
CLICKHOUSE_HOST = '127.0.0.1'
CLICKHOUSE_DB = 'test'
db = Database(**CLICKHOUSE_INFO)

client = Client(CLICKHOUSE_HOST)
client.execute('use ' + CLICKHOUSE_DB)
#client.execute('CREATE TABLE test5( id UInt64,name String) ENGINE = MergeTree() order by id partition by id;')

#a=client.execute("insert into test5 values (3,'liu')")

#a=client.execute("select * from test5") #返回的是一个查询结果列表：[(2, 'liu'), (3, 'liu')]
#print(a[0][0])#输出查询到的第一个记录的第一个字段

#a=client.execute("insert into test5 values (1,'wang');") #插入数据成功，返回值是一个空列表

#a=client.execute("alter table test5 update name='huang' where id=1;")#更新数据成功，返回值是一个空列表

a=client.execute("alter table test5 delete where id=1")#删除数据成功，返回值是一个空列表
print(a)
client.disconnect()