sqlalchemy table.update.returning only returns one value instead of multiple on postgresql

Question

I'm using postgresql and sqlalchemy, here is to update a table and then return multiple values by using returning:

async def update_monitor(id: int, payload: MonitorIn) -> Dict[str, Any]:
    
    utc_now = to_utc(datetime.now())
    query = (
        MONITOR
            .update()
            .where(id == MONITOR.c.id)
            .values(**payload.dict(), updated=utc_now)
            .returning(MONITOR.c.id,
                       MONITOR.c.watchlist_id,
                       MONITOR.c.term,
                       MONITOR.c.match_substring_variations,
                       MONITOR.c.nameserver_exclusions,
                       MONITOR.c.text_exclusions,
                       MONITOR.c.created,
                       MONITOR.c.updated)
    )
    result = await PRIMARY.execute(query=query)
    return {'id': result[0],
            'watchlist_id': result[1],
            'term': result[2],
            'match_substring_variations': result[3],
            'nameserver_exclusions': result[4],
            'text_exclusions': result[5],
            'created': result[6],
            'updated': result[7]}

PRIMARY is a postgresql db.

import databases
PRIMARY = databases.Database(config.DB_URL)

however, the result is an int that corresponds to MONITOR.c.id which is the first returned value, so it appears the returning method only returns one (the first) value instead of multiple values. I actually hope to get multiple values returned. and according to this it should support multiple values returned. what's wrong here?

Answer 1

It looks like the databases library's execute() returns Cursor.lastrowid, and if you want the result row(s) use fetch_one() / fetch_all() / iterate() instead.

Note that this is not the behaviour of SQLAlchemy and returning(), as hinted in the title of this question, but of databases, which simply uses the SQLAlchemy Core DSL as a way to define queries. Under the hood it then compiles them to text, and uses its own connection pool and the async driver in use to emit the statement to the database.