FastAPI tutorial with aiosqlite and real async calls

5,315 views
Skip to first unread message

gordon.d...@gmail.com

unread,
Nov 15, 2021, 6:43:05 PM11/15/21
to sqlalchemy-devel
Further to a conversation in the dev meeting last Thursday, I wanted to see if I could get the FastAPI tutorial going with aiosqlite using actual async calls and without using the Databases package. The current FastAPI "SQL databases" tutorial


uses sync calls because it says that "SQLAlchemy doesn't have compatibility for using await directly" (which may have been true when the tutorial was first written).

What I have so far is here


If I launch uvicorn with

uvicorn sql_app.main:app --reload

and then load the page


the methods to read one or more users from the database work fine. For example, if I use "Read User" to send


it invokes the route in main.py

@app.get("/users/{user_id}", response_model=schemas.User)
async def read_user(user_id: int, db: AsyncSession = Depends(get_db)):
    the_user = await crud.get_user(db, user_id=user_id)
    if the_user is None:
        raise HTTPException(status_code=404, detail="User not found")
    return the_user
    
which calls crud.get_user()

async def get_user(db: AsyncSession, user_id: int):
    result = await db.execute(
        select(models.User)
        .where(models.User.id == user_id)
        .options(selectinload(models.User.items))
    )
    return result.scalars().first()

and I get the correct JSON back:

{"email":"email_1","id":1,"is_active":true,"items":[]}

However, if I try to use "Create User" and supply

{
  "email": "email_2",
  "password": "pwd_2"
}

it invokes the route in main.py

@app.post("/users/", response_model=schemas.User)
async def create_user(user: schemas.UserCreate, db: AsyncSession = Depends(get_db)):
    existing_user = await crud.get_user_by_email(db, email=user.email)
    if existing_user:
        raise HTTPException(status_code=400, detail="Email already registered")
    new_user = await crud.create_user(db=db, user=user)
    return new_user

which calls crud.create_user

async def create_user(db: AsyncSession, user: schemas.UserCreate):
    fake_hashed_password = user.password + "notreallyhashed"
    db_user = models.User(
        email=user.email, hashed_password=fake_hashed_password
    )
    db.add(db_user)
    await db.commit()
    return db_user

and I get a 500 Internal Server Error. The traceback is

