async_sqlalchemy

async_sqlalchemy

import asyncio
from datetime import datetime

from sqlalchemy import Column, Integer, DateTime, Enum, String, select
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from sqlalchemy.orm import as_declarative, declared_attr

SQLALCHEMY_DATABASE_URL = f'mysql+aiomysql://root:root@127.0.0.1:3306/demo'

engine = create_async_engine(SQLALCHEMY_DATABASE_URL, pool_pre_ping=True, pool_size=0, max_overflow=-1)
SessionLocal = async_sessionmaker(autoflush=False, bind=engine)

@as_declarative()
class Base:
    metadata = None
    id = Column(Integer, primary_key=True, autoincrement=True, unique=True, index=True, doc="ID", comment="ID")
    create_time = Column(DateTime, default=datetime.now, doc="创建时间", comment="创建时间")
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now, doc="更新时间", comment="更新时间")
    is_delete = Column(Enum("0", "1"), default="0", doc="逻辑删除:0=存在,1=删除", comment="逻辑删除:0=存在,1=删除")
    __name__: str

    @declared_attr
    @classmethod
    def __tablename__(cls) -> str:
        return cls.__name__.lower()

    # 生成数据库表注释
    @declared_attr
    @classmethod
    def __table_args__(cls):
        return {"comment": cls.__doc__}

    def query(self, *args):
        return select(*args)


class Users(Base):
    """用户表"""
    username = Column(String(255), nullable=False, index=True, doc="用户名", comment="用户名")
    hashed_password = Column(String(255), nullable=False, doc="密码", comment="密码")
    name = Column(String(255), nullable=True, doc="姓名", comment="姓名")
    auth_token = Column(String(255), nullable=True, unique=True, doc="用户token", comment="用户token")
    flag = Column(Enum("组员", "组长", "审核员", "管理员"), default="组员", doc="用户身份", comment="用户身份")

async def main():
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)
    await engine.dispose()

    session = SessionLocal()
    try:
        async with session.begin():
            user = select(Users).filter("0" == Users.is_delete, "admin" == Users.username)
            user = await session.execute(user)
            user = user.first()
            if not user:
                user = Users(username='admin', hashed_password='dc8cQxUldD', name="超级管理员",
                             flag="管理员")
                session.add(user)
    except Exception as e:
        await session.rollback()
        raise e
    finally:
        await session.close()

asyncio.get_event_loop().run_until_complete(main())