具有SQLAlchemy和Python的通用存储库

最新推荐文章于 2023-12-18 10:38:25 发布

寒冰屋

最新推荐文章于 2023-12-18 10:38:25 发布

阅读量247

点赞数

分类专栏：数据库 python 文章标签： postgresql Python SQLAlchemy

原文链接：https://www.codeproject.com/Articles/5307399/Generic-Repository-with-SQLAlchemy-and-Python

版权

python 同时被 2 个专栏收录

258 篇文章 9 订阅

订阅专栏

数据库

220 篇文章 13 订阅

订阅专栏

下载源代码 - 34.9 KB

介绍

存储库模式以及工作单元模式允许在应用程序的数据访问层和业务逻辑层之间创建抽象层。创建此层的目的是隔离数据访问层，以便我们可能操作的更改不会直接影响业务逻辑层。大多数时候，使用通用存储库类，避免冗余代码。

在这里，我们将更多地关注使用Python为SQLAlchemy创建通用存储库类，并将在FastAPI项目中使用它。

通用存储库

表

公共属性或列的AppBaseModelOrm基类
TaskQueue数据库表模型
GroupQueue数据库表模型

models.py：

import datetime
from sqlalchemy import Boolean, Column, Integer, String,  \
     DateTime, PickleType, Enum as EnumType, JSON
from sqlalchemy.dialects.postgresql import UUID
from db.database import Base

# common fields for all entities
class AppBaseModelOrm:
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    is_active = Column(Boolean, default=True)  # soft delete
    created_by = Column(Integer)
    updated_by = Column(Integer, default=None)
    created_datetime = Column(DateTime(timezone=True), default=datetime.datetime.utcnow)
    updated_datetime = Column(DateTime(timezone=True), \
                       default=None, onupdate=datetime.datetime.utcnow)

    account_id = Column(Integer)

# tables
class TaskQueue(AppBaseModelOrm, Base):
    __tablename__ = "task_queues"
    name = Column(String, index=True)

class GroupQueue(AppBaseModelOrm, Base):
    __tablename__ = "group_queues"
    name = Column(String, index=True)

存储库类

TableRepository是包含一些基本操作/方法的基存储库类，例如：

从表中读取数据
在表中添加/更新/删除行

在构造函数级别，存储库类期望：

db:Session数据库会话对象
entity:object表实体

table_repo.py：

from sqlalchemy import and_
from sqlalchemy.orm import Session
from sqlalchemy.sql.expression import false
from datetime import datetime

class TableRepository:

    entity:object = NotImplementedError
    db:Session = NotImplementedError

    def __init__(self, db:Session, entity:object):
        self.db = db
        self.entity = entity

    def get_all(self):
        return self.db.query(self.entity)
           
    def get_by_id(self, id:int):
        return self.db.query(self.entity).filter(self.entity.id==id).one()

    def find_by_id(self, id:int):
        return self.db.query(self.entity).filter(self.entity.id==id).first()

    def get_actives(self):
        return self.db.query(self.entity).filter(self.entity.is_active==True)

    def get_by_account_id(self, account_id:int):
        return self.db.query(self.entity).filter(self.entity.account_id==account_id)

    def get_actives_by_account_id(self, account_id:int):
        return self.db.query(self.entity).filter\
        (self.entity.is_active==True, self.entity.account_id==account_id)

    def get_by_create_datetime_range(self, from_datetime:datetime, to_datetime:datetime):
        data = self.db.query(self.entity).filter\
        (self.entity.created_datetime >= from_datetime, \
        self.entity.created_datetime <= to_datetime)
        return data

    def add(self, entity, created_by_user_id:int = None):
        entity.created_by = created_by_user_id
        self.db.add(entity)      

    def update(self, entity, updated_by_user_id:int = None):
        entity.updated_by = updated_by_user_id

    def delete(self, entity, deleted_by_user_id:int = None):
        entity.is_active = False
        self.update(entity, updated_by_user_id=deleted_by_user_id)

    def permanent_delete(self, entity):
        self.db.delete(entity)

使用存储库类

在这里，对于TaskQueue实体和读写表task_queues，我们使用了不带任何继承的TableRepository repo类。

task_queue.py：

from typing import Optional, List
from fastapi import FastAPI, Request, Depends
from fastapi_utils.cbv import cbv
from fastapi_utils.inferring_router import InferringRouter
from sqlalchemy.orm import Session

from app import schemas
from app.depends.db_depend import get_db
from app.depends.auth_depend import get_current_user, CurrentUser
from db import models
from db.table_repo import TableRepository

router = InferringRouter()

