sqlalchemy (vesrion 1.4 !) - get last inserted id

Question

How I can get the last inserted id? Please, pay attention on the sqlalchemy version, so this question isn't my case.

I tried:

async def foo(db_session: AsyncSession, login):
    r1 = await db_session.execute(statement=models_users.insert().values(login=login))
    r2 = await db_session.flush()
    # r3 = await db_session.fetchval(...)  # Error, no such method for async
    r4 = await db_session.commit()
    print(r2, r4, r1.fechone())  # None, None, None
    print(r1.lastrowid)  # 'AsyncAdapt_asyncpg_cursor' object has no attribute 'lastrowid'


SQLAlchemy        1.4.4
asyncpg           0.22.0

Answer 1

SQLAlchemy's support for the lastrowid depends on the underlying DB-API implementation, so it isn't safe to rely on this attribute. However SQLAlchemy provides an inserted_primary_key attribute on the CursorResult object which ought to be reliable.

The return value is a tuple of the primary key values for the inserted row.

This version of the code in the question:

async def foo(db_session: AsyncSession, login):
    r1 = await db_session.execute(statement=models_users.insert().values(login=login))
    print(r1)
    print('Last pk', r1.inserted_primary_key)
    await db_session.flush()
    await db_session.commit()

Will produce output like this:

<sqlalchemy.engine.cursor.CursorResult object at 0x7f0215966bb0>
Last pk (17,)