ERROR:    Exception in ASGI application
Traceback (most recent call last):
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/uvicorn/protocols/http/h11_impl.py", line 373, in run_asgi
    result = await app(self.scope, self.receive, self.send)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/uvicorn/middleware/proxy_headers.py", line 75, in __call__
    return await self.app(scope, receive, send)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/fastapi/applications.py", line 208, in __call__
    await super().__call__(scope, receive, send)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/starlette/applications.py", line 112, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/starlette/middleware/errors.py", line 181, in __call__
    raise exc
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/starlette/middleware/errors.py", line 159, in __call__
    await self.app(scope, receive, _send)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/starlette/exceptions.py", line 82, in __call__
    raise exc
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/starlette/exceptions.py", line 71, in __call__
    await self.app(scope, receive, sender)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/starlette/routing.py", line 656, in __call__
    await route.handle(scope, receive, send)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/starlette/routing.py", line 259, in handle
    await self.app(scope, receive, send)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/starlette/routing.py", line 61, in app
    response = await func(request)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/fastapi/routing.py", line 234, in app
    response_data = await serialize_response(
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/fastapi/routing.py", line 127, in serialize_response
    value, errors_ = field.validate(response_content, {}, loc=("response",))
  File "pydantic/fields.py", line 723, in pydantic.fields.ModelField.validate
  File "pydantic/fields.py", line 906, in pydantic.fields.ModelField._validate_singleton
  File "pydantic/fields.py", line 913, in pydantic.fields.ModelField._apply_validators
  File "pydantic/class_validators.py", line 310, in pydantic.class_validators._generic_validator_basic.lambda12
  File "pydantic/main.py", line 737, in pydantic.main.BaseModel.validate
  File "pydantic/main.py", line 629, in pydantic.main.BaseModel.from_orm
  File "pydantic/main.py", line 1019, in pydantic.main.validate_model
  File "pydantic/utils.py", line 418, in pydantic.utils.GetterDict.get
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 481, in __get__
    return self.impl.get(state, dict_)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 926, in get
    value = self._fire_loader_callables(state, key, passive)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 962, in _fire_loader_callables
    return self.callable_(state, passive)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/orm/strategies.py", line 910, in _load_for_state
    return self._emit_lazyload(
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/orm/strategies.py", line 1046, in _emit_lazyload
    result = session.execute(
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1688, in execute
    conn = self._connection_for_bind(bind)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1529, in _connection_for_bind
    return self._transaction._connection_for_bind(
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 747, in _connection_for_bind
    conn = bind.connect()
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/future/engine.py", line 406, in connect
    return super(Engine, self).connect()
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3197, in connect
    return self._connection_cls(self, close_with_result=close_with_result)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
    else engine.raw_connection()
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3276, in raw_connection
    return self._wrap_pool_connect(self.pool.connect, _connection)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3243, in _wrap_pool_connect
    return fn()
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 310, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 868, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 476, in checkout
    rec = pool._do_get()
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 256, in _do_get
    return self._create_connection()
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
    return _ConnectionRecord(self)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 371, in __init__
    self.__connect()
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 666, in __connect
    pool.logger.debug("Error on connect(): %s", e)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 590, in connect
    return dialect.connect(*cargs, **cparams)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 584, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/dialects/sqlite/aiosqlite.py", line 292, in connect
    await_only(connection),
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 67, in await_only
    raise exc.MissingGreenlet(
sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_() here. Was IO attempted in an unexpected place? (Background on this error at: https://sqlalche.me/e/14/xd2s)


Notes:

1. My original attempt at repro code for aiosqlite used a stripped down version of the tutorial with only a single model named "Users" (i.e., with no relationships) and "Create User" worked fine in that scenario. When I went back to having "Items" related to "Users" then "Create User" started failing again. I suspect that I am missing something session-y here.

2. This is not specific to sqlite+aiosqlite. I tested aiosqlite after seeing the same issue with postgresql+asyncpg, which was my original target environment.

Mike Bayer

unread,
Nov 16, 2021, 9:56:04 AM11/16/21
to sqlalche...@googlegroups.com
the stack trace shows you have a lazy load of an attribute going on:

  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/orm/strategies.py", line 1046, in _emit_lazyload


this seems to be in context of pydantic doing some kind of validation of all fields on a persistent object where some attributes aren't loaded:

  File "pydantic/class_validators.py", line 310, in pydantic.class_validators._generic_validator_basic.lambda12
  File "pydantic/main.py", line 737, in pydantic.main.BaseModel.validate
  File "pydantic/main.py", line 629, in pydantic.main.BaseModel.from_orm
  File "pydantic/main.py", line 1019, in pydantic.main.validate_model
  File "pydantic/utils.py", line 418, in pydantic.utils.GetterDict.get
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 481, in __get__
    return self.impl.get(state, dict_)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 926, in get
    value = self._fire_loader_callables(state, key, passive)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 962, in _fire_loader_callables
    return self.callable_(state, passive)
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/orm/strategies.py", line 910, in _load_for_state
    return self._emit_lazyload(
  File "/home/gord/git/fastapi-tutorial-aiosqlite/venv/lib/python3.9/site-packages/sqlalchemy/orm/strategies.py", line 1046, in _emit_lazyload


this is all normal for asyncio, so you'd want to follow everything discussed at https://docs.sqlalchemy.org/en/14/orm/extensions/asyncio.html#preventing-implicit-io-when-using-asyncsession
--
You received this message because you are subscribed to the Google Groups "sqlalchemy-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-dev...@googlegroups.com.

gordon.d...@gmail.com

unread,
Nov 16, 2021, 1:06:34 PM11/16/21
to sqlalchemy-devel
Thanks, Mike. I was pretty sure that I had those all covered from my side (e.g., __mapper_args__ = {"eager_defaults": True}). The row is actually inserted into the table, it's just that pydantic causes the error when it tries to validate the object.

It looks like pydantic loops through the attributes of the object being validated and calls getattr(self._obj, key, default) on each one

https://github.com/samuelcolvin/pydantic/blob/8afdaab4acefb2f50e056b6661fbbda36b63b2b8/pydantic/utils.py#L386

So when I drop a breakpoint() there I see that it's calling getattr() on my User object for "email", "id", and "is_active" and they are all okay

> /home/gord/git/pydantic/pydantic/utils.py(387)get()
-> return getattr(self._obj, key, default)
(Pdb) self._obj
<User(id=2, email='email_2')>
(Pdb) key
'email'
(Pdb) c
> /home/gord/git/pydantic/pydantic/utils.py(387)get()
-> return getattr(self._obj, key, default)
(Pdb) self._obj
<User(id=2, email='email_2')>
(Pdb) key
'id'
(Pdb) c
> /home/gord/git/pydantic/pydantic/utils.py(387)get()
-> return getattr(self._obj, key, default)
(Pdb) self._obj
<User(id=2, email='email_2')>
(Pdb) key
'is_active'
(Pdb) c
> /home/gord/git/pydantic/pydantic/utils.py(387)get()
-> return getattr(self._obj, key, default)
(Pdb) self._obj
<User(id=2, email='email_2')>
(Pdb) key
'items'
(Pdb)

but when I "c" to proceed and call getattr() on "items" the traceback shows that we go straight back to SQLA (orm.attributes.py) and the lazy load happens there

  File "/home/gord/git/pydantic/pydantic/utils.py", line 387, in get
    return getattr(self._obj, key, default)
  File "/home/gord/git/sqla-gerrit/lib/sqlalchemy/orm/attributes.py", line 481, in __get__
    return self.impl.get(state, dict_)
  File "/home/gord/git/sqla-gerrit/lib/sqlalchemy/orm/attributes.py", line 926, in get

    value = self._fire_loader_callables(state, key, passive)
  File "/home/gord/git/sqla-gerrit/lib/sqlalchemy/orm/attributes.py", line 962, in _fire_loader_callables
    return self.callable_(state, passive)
  File "/home/gord/git/sqla-gerrit/lib/sqlalchemy/orm/strategies.py", line 910, in _load_for_state
    return self._emit_lazyload(
  File "/home/gord/git/sqla-gerrit/lib/sqlalchemy/orm/strategies.py", line 1047, in _emit_lazyload
    result = session.execute(
 
Might there be a way that we can suppress the lazy load on the SQLA side if the object has __mapper_args__ = {"eager_defaults": True}?

Mike Bayer

unread,
Nov 16, 2021, 1:31:53 PM11/16/21
to sqlalche...@googlegroups.com


On Tue, Nov 16, 2021, at 1:06 PM, gordon.d...@gmail.com wrote:
Thanks, Mike. I was pretty sure that I had those all covered from my side (e.g., __mapper_args__ = {"eager_defaults": True}). The row is actually inserted into the table, it's just that pydantic causes the error when it tries to validate the object.

the stack is a lazyload of a relationship() to a related mapper.  eager_defaults only covers columns in the immediate mapped table that have server-side defaults set upon them.  



It looks like pydantic loops through the attributes of the object being validated and calls getattr(self._obj, key, default) on each one


So when I drop a breakpoint() there I see that it's calling getattr() on my User object for "email", "id", and "is_active" and they are all okay

yup those are columns

but when I "c" to proceed and call getattr() on "items" the traceback shows that we go straight back to SQLA (orm.attributes.py) and the lazy load happens there

Might there be a way that we can suppress the lazy load on the SQLA side if the object has __mapper_args__ = {"eager_defaults": True}?

pydantic is trying to call upon an unloaded relationship to another class.   you would have to set up an eager loader to that relationship so that when the object is loaded, the collection or whatever is present.

I will also note that if you create a new, persistent object, and you dont assign an empty collection to a related collection, that will have no choice but to attempt lazyloading once you access it from the object as persistent.

which means this will lazyload:

   u = User(name='foo')
   sess.add(u)
   sess.flush()
   u.addresses

this will not:

   u = User(name='foo', addresses=[])
   sess.add(u)
   sess.flush()
   u.addresses

since this is a persistence-side issue maybe look at that.





gordon.d...@gmail.com

unread,
Nov 16, 2021, 2:03:16 PM11/16/21
to sqlalchemy-devel
Brilliant! Just adding the empty list for the relationship seems to have done the trick:

async def create_user(db: AsyncSession, user: schemas.UserCreate):
    fake_hashed_password = user.password + "notreallyhashed"
    db_user = models.User(
        email=user.email,
        hashed_password=fake_hashed_password,
        items=[],  # so pydantic won't trigger a lazy load
    )
    db.add(db_user)
    await db.commit()
    return db_user

Thanks again!
Reply all
Reply to author
Forward
0 new messages