search

LuisLuii / FastAPIQuickCRUD

Generate CRUD methods in FastApi from an SQLAlchemy schema
https://pypi.org/project/fastapi-quickcrud/
MIT License
252 stars 32 forks source link
api-generator fastapi fastapi-crud fastapi-framework fastapi-sqlalchemy orm postgres postgresql postgresql-database python python-library python37 restful restful-api restful-routes sqlalchemy sqlalchemy-models sqlalchemy-orm sqlalchemy-orm-queries sqlalchemy-python

readme

FastAPI Quick CRUD

Imgur

Codacy Badge Coverage Status unit test SupportedVersion develop dtatus PyPI version

If you need apply business logic or add on some code, you can use my another open source project which supports CRUD router code generator

Introduction

I believe that many people who work with FastApi to build RESTful CRUD services end up wasting time writing repitive boilerplate code.

FastAPI Quick CRUD can generate CRUD methods in FastApi from an SQLAlchemy schema:

FastAPI Quick CRUDis developed based on SQLAlchemy 1.4.23 version and supports sync and async.

docs page docs_page_2

Advantages

Limitations

Getting started

I try to update the version dependencies as soon as possible to ensure that the core dependencies of this project have the highest version possible.

fastapi<=0.68.2
pydantic<=1.8.2
SQLAlchemy<=1.4.30
starlette==0.14.2

Installation

pip install fastapi-quickcrud

I suggest the following library if you try to connect to PostgreSQL 

pip install psycopg2
pip install asyncpg

Usage

run and go to http://127.0.0.1:port/docs and see the auto-generated API

