batch insert with returning specified is not working

[Former user]

Postgres supports specifying a "returning" clause on a batch insert but this doesn't appear to be working when done via SQLAlchemy.

I've been testing with 0.7.7 and 0.7.9.

>>> import psycopg2
>>> conn = psycopg2.connect(connection_string)
>>> c = conn.cursor()
>>> c.execute('begin')
>>> c.execute('create temp table tmp(id serial primary key, foo integer)')
>>> c.execute('insert into tmp (foo) values (1),(2),(3),(4),(5) returning id')
>>> c.fetchall()
[(2,), (3,), (4,), (5,)]((1,),)

versus

>>> import sqlalchemy as sa
>>> from sqlalchemy.sql import table, column, bindparam
>>> engine = sa.create_engine('postgresql+psycopg2://' + connection_string')
>>> c = engine.connect()
>>> c.execute('create temp table tmp (id serial primary key, foo integer)')
<sqlalchemy.engine.base.ResultProxy object at 0x10149f050>
>>> tbl = table('tmp', column('id', sa.Integer()), column('foo', sa.Integer()))
>>> c.execute('create temp table tmp(id serial primary key, foo integer)')
>>> result = c.execute(                 
...     tbl.insert().values(            
...         foo=bindparam('_foo')       
...     ).returning(                    
...         tbl.c.id,                   
...     ),                              
...     [i} for i in range(5)]({'_foo':),
... )
[(None,), (None,), (None,), (None,)]((5,),)

[Mike Bayer]

those two examples aren't equivalent. execute(statement, p2, p3, ..) uses DBAPI executemany(), for which psycopg2 does not support result sets, nor does any other DBAPI I've tested.

The equivalent statement here:

from sqlalchemy import create_engine

e = create_engine("postgresql+psycopg2://scott:tiger@localhost/test", echo=True)

with e.begin() as c:
    c.execute('create temp table tmp (id serial primary key, foo integer)')
    result = c.execute("insert into tmp (foo) values (1),(2),(3),(4),(5) returning id")
    print result.fetchall()

gives you the same result:

2012-11-24 20:01:39,364 INFO sqlalchemy.engine.base.Engine insert into tmp (foo) values (1),(2),(3),(4),(5) returning id
2012-11-24 20:01:39,364 INFO sqlalchemy.engine.base.Engine {}
[(2,), (3,), (4,), (5,)]((1,),)

the insert() construct does not currently support inline rendering of multiple values.