记录sanic + gino简单使用

简单记录下和sanic配合的gino数据框架使用方式，gino底层使用SQLalchemy core去定义表结构，使用asyncpg去操作数据库。

Table

class CompanyAccountModel(db.Model):
    __tablename__ = 'company_account'
    id = db.Column(db.Integer, primary_key=True)
    account = db.Column(VARCHAR(20), unique=True, nullable=False)
    _password = db.Column(BYTEA, nullable=False)
    secret_key = db.Column(UUID(as_uuid=True), unique=True, nullable=False, default=uuid.uuid4().hex)
    state = db.Column(BOOLEAN, nullable=False, default=False)
    uid = db.Column(UUID(as_uuid=True), unique=True, nullable=False, default=uuid.uuid1())
    create_time = db.Column(TIMESTAMP, nullable=False, default=datetime.now)
    edited_time = db.Column(TIMESTAMP, nullable=False, default=datetime.now, onupdate=datetime.now)


class CompanyNoticeModel(db.Model):
    __tablename__ = 'company_notice'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(VARCHAR(30), nullable=False)
    company_account_id = db.Column(INTEGER, db.ForeignKey('company_account.id'))
    message = db.Column(TEXT, nullable=False, default='')
    create_time = db.Column(TIMESTAMP, nullable=False, default=datetime.now)
    edited_time = db.Column(TIMESTAMP, nullable=False, default=datetime.now, onupdate=datetime.now)

数据库使用上面两个作为例子

增
await CompanyNoticeModel.create(title='ss')

改
c = await CompanyNoticeModel.query.gino.first()
await c.update(title='11').apply()

await CompanyNoticeModel.update.values(title='sdfsd').where(CompanyNoticeModel.title=='11').gino.all()


查
await CompanyNoticeModel.query.gino.first()
await CompanyNoticeModel.query.gino.all()
await CompanyNoticeModel.get(1)
await CompanyNoticeModel.query.where(CompanyNoticeModel.title=='sdfsd').gino.all()

query = CompanyNoticeModel.query.where(CompanyNoticeModel.title='sdfsd')
await query.where(CompanyNoticeModel.id==1).gino.all()


选择字段
await CompanyNoticeModel.select('title').gino.first()
await CompanyNoticeModel.select('title').gino.all()

生成器
await CompanyNoticeModel.query.gino.iterate()

删
await CompanyNoticeModel.delete.where(CompanyNoticeModel.title=='ss').gino.all()
await CompanyNoticeModel.delete.gino.all() == await CompanyNoticeModel.delete.gino.first()

c = await CompanyNoticeModel.query.gino.first()
await c.delete()

事务
async with db.transaction():

排序
await CompanyNoticeModel.query.order_by(CompanyNoticeModel.id).gino.first()
await CompanyNoticeModel.query.order_by(CompanyNoticeModel.id.desc()).gino.first()

distinct
await CompanyNoticeModel.query.distinct(CompanyNoticeModel.title).order_by(CompanyNoticeModel.title,CompanyNoticeModel.id.desc()).gino.all()
await CompanyNoticeModel.query.distinct(CompanyNoticeModel.title).gino.all()


join
from sqlalchemy.sql import join, outerjoin
j = join(CompanyNoticeModel, CompanyAccountModel, CompanyNoticeModel.company_account_id==CompanyAccountModel.id)
n = await CompanyNoticeModel.query.select_from(j).gino.all()


count
setattr(CompanyNoticeModel, 'count', func.count(CompanyNoticeModel.id).label('count'))
await CompanyNoticeModel.select('count').gino.all()


alise
setattr(CompanyNoticeModel, 'label', CompanyNoticeModel.id.label('id_'))
await CompanyNoticeModel.select('label').gino.all()


subquery
from sqlalchemy.sql import select
nn = select([CompanyAccountModel.id])
await CompanyNoticeModel.query.where(CompanyNoticeModel.company_account_id==nn).gino.all()

group_by
await CompanyNoticeModel.select('title').group_by(CompanyNoticeModel.title).gino.all()


limit/offset
await CompanyNoticeModel.query.limit(10).offset(2).gino.all()