awtkns / fastapi-crudrouter Goto Github PK

Hi there,

I tried to create a sample code from the documentation. However it throws a type error.

Please find my code and other details below.

Code:

from pydantic import BaseModel
from fastapi import FastAPI
from fastapi_crudrouter import MemoryCRUDRouter as CRUDRouter

class Car(BaseModel):

    name: str
    year: int
    make: str


app = FastAPI()
app.include_router(CRUDRouter(schema=Car))

When I try to execute this, I get the following error.

Traceback (most recent call last):
  File "/home/dineshkumarkb/MyGitHub/MyPractice/Python/fastapi/autogenerate.py", line 13, in <module>
    app.include_router(CRUDRouter(Car))
  File "/home/dineshkumarkb/.local/lib/python3.8/site-packages/fastapi_crudrouter/core/mem.py", line 11, in __init__
    super(MemoryCRUDRouter, self).__init__(schema, *args, **kwargs)
  File "/home/dineshkumarkb/.local/lib/python3.8/site-packages/fastapi_crudrouter/core/_base.py", line 35, in __init__
    super().__init__(prefix=prefix, tags=[prefix.strip('/').capitalize()], *args, **kwargs)
TypeError: __init__() got an unexpected keyword argument 'prefix'

Am I missing something?

Python version : 3.8.5
OS : Ubuntu 20.04

Discussed in #102

DELETE /api/v1/authors/{item_id} [P]
1. Received a response with a status code, which is not defined in the schema: 404

Declared status codes: 200, 422

The aim of this feature is to document 404 status codes in the openapi spec.

Hello,

I am looking at modifying your crudrouter to work with Marmelab's react-admin.

To work with their Listview, I added pagination, sort column and order, column filter and result count to the get_all function.

To work with their Edit and Create views, I have permissions rules on the front end but I would like to have some on the backend too.

I added a dependency to the router and check there the authenticated status.
I suppose I could extend it to check the method and path and allow / restrict access based on this and permissions.
However I thought it would also be convenient to have different dependencies per route/function as these rules might be different from collection to collection.

Something like this:

users_router = SQLAlchemyCRUDRouter(
    schema=User,
    create_schema=UserCreate,
    db_model=UserModel,
    db=get_db,
    prefix='users',
    dependencies=[Depends(token_auth)],
    delete_one_route_dependencies=[Depends(must_be_admin)],
    delete_all_route_dependencies=[Depends(must_be_admin)],
)

I am not sure this is a good idea but if it seems interesting, where would you suggest I look at implementing this?
I looked in your code but was not sure if this should be in CRUDGenerator or in add_api_route.

In any case, great project, thank you !

I'm mixing this project and FastAPI-Users together so I have a very basic DatabasesCRUDRouter that has delete_all_route disabled and the delete_one_route has FastAPI-Users dependency for superuser on it. Otherwise, nothing else interacts with the delete endpoints yet when I delete an item I'm returned with 404 error and the detail says the item is not found.

The item is deleted though.

from fastapi import Depends
from fastapi_crudrouter import DatabasesCRUDRouter

from app.core.users import fastapi_users
from app.db.session import database
from app.models.thing import Thing as ThingTable 
from app.schemas.thing import ThingCreate, Thing

current_active_verified_user = fastapi_users.current_user(active=True, verified=True)
current_superuser = fastapi_users.current_user(active=True, superuser=True)

thing_db = ThingTable.__table__

router = DatabasesCRUDRouter(
    prefix="things",
    tags=["things"],
    schema=Thing,
    create_schema=ThingCreate,
    table=thing_db,
    database=database,
    # Disable delete_all endpoint
    delete_all_route=False,
    # Dependencies for endpoints
    create_route=[Depends(current_active_verified_user)],
    update_route=[Depends(current_active_verified_user)],
    delete_one_route=[Depends(current_superuser)],
)

Hello,

Would this package work with the (somewhat) new version of the SQLAlchemy ORM which is async?

They use different engine classes (AsyncEngine + AsyncSession VS Engine + Session)

Thanks for proj.

• L

https://fastapi-crudrouter.awtkns.com/routing

"Working"

