Async return value after commit returns greenlet_spawn has not been called

[daniel-butler]

First Check

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

Commit to Help

• I commit to help with one of those options 👆

Example Code

https://github.com/daniel-butler/reproduce-commit-issue

# Look at the repository for the runnable code
...

@app.post("/")
async def root(inpt: Input, db: AsyncSession = Depends(get_db)):
    entry = Entry(content=inpt.content)
    db.add(entry)
    await db.commit()
    await db.refresh(entry)
    print(f"Created entry with id: {entry.id}")  # Can access the id here!

    table_two = TableTwo(content="")
    db.add(table_two)
    entry_id = entry.id   # <--- Using this variable in the response will avoid the error
    await db.commit()
    return {"id": entry.id}

Description

I'm not really sure if SQLModel is the correct package, but figured I would start here. Even though the error is from SQLAlchemy I think it is outside of the SQLAchlemy realm since I can access the variable entry.id in other places in the function. The error can be reproduced using this repository.

The order to reproduce the error below.

sqlalchemy.exc.MissingGreenlet: greenlet_spawn has not been called; can't call await_only() here. Was IO attempted in an unexpected place?

Run the command below, it is set to give a verbose output.

pytest -vv

The error can be avoided by creating and using a variable before the final db.commit() here.

Output from pip freeze.

pip freeze

aiosqlite==0.19.0
annotated-types==0.5.0
anyio==3.7.1
certifi==2023.7.22
fastapi==0.103.2
greenlet==2.0.2
h11==0.14.0
httpcore==0.18.0
httpx==0.25.0
idna==3.4
iniconfig==2.0.0
packaging==23.2
pluggy==1.3.0
pydantic==1.10.13
pydantic_core==2.10.1
pytest==7.4.2
sniffio==1.3.0
SQLAlchemy==1.4.41
sqlalchemy2-stubs==0.0.2a35
sqlmodel==0.0.8
starlette==0.27.0
typing_extensions==4.8.0

Operating System

macOS

Operating System Details

On a Mac M1

SQLModel Version

0.0.8

Python Version

3.11.5

Additional Context

When I refresh the entry it also works.

    ....
    await db.refresh(entry)  # <--- Refreshing from the database again fixes the issue
    return {"id": entry.id}

This is contained in the additional-refresh branch

[HaleTime]

hi, I have a same issue , have you solved?

[YuriiMotov]

After you call session.commit(), objects liked to this session expire. Next time you try accessing their properties, SQLAlchemy will try refreshing it automatically. But by that time your session is closed and it fails.

So, calling await db.refresh(entry) is right way to do it

Read more: https://sqlmodel.tiangolo.com/tutorial/automatic-id-none-refresh/