Simple Code (or see the longer (example)

from fastapi import FastAPI
from sqlalchemy import Column, Integer, \
    String, Table, ForeignKey, orm
from fastapi_quickcrud import crud_router_builder

Base = orm.declarative_base()

class User(Base):
    __tablename__ = 'test_users'
    id = Column(Integer, primary_key=True, autoincrement=True, unique=True)
    name = Column(String, nullable=False)
    email = Column(String, nullable=False)

friend = Table(
    'test_friend', Base.metadata,
    Column('id', ForeignKey('test_users.id', ondelete='CASCADE', onupdate='CASCADE'), nullable=False),
    Column('friend_name', String, nullable=False)
)

crud_route_1 = crud_router_builder(db_model=User,
                                   prefix="/user",
                                   tags=["User"],
                                   async_mode=True
                                   )
crud_route_2 = crud_router_builder(db_model=friend,
                                   prefix="/friend",
                                   tags=["friend"],
                                   async_mode=True
                                   )

app = FastAPI()
app.include_router(crud_route_1)
app.include_router(crud_route_2)

Foreign Tree With Relationship

from fastapi import FastAPI
from fastapi_quickcrud import crud_router_builder
from sqlalchemy import *
from sqlalchemy.orm import *
from fastapi_quickcrud.crud_router import generic_sql_crud_router_builder

Base = declarative_base()

class Account(Base):
    __tablename__ = "account"
    id = Column(Integer, primary_key=True, autoincrement=True)
    blog_post = relationship("BlogPost", back_populates="account")

class BlogPost(Base):
    __tablename__ = "blog_post"
    id = Column(Integer, primary_key=True, autoincrement=True)
    account_id = Column(Integer, ForeignKey("account.id"), nullable=False)
    account = relationship("Account", back_populates="blog_post")
    blog_comment = relationship("BlogComment", back_populates="blog_post")

class BlogComment(Base):
    __tablename__ = "blog_comment"
    id = Column(Integer, primary_key=True, autoincrement=True)
    blog_id = Column(Integer, ForeignKey("blog_post.id"), nullable=False)
    blog_post = relationship("BlogPost", back_populates="blog_comment")

crud_route_parent = crud_router_builder(
    db_model=Account,
    prefix="/account",
    tags=["account"],
    foreign_include=[BlogComment, BlogPost]

)
crud_route_child1 = generic_sql_crud_router_builder(
    db_model=BlogPost,
    prefix="/blog_post",
    tags=["blog_post"],
    foreign_include=[BlogComment]

)
crud_route_child2 = generic_sql_crud_router_builder(
    db_model=BlogComment,
    prefix="/blog_comment",
    tags=["blog_comment"]

)

app = FastAPI()
[app.include_router(i) for i in [crud_route_parent, crud_route_child1, crud_route_child2]]

SQLAlchemy to Pydantic Model Converter And Build your own API(example)

import uvicorn
from fastapi import FastAPI, Depends
from fastapi_quickcrud import CrudMethods
from fastapi_quickcrud import sqlalchemy_to_pydantic
from fastapi_quickcrud.misc.memory_sql import sync_memory_db

from sqlalchemy import CHAR, Column, Integer
from sqlalchemy.ext.declarative import declarative_base

app = FastAPI()

Base = declarative_base()
metadata = Base.metadata

class Child(Base):
    __tablename__ = 'right'
    id = Column(Integer, primary_key=True)
    name = Column(CHAR, nullable=True)

friend_model_set = sqlalchemy_to_pydantic(db_model=Child,
                                          crud_methods=[
                                              CrudMethods.FIND_MANY,
                                              CrudMethods.UPSERT_MANY,
                                              CrudMethods.UPDATE_MANY,
                                              CrudMethods.DELETE_MANY,
                                              CrudMethods.CREATE_ONE,
                                              CrudMethods.PATCH_MANY,

                                          ],
                                          exclude_columns=[])

post_model = friend_model_set.POST[CrudMethods.CREATE_ONE]
sync_memory_db.create_memory_table(Child)

@app.post("/hello",
          status_code=201,
          tags=["Child"],
          response_model=post_model.responseModel,
          dependencies=[])
async def my_api(
        query: post_model.requestBodyModel = Depends(post_model.requestBodyModel),
        session=Depends(sync_memory_db.get_memory_db_session)
):
    db_item = Child(**query.__dict__)
    session.add(db_item)
    session.commit()
    session.refresh(db_item)
    return db_item.__dict__

uvicorn.run(app, host="0.0.0.0", port=8000, debug=False)

Main module

Generate CRUD router

crud_router_builder args

Design

In PUT DELETE PATCH, user can use Path Parameters and Query Parameters to limit the scope of the data affected by the operation, and the Query Parameters is same with FIND API

Path Parameter

In the design of this tool, Path Parameters should be a primary key of table, that why limited primary key can only be one.

Query Parameter

Query to SQL statement example

Request Body

In the design of this tool, the columns of the table will be used as the fields of request body.

In the basic request body in the api generated by this tool, some fields are optional if :

Foreign Tree

TBC

Upsert

** Upsert supports PosgreSQL only yet

POST API will perform the data insertion action with using the basic Request Body, In addition, it also supports upsert(insert on conflict do)

The operation will use upsert instead if the unique column in the inserted row that is being inserted already exists in the table

The tool uses unique columns in the table as a parameter of on conflict , and you can define which column will be updated

upsert

Add description into docs

You can declare comment argument for sqlalchemy.Column to configure the description of column

example:


class Parent(Base):
    __tablename__ = 'parent_o2o'
    id = Column(Integer, primary_key=True,comment='parent_test')

    # one-to-many collection
    children = relationship("Child", back_populates="parent")

class Child(Base):
    __tablename__ = 'child_o2o'
    id = Column(Integer, primary_key=True,comment='child_pk_test')
    parent_id = Column(Integer, ForeignKey('parent_o2o.id'),info=({'description':'child_parent_id_test'}))

    # many-to-one scalar
    parent = relationship("Parent", back_populates="children")

Relationship

Now, FIND_ONE and FIND_MANY are supporting select data with join operation


class Parent(Base):
    __tablename__ = 'parent_o2o'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", back_populates="parent")

class Child(Base):
    __tablename__ = 'child_o2o'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent_o2o.id'))
    parent = relationship("Parent", back_populates="children")

there is a relationship with using back_populates between Parent table and Child table, the parent_id in Child will refer to id column in Parent.

FastApi Quick CRUD will generate an api with a join_foreign_table field, and get api will respond to your selection of the reference data row of the corresponding table in join_foreign_table field,

join_1

join_2

parent

child

when i request

FastAPI_quickcrud Response Status Code standard

When you ask for a specific resource, say a user or with query param, and the user doesn't exist

GET: get one : https://0.0.0.0:8080/api/:userid?xx=xx

UPDATE: update one : https://0.0.0.0:8080/api/:userid?xx=xx

PATCH: patch one : https://0.0.0.0:8080/api/:userid?xx=xx

DELETE: delete one : https://0.0.0.0:8080/api/:userid?xx=xx

then fastapi-qucikcrud should return 404. In this case, the client requested a resource that doesn't exist.

In the other case, you have an api that operate data on batch in the system using the following url:

GET: get many : https://0.0.0.0:8080/api/user?xx=xx

UPDATE: update many : https://0.0.0.0:8080/api/user?xx=xx

DELETE: delete many : https://0.0.0.0:8080/api/user?xx=xx

PATCH: patch many : https://0.0.0.0:8080/api/user?xx=xx

If there are no users in the system, then, in this case, you should return 204.

TODO

milestones