@cbv(router)
class TaskQueue:
    db: Session = Depends(get_db)
    current_user:CurrentUser = Depends(get_current_user)

    @router.get("/", response_model=List[schemas.TaskQueueSchema])
    def get_all(self):
        repo = TableRepository(self.db, models.TaskQueue)
        items = repo.get_all().all()
        return items

    @router.get("/actives", response_model=List[schemas.TaskQueueSchema])
    def get_actives(self):
        repo = TableRepository(self.db, models.TaskQueue)
        items = repo.get_actives().all()
        return items

    @router.get("/account/{account_id}", response_model=List[schemas.TaskQueueSchema])
    def get_by_account(self, account_id: int):
        repo = TableRepository(self.db, models.TaskQueue)
        items = repo.get_by_account_id(account_id).all()
        return items

    @router.get("/account/{account_id}/actives", \
                 response_model=List[schemas.TaskQueueSchema])
    def get_actives_by_account(self, account_id: int):
        repo = TableRepository(self.db, models.TaskQueue)
        items = repo.get_actives_by_account_id(account_id).all()
        return items

    @router.get("/{id}", response_model=schemas.TaskQueueSchema)
    def get_by_id(self, id: int):
        repo = TableRepository(self.db, models.TaskQueue)
        item = repo.get_by_id(id)
        return item

    @router.get("/find/{id}", response_model=schemas.TaskQueueSchema)
    def find_by_id(self, id: int):
        '''can be null'''
        repo = TableRepository(self.db, models.TaskQueue)
        item = repo.find_by_id(id)
        return item

    @router.post("/", response_model=schemas.TaskQueueSchema)
    def post_item(self, model: schemas.TaskQueueCreate):
        item = models.TaskQueue(name=model.name, account_id=model.account_id)
        repo = TableRepository(self.db, models.TaskQueue)
        repo.add(item, self.current_user.id)
        self.db.commit()
        self.db.refresh(item)
        return item

    @router.put("/{id}", response_model=schemas.TaskQueueSchema)
    def put_item(self, id:int, model: schemas.TaskQueueUpdate):
        '''can be null'''
        repo = TableRepository(self.db, models.TaskQueue)
        item = repo.find_by_id(id)
        if item:
            item.name = model.name
            repo.update(item, self.current_user.id)
            self.db.commit()
            self.db.refresh(item)
        return item

    @router.delete("/{id}", response_model=schemas.TaskQueueSchema)
    def delete_item(self, id: int):
        '''can be null'''       
        repo = TableRepository(self.db, models.TaskQueue)
        item = repo.find_by_id(id)
        if item:
            repo.delete(item, self.current_user.id)
            self.db.commit()
            self.db.refresh(item)
        return item

    @router.delete("/permanent/{id}", response_model=schemas.TaskQueueSchema)
    def permanent_delete_item(self, id: int):
        '''can be null'''       
        repo = TableRepository(self.db, models.TaskQueue)
        item = repo.find_by_id(id)
        if item:
            repo.permanent_delete(item)
            self.db.commit()
        return item

使用存储库类作为基类

GroupQueueCrud继承了GroupQueue实体和读写表group_queues的repo类TableRepository：

继承存储库类

group_queue_crud.py：

from sqlalchemy.orm import Session
from app import schemas
from db import models
from db.table_repo import TableRepository

class GroupQueueCrud(TableRepository):
    
    def __init__(self, db:Session): 
        super().__init__(db=db, entity=models.GroupQueue)

使用CRUD类

group_queue.py：

from datetime import datetime
from typing import Optional, List
from fastapi import FastAPI, Request, Depends, Query
from fastapi_utils.cbv import cbv
from fastapi_utils.inferring_router import InferringRouter
from sqlalchemy.orm import Session

from app import schemas
from app.depends.db_depend import get_db
from app.depends.auth_depend import get_current_user, CurrentUser
from app.cruds.group_queue_crud import GroupQueueCrud
from db import models

router = InferringRouter()


@cbv(router)
class GroupQueue:
    db: Session = Depends(get_db)
    current_user:CurrentUser = Depends(get_current_user)

    @router.get("/{id}", response_model=schemas.GroupQueueSchema)
    def get_by_id(self, id: int):
        repo = GroupQueueCrud(self.db)
        item = repo.find_by_id(id)
        return item    

    @router.post("/", response_model=schemas.GroupQueueSchema)
    def post_item(self, model: schemas.GroupQueueCreate):
        item = models.GroupQueue(name=model.name, account_id=model.account_id)
        repo = GroupQueueCrud(self.db)
        repo.add(item, self.current_user.id)
        self.db.commit()
        self.db.refresh(item)
        return item

    @router.put("/{id}", response_model=schemas.GroupQueueSchema)
    def put_item(self, id:int, model: schemas.GroupQueueUpdate):
        '''can be null'''
        repo = GroupQueueCrud(self.db)
        item = repo.find_by_id(id)
        if item:
            item.name = model.name
            repo.update(item, self.current_user.id)
            self.db.commit()
            self.db.refresh(item)
        return item

    @router.delete("/{id}", response_model=schemas.GroupQueueSchema)
    def delete_item(self, id: int):
        '''can be null'''       
        repo = GroupQueueCrud(self.db)
        item = repo.find_by_id(id)
        if item:
            repo.delete(item, self.current_user.id)
            self.db.commit()
            self.db.refresh(item)
        return item

使用代码

Go to backend folder
Open cmd 
Type docker-compose up -d

\backend> docker-compose up -d

project will run http://localhost:4003

Go to Api Doc
http://localhost:4003/docs#/

检查任务队列和组队列部分：