python FastAPI sqlalchemy 数据库模型基类通用模型

作用

用于所有表都需要使用的字段或者方法

实现代码

base.py

#!/usr/bin/python
# -*- coding: utf-8 -*-
# @time    : 2023/2/13 17:43 
# @author  : pugongying
# @description :
from sqlalchemy import Column, Integer, String, func, DateTime
from sqlalchemy.orm import Session

from app.dbs.database import Base


class BaseModel(Base):
    __abstract__ = True  #设置为可继承的基础模型，不创建表
    id = Column(Integer, primary_key=True, autoincrement=True, comment='id')
    date_created = Column(DateTime(timezone=True), default=func.now(), comment='创建时间')
    last_updated = Column(DateTime(timezone=True), default=func.now(), onupdate=func.now(), comment='修改时间')

    def to_dict(self):
        model_dict = dict(self.__dict__)
        del model_dict['_sa_instance_state']
        return model_dict

    Base.to_dict = to_dict  # 注意:这个跟使用flask_sqlalchemy的有区别

    # 单个对象方法2
    def single_to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}

    # 多个对象
    def dobule_to_dict(self):
        result = {}
        for key in self.__mapper__.c.keys():
            if getattr(self, key) is not None:
                result[key] = str(getattr(self, key))
            else:
                result[key] = getattr(self, key)
        return result

    # 配合多个对象使用的函数
    @staticmethod
    def to_json(all_vendors):
        v = [ven.dobule_to_dict() for ven in all_vendors]
        return v

    @staticmethod
    def bulks_update(db: Session, modelclass, listdict):
        """
        modelclass: 模型名称
        listdict: list[dict]
        """
        # print(db, modelclass, listdict)
        db.bulk_update_mappings(modelclass, listdict)
        db.commit()

    @staticmethod
    def bulks_insert(db: Session, modelclass, listdict):
        """
        modelclass: 模型名称
        listdict: list[dict]
        """
        db.bulk_insert_mappings(modelclass, listdict)
        db.commit()

user_schemas.py

#!/usr/bin/python
# -*- coding: utf-8 -*-
# @time    : 2023/2/3 17:46 
# @author  : pugongying
# @description : 数据验证模型
from typing import List

from pydantic import BaseModel



class UserTaskAccount(BaseModel):
    username: str
    password: str
    account_ids: List

user.py

#!/usr/bin/python
# -*- coding: utf-8 -*-
# @time    : 2023/3/6 15:49 
# @author  : pugongying
# @description :
from typing import List

from sqlalchemy import Boolean, Column, Integer, String, DateTime, func
from sqlalchemy.orm import Session

from app.models.base import BaseModel
from app.schemas.user_schemas import UserTaskAccount


class UserAccount(BaseModel):
    __tablename__ = "table_ssssss"  # 表名

    account_id = Column(String(100), nullable=False, comment='xxxx')
    username = Column(String(100), nullable=False, comment='xxxx')
    password = Column(String(100), nullable=False, comment='xxxx')
    account_type = Column(Integer, default=1, nullable=False, comment='xxxx')
    deleted = Column(Boolean, default=True, nullable=False, comment='xxxx')
     
    # 定义模型方法，直接调用返回需要的数据
    @staticmethod
    def query_task_account(db: Session, username: int) -> List[UserTaskAccount]:
        result_list = db \
            .query(UserAccount.username,
                   func.min(UserAccount.password),
                   func.array_agg(UserAccount.account_id)) \
            .filter(UserAccount.deleted == False,
                    UserAccount.account_type == account_type) \
            .group_by(UserAccount.username) \
            .all()
        return [UserTaskAccount(username=item[0], password=item[1], account_ids=item[2]) for item in result_list]