from pydantic import BaseModel
from fastapi import FastAPI
from fastapi_crudrouter import MemoryCRUDRouter as CRUDRouter

class Potato(BaseModel):
    id: int
    color: str
    mass: float

app = FastAPI()
router = CRUDRouter(schema=Potato)

@router.get('')
def overloaded_get_all():
    return 'My overloaded route that returns all the items'

@router.get('/{item_id}')
def overloaded_get_one():
    return 'My overloaded route that returns one item'

app.include_router(router)

Is it possible to provide an exemple to show what to do with MemoryCRUDRouter instead of a string ?
Regards

After #104, using the schemathesis tests are still failing when validating the generated openapi spec. The goal of this issue would be to resolve these failing tests (as detailed in #102).

@awtkns I looked at this a little more, and I think my implementation here might be a little bit off. If you're planning to implement schemathesis I'm sure you'll spot it, but leaving this here in case it's helpful. Looking through these docs, it looks like this is the way to specify responses:

class NotFoundModel(BaseModel):
    detail: str


@router.get('/{item_id}/', status_code=200, response_model=Model, responses={'404': {'model': NotFoundModel}})
async def retrieve(item_id: int) -> Model:
    try:
        return await Model.objects.get(id=item_id)
    except NoMatch:
        raise HTTPException(status_code=404, detail="Item not found")

In other words, I think this PR fixed one schemathesis issue: the missing response code, but didn't fully resolve it, since the model isn't correctly specified.

Originally posted by @sondrelg in #104 (comment)

Thank you for the project. I would like to add python-gino support. We use https://github.com/Turall/FastApi-boilerplate our template for creating an API. For our project, we want to use your module. But you don't support gino for this module.

Hello,
I would like to know if there is a way to have "lazy loading" using crudrouter on fastapi?
Is there a solution or a manner to do it?
Thank you in advance.

It would be amazing to have support for directly storing/retrieving model data in Redis, without the need for an extra SQLAlchemy layer.

fastapi_crudrouter is not working on Py3.8

Python 3.7.8 (default, Nov  3 2020, 14:38:34)
[Clang 11.0.0 (clang-1100.0.33.16)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from sqlalchemy.orm import sessionmaker
>>> from fastapi_crudrouter import SQLAlchemyCRUDRouter
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/chethan/.pyenv/versions/3.7.8/lib/python3.7/site-packages/fastapi_crudrouter/__init__.py", line 1, in <module>
    from .core import MemoryCRUDRouter, SQLAlchemyCRUDRouter, DatabasesCRUDRouter
  File "/Users/chethan/.pyenv/versions/3.7.8/lib/python3.7/site-packages/fastapi_crudrouter/core/__init__.py", line 5, in <module>
    from .databases import DatabasesCRUDRouter
  File "/Users/chethan/.pyenv/versions/3.7.8/lib/python3.7/site-packages/fastapi_crudrouter/core/databases.py", line 16, in <module>
    class DatabasesCRUDRouter(CRUDGenerator):
  File "/Users/chethan/.pyenv/versions/3.7.8/lib/python3.7/site-packages/fastapi_crudrouter/core/databases.py", line 18, in DatabasesCRUDRouter
    def __init__(self, schema: BaseModel, table: Table, database: Database, *args, **kwargs):
NameError: name 'Table' is not defined

API Version:

crudfast pip list | grep "fastapi"
fastapi            0.63.0
fastapi-crudrouter 0.3.1

When using TortoiseCRUDRouter, the CRUD routers generate a base prefix that has the same casing as the db_model.

For instance, for a router with prefix "/api", and an object CRUD router defined by

router.include_router(TortoiseCRUDRouter(schema=UserPydantic, db_model=User))

The generated CRUD route that appears in the /doc is /api/User, which is quite odd for APIs.

This issue seems to come from the interaction of these 2 lines

Since the prefix is defined in the TortoiseCRUDRouter, it is never lowercased

This might be intended. Anyway, this behavior can be circumvented by passing prefix to the TortoiseCRUDRouter.

Hello!, I have simple model with a nullable field:

from tortoise import fields, models

class Scan(models.Model):
    id = fields.IntField(pk=True)
    scan_data = fields.JSONField()
    time_stamp = fields.DatetimeField(auto_now=True, null=True)  
    class Meta:
        table = "scans"

router = TortoiseCRUDRouter(
    schema=models.Scan_Pydantic, 
    db_model=models.Scan
}

If I send some data to the post route, I expect that the "time_stamp" field will not need to be present in the payload because it is declared as nullable.

{
    "carrier": "FEDEX",
    "scan_data": {"scan_code":  "CD",  "scan_description":  "DELAY"}
}

but instead this fails with a 422 saying the "time_stamp" field is required.

I understand that adding the field with a null value work but I feel like declaring the field as nullable on model should make the field optional entirely in body of create requests.

I'd be happy to submit a pr for this change if there is support for it ! :)

In an upcoming version of CRUDRouter I am hoping to add Query / Filter support. I've created this thread in hopes of getting some community feedback on how you guys would like the query params constructed.

• Ability to filter column(s) eg: /potato?color=red or /potato?color=red&?type=small
• Ability to filter multiple values in the same column eg: /potato?color=red&color=blue
• Ability to order by columns
• etc...

Please feel free to make any suggestions 😄

fastapi 0.63.0
fastapi-crudrouter 0.6.1

Trying to use SQLAlchemyCRUDRouter for joined tables ,
All endpoints created automatically (amazing:)).

