Issue trying to use SQLModel with AsyncSession and object conversion from Row

[Miguelme]

First Check

• [X] I added a very descriptive title to this issue.
• [X] I used the GitHub search to find a similar issue and didn't find it.
• [X] I searched the SQLModel documentation, with the integrated search.
• [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
• [X] I already read and followed all the tutorial in the docs and didn't find an answer.
• [X] I already checked if it is not related to SQLModel but to Pydantic.
• [X] I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• [X] I commit to help with one of those options 👆

Example Code

from sqlmodel.ext.asyncio.session import AsyncSession
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.orm import sessionmaker
from sqlmodel import Field, SQLModel
from typing import Optional

user = "user"
password = "password"
host = "db"
db = "db"
engine = create_async_engine(f"mysql+aiomysql://{user}:{password}@{host}/{db}")
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine, class_=AsyncSession)


async def get_db():
    async with SessionLocal() as session:
        yield session


class Item(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    title: str = Field()
    content: str = Field()
    


## Endpoint for delete
from fastapi import FastAPI, Depends
from sqlalchemy import select, update
from sqlmodel.ext.asyncio.session import AsyncSession
from sqlalchemy.orm import selectinload
from database import Item, get_db
import logging

app = FastAPI()

async def get_item(item_id: int, db: AsyncSession):
    query = select(Item).where(Item.id == item_id).options(selectinload('*'))
    result = await db.exec(query)
    return result.first()

@app.delete("item/{item_id}")
async def delete_item(item_id: int, db: AsyncSession = Depends(get_db)):
    await db.delete(await get_item(item_id, db))
    await db.commit()
    return await get_all(db)

Description

I'm trying to do some really silly implementation of a CRUD but I am not able to make it work using an Async Connection to the MySQL Database, the error I get is that the object is not being converted to a SQLModel but that it stays as a Row. It's the following:

fastapi_starter-api-1  | 
fastapi_starter-api-1  | Traceback (most recent call last):
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/uvicorn/protocols/http/h11_impl.py", line 403, in run_asgi
fastapi_starter-api-1  |     result = await app(self.scope, self.receive, self.send)
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/uvicorn/middleware/proxy_headers.py", line 78, in __call__
fastapi_starter-api-1  |     return await self.app(scope, receive, send)
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/fastapi/applications.py", line 269, in __call__
fastapi_starter-api-1  |     await super().__call__(scope, receive, send)
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/starlette/applications.py", line 124, in __call__
fastapi_starter-api-1  |     await self.middleware_stack(scope, receive, send)
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/starlette/middleware/errors.py", line 184, in __call__
fastapi_starter-api-1  |     raise exc
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/starlette/middleware/errors.py", line 162, in __call__
fastapi_starter-api-1  |     await self.app(scope, receive, _send)
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/starlette/exceptions.py", line 93, in __call__
fastapi_starter-api-1  |     raise exc
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/starlette/exceptions.py", line 82, in __call__
fastapi_starter-api-1  |     await self.app(scope, receive, sender)
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
fastapi_starter-api-1  |     raise e
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
fastapi_starter-api-1  |     await self.app(scope, receive, send)
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 670, in __call__
fastapi_starter-api-1  |     await route.handle(scope, receive, send)
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 266, in handle
fastapi_starter-api-1  |     await self.app(scope, receive, send)
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/starlette/routing.py", line 65, in app
fastapi_starter-api-1  |     response = await func(request)
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/fastapi/routing.py", line 227, in app
fastapi_starter-api-1  |     raw_response = await run_endpoint_function(
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/fastapi/routing.py", line 160, in run_endpoint_function
fastapi_starter-api-1  |     return await dependant.call(**values)
fastapi_starter-api-1  |   File "/code/./main.py", line 58, in delete_item
fastapi_starter-api-1  |     await db.delete(await get_item(item_id, db))
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/ext/asyncio/session.py", line 378, in delete
fastapi_starter-api-1  |     return await greenlet_spawn(self.sync_session.delete, instance)
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 123, in greenlet_spawn
fastapi_starter-api-1  |     result = context.switch(*args, **kwargs)
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 2639, in delete
fastapi_starter-api-1  |     util.raise_(
fastapi_starter-api-1  |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
fastapi_starter-api-1  |     raise exception
fastapi_starter-api-1  | sqlalchemy.orm.exc.UnmappedInstanceError: Class 'sqlalchemy.engine.row.Row' is not mapped

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.9.9

Additional Context

I think what is happening is that the async connection is not set-up properly connected to the SQLModel implementation so it can get automatically converted to the SQLModel object but I'm not sure which should be the expected process. Please let me know if you need more information about it

[phi-friday]

Is it an error that occurred after attempting to create a table?

[Miguelme]

No, when trying to delete the resource given that the GET is returning a row instead of the actual Item sqlmodel (apparently).

This is the exact line where it fails:

await db.delete(await get_item(item_id, db))

[antont]

With async you need this for get_item, to add the scalar bit. At least with the new 2.0 style SQLAlchemy core api, which is included in 1.4.

from sqlalchemy.future import select  #2.0 core style for async, https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html

   stmt = select(dbmodel).where(dbmodel.id == obid)
   res = await session.execute(stmt)
   return res.unique().scalar_one()

[Miguelme]

Why is this difference only specific to the async style? Is there any place where I could find it documented to avoid it in the future?

[antont]

SQLAlchemy async docs are at https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html