Sqlalchemy insert query does not return results

Question

I have tried to select returning values from insert query:

HOST_INSERT_QUERY = (
    Host.__table__.insert()
        .values(
            name=bindparam("name"),
            source=bindparam("source"),
            type=bindparam("type")
                )
        .returning(Host.__table__.c.id)
)
result = db_conn.execute(HOST_INSERT_QUERY, values)

What I see from db.echo:

info sqlalchemy.engine.base.Engine INSERT INTO hosts (name, source, type) VALUES ( %(name)s, %(source)s, %(type)s) RETURNING hosts.id
info sqlalchemy.engine.base.Engine ({ 'name': 'hhh', 'source': '["import"]', 'type': 'host'},{...})
debug:  Added 2 hosts

It really works. Hosts inserts into database, but I see some troubles in debugger; result (ResultProxy) has:

rowcount = 2 #right!
is_insert = True #right!
return_rows=False #WHYYYY????????

If I try to:

result.fetсhall()

AttributeError: 'ResultProxy' object has no attribute 'fetсhall'

Versions:

• SQLAlchemy 1.3.1
• psycopg-binary 2.7.7

Note! This code only works when inserting a single record. When inserting multiple lines, it is not possible to pull the values ​​out of the RETURNING.

Answer 1

You cannot get results from executemany, which SQLAlchemy uses underneath if you pass a set of more than 1 row to insert. That's why you observe return_rows=False. A workaround is to "inline" the rows using one multi VALUES clause, instead of using bindparams and executemany:

HOST_INSERT_QUERY = (
    Host.__table__.insert()
        .values(values)
        .returning(Host.__table__.c.id)
)

result = db_conn.execute(HOST_INSERT_QUERY)

This executes as a single "multi values" statement and so avoids using executemany, and may return results. Note that the order of the returned IDs may not match that of the inserted rows.