Instance '<Ttz at 0x1142ac9d0>' is not persistent within this Session

Question

I have some FastAPI application code:

engine = create_async_engine(settings.DB.async_dns, **settings.DB.OPTIONS)
Session = async_sessionmaker(bind=engine, expire_on_commit=False)
metadata = MetaData()


async def get_session():
    session = Session()
    try:
        yield session
        await session.commit()
    except Exception as e:
        await session.rollback()
        raise e
    finally:
        await session.close()


async def get_active_ttz(session, ttz_id: int):
    stmt = select(Ttz).where(Ttz.id == ttz_id)
    return await session.scalar(stmt)


async def create_file(session, **values):
    instance = TtzFile(**values)
    session.add(instance)
    await session.flush()
    return instance


@router.post("/ttz/{ttz_id}/file")
async def create_ttz_file(ttz_id: PositiveInt, data: TtzFileCreateDataSchema, session: AsyncSession = Depends(get_session)):
    ttz = await get_active_ttz(session, ttz_id)
    ttz_file = await create_file(session, ttz=ttz, **data.model_dump())
    return ttz_file

and some Pytest test:

@pytest.fixture(autouse=True)
async def session(database, mocker: MockerFixture) -> AsyncGenerator[AsyncSession, None]:
    connection = await engine.connect()
    transaction = await connection.begin()
    session = AsyncSession(bind=connection, expire_on_commit=False, join_transaction_mode="create_savepoint")
    mocker.patch("sqlalchemy.ext.asyncio.session.async_sessionmaker.__call__", return_value=session)
    try:
        yield session
    finally:
        await session.close()
        await transaction.rollback()
        await connection.close()


async def test(session: AsyncSession, client: AsyncClient):
    ttz = Ttz(name="TTZ", start_date=today())
    session.add(ttz)
    await session.commit()

    url = app.url_path_for("create_ttz_file", ttz_id=ttz.id)
    ttz_file_create_payload = fake_ttz_file_create_payload()
    await client.post(url, json=encode_json_payload(ttz_file_create_payload))

    await session.refresh(ttz, attribute_names=["files"])

I faced the next problem. When I make request via client.post get_session dependency works. When it work session is being closed and because of it the code await session.refresh(ttz, attribute_names=["files"]) fails with error:

sqlalchemy.exc.InvalidRequestError: Instance '<Ttz at 0x1142ac9d0>' is not persistent within this Session

I guess to make it work should I somehow add ttz instance into session? If so how can I do this? Or maybe there is another way to fix the problem?

Upd. If to run ttz = await session.merge(ttz) then it works. But why this happens if session is not actually committed if it is bounded to external transaction?

Answer 1

Need to make merge:

ttz = await session.merge(ttz)
await session.refresh(ttz, attribute_names=["files"])