Tried using the "create one " for input:

{
  "customer_no": 1,
  "subscriber": [
    {
      "subscriber_no": 1,
      "is_active": false,
      "owner": 1
    }
  ]
}

End up with error:

File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/state.py", line 434, in _initialize_instance
    return manager.original_init(*mixed[1:], **kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/decl_base.py", line 1086, in _declarative_constructor
    setattr(self, k, kwargs[k])
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 427, in __set__
    self.impl.set(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1512, in set
    collections.bulk_replace(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/collections.py", line 817, in bulk_replace
    appender(member, _sa_initiator=initiator)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/collections.py", line 1131, in append
    item = __set(self, item, _sa_initiator)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/collections.py", line 1096, in __set
    item = executor.fire_append_event(item, _sa_initiator)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/collections.py", line 727, in fire_append_event
    return self.attr.fire_append_event(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1345, in fire_append_event
    value = fn(state, value, initiator or self._append_token)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/attributes.py", line 1675, in emit_backref_from_collection_append_event
    child_state, child_dict = instance_state(child), instance_dict(child)
AttributeError: 'dict' object has no attribute '_sa_instance_state'

Code:

Routes:

from config.config import postgress_engine,SessionLocal
from fastapi_crudrouter impor SQLAlchemyCRUDRouter as SQLDBCrudRouter

customer_router = APIRouter()

def get_db():
    session = SessionLocal()
    try:
        yield session
        session.commit()
    finally:
        session.close()


customer_router = SQLDBCrudRouter(schema=Customer,create_schema=CustomerCreate,
             db_model=CustomerModel,db=get_db,prefix='customer')

schemas.py

from pydantic import BaseModel
from typing import List, Optional


class SubscriberBase(BaseModel):
    subscriber_no: int
    is_active: bool = False

class SubscriberCreate(BaseModel):
    pass


class Subscriber(SubscriberBase):
    owner: int

    class Config:
        orm_mode = True

class CustomerCreate(BaseModel):
    customer_no: Optional[int] = None
    subscriber: Optional[List[Subscriber]] =None

class Customer(CustomerCreate):
    id: int
    class Config:
        orm_mode = True

models.py

from sqlalchemy import Boolean, Column, ForeignKey, Integer, String,DateTime
from sqlalchemy.orm import relationship
from config.config import Base


class CustomerModel(Base):
    __tablename__ = 'customer'
    id = Column(Integer, primary_key=True, index=True)
    customer_no= Column(Integer,index=True)
    subscriber= relationship("SubscriberModel", back_populates="owner")


class SubscriberModel(Base):
    __tablename__ = 'subscriber'
    id = Column(Integer, ForeignKey("customer.id"))
    subscriber_no= Column(Integer, primary_key=True, index=True)
    owner = relationship("CustomerModel", back_populates="subscriber")

Hello.

I'm trying fastapi-crudrouter. Unfortunetly, I'm experiencing an unrecognized issue:

ModuleNotFoundError: No module named 'fastapi_crudrouter'

I have virtualenv with fastapi-crudrouter installed

rom pydantic import  BaseModel
from fastapi import FastAPI
from fastapi_crudrouter import MemoryCRUDRouter as CRUDRouter


class Potato(BaseModel):
    id: int
    color: str
    mass: float

app = FastAPI()

app.include_router(CRUDRouter(schema=Potato))

requiremnts.txt

fastapi==0.68.1
fastapi-crudrouter==0.8.1
pydantic==1.8.2
starlette==0.14.2
typing-extensions==3.10.0.2

• Sqlalchemy #48
• Ormar #48
• In Memory
• Databases
• Tortoise

Hello,
First off, absolutely love this library. Fantastic work @awtkns.

I was wondering if there is any sort of known solution for implementing tags on a per-route basis?

I am currently using @kael-shipman's awesome Swagger-UI plugin, Hierarchical Tags. This allows my to Swagger docs to look like this, with specific routes grouped and nested:

Essentially, with this plugin I can establish parent and child "nodes". Here is how I achieve the above screenshot with a vanilla FastAPI router:

# Create list of dict where the first dict is the parent node, and the following dicts are children-nodes
tags_metadata = [
    {"name": "Trusted", "description": "API endpoints for Trusted resources."},
    {
        "name": "Trusted|Groups",
        "description": "Query operations for Trusted Group resources. List (with offset and limit params supported) or get Groups by ID.",
    },
]

# Create the router
router = APIRouter(
    prefix="/trusted",
    responses={404: {"description": "Not found"}},
)

# Create _list_ route for Group resource, assign this route as a child node to Trusted > Groups > List
@router.get(
    "/groups/",
    dependencies=[Depends(JWTBearer())],
    response_model=List[TrustedGroupSchemaWithUnits],
    tags=["Trusted|Groups|List"],  # This tags=[] assignment here, how can I do that with CRUDRouter?
)
async def list_groups(
    db: Session = Depends(get_trusted_db),
    skip: int = 0,
    limit: int = 25,
):
    groups = db.query(TrustedGroup).offset(skip).limit(limit).all()
    return groups


# Create _get_ route for Group resource, assign this route as a child node to Trusted > Groups > List
@router.get(
    "/group/{id}",
    dependencies=[Depends(JWTBearer())],
    response_model=TrustedGroupSchemaWithUnits,
    tags=["Trusted|Groups|Get"],  # Assigned hierarchically as Trusted > Groups > Get
)
async def get_group(id: int, db: Session = Depends(get_trusted_db)):
    group = db.query(TrustedGroup).get(id)
    return group


# .... omitting rest of routes for simplicity's sake, but hierarchical tag setup matches the above

# Instantiate the FastAPI app instance with my "parent" tags defined above
app = FastAPI(
        title="POC for awesome FastAPI-CRUDRouter library,
        description="This API is really cool, but is not real",
        version="1.3.3.7",
        openapi_tags=tags_metadata  # assign the parent/sub-parent tags defined on line 1 to openapi_tags
    )

# Include router
app.include_router(router)

This is a small snippet that should demonstrate the basics of how the hierarchical tags plugin works (eg. matches tag pattern and uses | delimiter to denote sub-nodes).

So, in summary, I need to be able to override the tags param for each of the routes CRUDRouter generates. I am a bit unclear from the documentation on how to go about doing this. From the Overriding Routes documentation, I have attempted to override my CRUDRouter routes to include a custom tags param but I seem to lose the rest of the functionality CRUDRouter offers.

For example, here is what a route looks like without overriding:

Code:

router = SQLAlchemyCRUDRouter(
    schema=NonS1EventSchema,
    create_schema=NonS1EventSchemaCreate,
    db_model=NonS1Event,
    db=get_event_db,
    dependencies=[Depends(JWTBearer())],
    prefix="non-s1",
    tags=["Event|Non S1 Events"],
)

Screenshot:

And then when I attempt to override a function, and pass only the tags param (I do not want to override any of the other pieces CRUDRouter generates), I run into difficulties:

Code:

router = SQLAlchemyCRUDRouter(
    schema=NonS1EventSchema,
    create_schema=NonS1EventSchemaCreate,
    db_model=NonS1Event,
    db=get_event_db,
    dependencies=[Depends(JWTBearer())],
    prefix="non-s1",
    tags=["Event|Non S1 Events"],
)


@router.get("", tags=["Event|Non S1 Events|List"])
def list_s1_events():
    pass

Screenshot:

The issues here:

• The route is duplicated in Swagger (perhaps it is still applying the default tags so it is listed twice?)
• I lose the parameters section
• I lose the Example Value in the response section
• I lose the Validation Error in the response section
• And I may also lose actual query functionality, but I have not tested this yet.

Is this because my override function body only contains pass? My presumption was that this approach would behave similar to overriding a class and calling super(), still inheriting all the parameters passed to the SQLAlchemyCRUDRouter() (eg. schema, create_schema, db_model, etc.) and generate the rest of the path/query parameters CRUDRouter generates.

Is there a way I can achieve this functionality (passing custom tags on the router function level) without modifying the source? Perhaps by passing the tags as a kwarg somewhere, or overriding "correctly"-- upon reflection the pass makes sense in why it is not "inheriting" so to speak. Am I able to inherit from a function?

Or should I fork and submit a PR?

Another idea I have is implementing a custom SQLAlchemyCRUDRouter class....

Again, thanks so much for this amazing project.

The Get All path return error on SQL Server

class BsdCountry(Base):
    __tablename__ = "bsd_country"
    country_id = Column(Integer, primary_key=True)
    country_iso_alpha2_code = Column(Unicode(50))
    country_iso_alpha3_code = Column(Unicode(50))

class BsdCountry(BaseModel):
    country_id: int
    country_iso_alpha2_code: Optional[str]
    country_iso_alpha3_code: Optional[str]
    
    class Config:
        orm_mode = True

from fastapi_crudrouter import SQLAlchemyCRUDRouter

router = SQLAlchemyCRUDRouter(
    schema=schemas.crud.BsdCountry,
    create_schema=schemas.crud.BsdCountry,
    db_model=models.crud.BsdCountry,
    db=deps.get_dw_crud,
    prefix='bsd_country',
)

INFO:     192.168.121.23:51712 - "GET /api/v1/crud/bsd_country?skip=0 HTTP/1.1" 500 Internal Server Error
ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/uvicorn/protocols/http/httptools_impl.py", line 398, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "/usr/local/lib/python3.8/site-packages/uvicorn/middleware/proxy_headers.py", line 45, in __call__
    return await self.app(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/fastapi/applications.py", line 199, in __call__
    await super().__call__(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/applications.py", line 111, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/middleware/errors.py", line 181, in __call__
    raise exc from None
  File "/usr/local/lib/python3.8/site-packages/starlette/middleware/errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "/usr/local/lib/python3.8/site-packages/starlette/middleware/gzip.py", line 18, in __call__
    await responder(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/middleware/gzip.py", line 35, in __call__
    await self.app(scope, receive, self.send_with_gzip)
  File "/usr/local/lib/python3.8/site-packages/starlette/middleware/cors.py", line 78, in __call__
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/exceptions.py", line 82, in __call__
    raise exc from None
  File "/usr/local/lib/python3.8/site-packages/starlette/exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 566, in __call__
    await route.handle(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 227, in handle
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.8/site-packages/starlette/routing.py", line 41, in app
    response = await func(request)
  File "/usr/local/lib/python3.8/site-packages/fastapi/routing.py", line 201, in app
    raw_response = await run_endpoint_function(
  File "/usr/local/lib/python3.8/site-packages/fastapi/routing.py", line 150, in run_endpoint_function
    return await run_in_threadpool(dependant.call, **values)
  File "/usr/local/lib/python3.8/site-packages/starlette/concurrency.py", line 34, in run_in_threadpool
    return await loop.run_in_executor(None, func, *args)
  File "/usr/local/lib/python3.8/concurrent/futures/thread.py", line 57, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/usr/local/lib/python3.8/site-packages/fastapi_crudrouter/core/sqlalchemy.py", line 72, in route
    db.query(self.db_model).limit(limit).offset(skip).all()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2633, in all
    return self._iter().all()
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 2768, in _iter
    result = self.session.execute(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1653, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1520, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 313, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1381, in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 533, in _compile_w_cache
    compiled_sql = self._compiler(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 566, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/mssql/base.py", line 1686, in __init__
    super(MSSQLCompiler, self).__init__(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 766, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 455, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 490, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 3086, in visit_select
    text = self._compose_select_body(
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 3262, in _compose_select_body
    text += self._row_limit_clause(select, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/mssql/base.py", line 1813, in _row_limit_clause
    self._check_can_use_fetch_like(select)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/mssql/base.py", line 1791, in _check_can_use_fetch_like
    raise exc.CompileError(
sqlalchemy.exc.CompileError: MSSQL requires an order_by when using an OFFSET or a non-simple LIMIT clause

I want to do pagination with offset and limit using fast-crurdrouter, but in the parameters I can't specificy in the parameters with SQLALCHEMYCRUDROUTER.

Probably use ODMantic: https://github.com/art049/odmantic

How would you test the implementation? mock a mongo db?

Is there an example for doing more then one table.

Potato - table 1
Meat - table 2

Hi,

I am trying to overload a route.

My structure is that crudrouters are in a separate file:

crudrouter.py

orders_router = SQLAlchemyCRUDRouter(
    schema=OrderSchema,
    create_schema=OrderCreate,
    update_schema=OrderEdit,
    db_model=Order,
    db=get_db,
    prefix="orders",
    dependencies=[Depends(token_auth)],
)

main.py

from crudrouters import orders_router
app.include_router(orders_router)

it works perfectly.

However when I try to overload:

@orders_router.get("/")
async def overload_orders():
    return "Hello world 1"

@orders_router.get("/orders")
async def overload_orders():
    return "Hello world 2"

Following your example:

app = FastAPI()
router = CRUDRouter(model=mymodel)

@router.get('/{item_id}')
def overloaded_get_all():
    return 'My overloaded route'

I am unable to overload using the router.

I tried:

• using orders_routers.get() in the crudrouter.py right after the definition of the SQLAlchemyCRUDRouter
• using orders_routers.get() before and after the app.include_router
• using with and without the prefix defined in SQLAlchemyCRUDRouter
• using app.get() after the app.include_router

and finally:

• using app.get before the app.include_router

which works:

@app.get("/orders")
async def overload_orders():
    return "successful overload"

app.include_router(orders_router)

Is this the normal behavior or I am missing something to properly use the feature?
Thank you

This would help to avoid confusion. The function prototype bellow is frankly confusing and should be standardized

 SQLAlchemyCRUDRouter(
    model=Potato, 
    db_model=PotatoModel, 
    db=get_db, 
    create_schema=PotatoCreate, 
    prefix='potato'
)

When I use the SQLAlchemyCRUDRouter in my project, the Pydantic schema is not being imported into the model on the create item calls.

I've traced this into the crudrouter init() call:

def _create(self, *args: Any, **kwargs: Any) -> Callable[..., Model]:
        def route(
            model: self.create_schema,  # type: ignore
            db: Session = Depends(self.db_func),
        ) -> Model:
            try:
                db_model: Model = self.db_model(**model.dict())
                db.add(db_model)
                db.commit()
                db.refresh(db_model)
                return db_model
            except IntegrityError:
                db.rollback()
                raise HTTPException(422, "Key already exists")

        return route

On a normal test:
Pydantic Object: sample_object = AdminUserCreate(name="string",email='string',password='string')
This works: print("Object: ",sample_object.dict()))
This does not work: print("Dict: ",**sample_object.dict())

I'm new to pydantic, so if it is a versioning issue, please do suggest the correct version this library requires.

pls sir, i need this for Perf Testing

Right now you return the models ordered by id (or not really ordered didn't get into details of all backends, and without explicit order by the order in db is not guaranteed).

I would like to be able to pass a list of columns that should be used to, note that it should be treated as a default sorting, other functionality or params passed to query can change it later if you decide to support such function.

Given sample:

class Flower(pydantic.BaseModel):
    id: int
    name: str
    color: str
    petals: int
    created_date: datetime.date
    updated_date: datetime.date

I want to be able to by default sort by last updated and then by name:

router = MemoryCRUDRouter(
    schema=User,
    prefix="users",
    exclude_in_list: ["-updated_date", "name"] # to sort descending pass - in front of name
)

This would cause the returned values should be sorted in a proper way.

Optionally this should also support #53 and passing like "flower__garden__name" would order by property name in model Garden related to model Flower.

Hello,

Me again, just reporting this so I do not forget.

I found a workaround but I observe the following behavior and I am not sure if that is a bug or not.

Router declared as:

orders_router = SQLAlchemyCRUDRouter(
    schema=OrderSchema,
    create_schema=OrderCreate,
    update_schema=OrderEdit,
    db_model=Order,
    db=get_db,
    prefix="orders",
    dependencies=[Depends(token_auth)],
)

Update route overloaded like this:

@orders_router.put("/{order_id}")
async def update_order(
    order_id: str, update_data: OrderEdit, db=Depends(get_db), user=Depends(token_auth)
):
    print(f"Overloaded {order_id}")

And it is ignored, the default Update route from SQLAlchemyCRUDRouter is preferred.

However with a create route it works:

@orders_router.post("")
async def create_order(
    data: OrderCreate, db=Depends(get_db), user=Depends(token_auth),
):
    print("Overloaded")

And if I do this:

orders_router = SQLAlchemyCRUDRouter(
    schema=OrderSchema,
    create_schema=OrderCreate,
    update_schema=OrderEdit,
    db_model=Order,
    db=get_db,
    prefix="orders",
    dependencies=[Depends(token_auth)],
    update_route=False,
)

Then the default one is not enabled apparently and my overloaded Update route is correctly used.
This also happens with a delete one route: orders_routers.delete("/{order_id}").

Hi, actually there is a need for a small development on _create as well(sqlalchemy.py). When inserting a row via post, I ended up with a different error message. I tried to insert a row into a table that have foreign key constraint on another table. The other table have no primary key value associated with row I tried to insert. So, I violated foreign key constraint.

except IntegrityError: db.rollback() raise HTTPException(422, "Key already exists")

As a result of code above, I get "key already exist". It could be something like that;
except IntegrityError as e: db.rollback() raise HTTPException(422, ", ".join(e.args))

Amazing Framework, really love it,
please update or place the patch endpoint to update certain data that have been passed while leaving the unspecified data intact in the database..

Hi,

I'm creator of ormar which is an async "mini" ORM with pydantic validation, that can be used directly (as request and response models) with fastapi.

I want to make ormar a full-fledged member of the fastapi ecosystem, so I'm asking if you would like to add ormar support to fastapi-crudrouter? I can probably issue a PR with this functionality if you are interested. :)

Hi,
is it possible to "inject" somehow the value from a dependency to the create or update model?
I would like to add the updating user in my create schema.
Any ideas?
Thank you for the great work

Similar to the id field, I have a created_on field the database will auto create for me. Now, what is the best way to not allow the field in POST, PUT but have it in GETs?

1. Require the use of Create and Update Schema. Nothing would need to change, but all that schema creation smells of boilerplate.
2. Modify the _utils.py schema_factory to allow for additional fields to exclude. However, at that point you might as well add id to that list and make it generic.
3. Modify the _utils.py schema_factory to look for allow_mutation and exclude it along with id.

for f in schema_cls.__fields__.values()
if f.name != pk_field_name and f.field_info.allow_mutation

Now my field could look something like

# Not in OpenAPI post or put
update_on: datetime.datetime = Field(
    allow_mutation=False, description="Last update from sync system"
)

Is there a better way to remove fields from Create and Update schemas easily?

Discussed in #108

Pretty common requirement is returning a list of items with only selected fields populated, while getting an item per id should return all fields (or at least different set of fields then list).

That's why excluding a given set of fields in given route should be possible.
Of course only optional pydantic fields can be excluded to not raise the ValidationError.

Example:

class Flower(pydantic.BaseModel):
    id: int
    name: str
    color: str
    petals: int
    created_date: datetime.date
    updated_date: datetime.date

Now let's say that in main view I need only name and created_date, as I will display a list of links, but in getting by id I need all of fields except the dates since they will be updated automatically.

router = MemoryCRUDRouter(
    schema=User,
    prefix="users",
    exclude_in_list: ["color", "petals", "updated_date"]  # pass list of fields to exclude in list
    exclude_in_get: ["created_date", "updated_date"]  # pass list of fields to exclude in get
)

Note that the names to exclude should also handle potential relation fields names as in #53. That way you can join in details view, but not in the list view (or opposite).

Passed exclude should be used as pydantic exclude param that removes the fields from result of API call.

Hi ;)

I realized I'm doing something pretty similar right now for my API endpoints but would like to use your library to save some work.
But for that I need more functionalities that I'm happy to help implement if you accepts them.

So I started a series of issues that describes what I'm missing right now, this is the first one.

Right now there is no way to select/pass also related models in routes if I understand correctly?

So there is no way to fetch the users with tasks in following example:

class Task(pydantic.BaseModel):
    id: int
    owner: Optional["User"]

class User(pydantic.BaseModel):
    id: int
    tasks: Optional[List[Task]]

Task.update_forward_refs()

In backends that should translate to joins in queries

Since despite the relations you might don't want to read them all in your Router I guess something like related_models: List[str] should be passed to router as to which related models should be loaded?

router = MemoryCRUDRouter(
    schema=User,
    prefix="users",
    related_models=["tasks"]  # pass name of the relation or table depending on backend
)

That way calling .get("/users/1/") would return User with populated related tasks.

Hello,
First of all, I am not an expert of FastAPI... so it might not be related at all to fastapi-crudrouter.

I have Pydantic class which holds file metadata:

from pydantic import BaseModel, Field, validator
from fastapi import FastAPI
from fastapi_crudrouter import MemoryCRUDRouter as CRUDRouter

class File(BaseModel):
    FileSize: Optional[ByteSize]
    absolute_path: FilePath

    def __init__(self, *args, **kwargs: Any):
        super().__init__(**kwargs)
        # Size, creationalidator pre
        st = self.absolute_path.stat()
        self.FileSize = st.st_size
        
    @validator("absolute_path")
    def convert_str_to_path(cls, v, values, **kwargs):
        """Convert string to absolute_path
        Returns:
            Path: Absolute path
        """
        if v is None:
            v = values[0]
        if isinstance(v, str):
            v = Path.path(v)
        return v.resolve()
# Works with local file    
# f = File(absolute_path='somefile.py')

# FastAPI with crudrouter
app = FastAPI()
app.include_router(CRUDRouter(schema=File))

I read the documentation to do something like this but I don't know what to put in CreateFile class ?

class CreateFile(BaseModel):
    pass

app.include_router(CRUDRouter(schema=File, create_schema=CreateFile))

In pure FastAPI, upload is done here:

from fastapi import FastAPI, UploadFile
from fastapi import File as FastFile # To not interfear with hand made class name

@app.post('/')
async def upload_file(file: UploadFile = FastFile(...)):
    with open(f'{file.filename}', 'wb') as buffer:
        shutil.copyfileobj(file.file, buffer)

Any ideas if it is possible and how ?

Maybe with this routing functionality ?
https://fastapi-crudrouter.awtkns.com/routing

app = FastAPI()
# class CreateFile(BaseModel):
#     pass
router = CRUDRouter(
    schema=File,
    # create_schema=CreateFile
)

router = CRUDRouter(
    schema=File,
    # create_schema=CreateFile
)

@router.post("/file")
async def upload_file(file: UploadFile = FastFile(...)):
    with open(f"{file.filename}", "wb") as buffer:
        shutil.copyfileobj(file.file, buffer)
    # return ???


app.include_router(router)

Regards

I found an issue when Tortoise's PydanticModel is used, where the related models are not being populated.
Due to Tortoise's async nature and Pydantic lack of asyncio support, some methods need to be called to populate models with nested submodels properly (related link).

I hacked a fix for this in my fork, so we can continue development in my company.
I want to clean it up and make a PR when I have some free time, but it seems that there is quite an elaborate test setup.
Could you give me some pointers?
Maybe I should make a test case apart from the rest, and you could give me the right pointers later when everything is finished.

What do you think?