Sqlalchemy: possible bug when inserting rows and returning the primary key

[Luca Fiaschi]

Hi, I noticed something strange when inserting multiple rows and returning the primary keys. If I add use the parameter values in the isert query I get the expected behaviour, hower when passing the values to the cursor, nothing is returned.

Is it possibly a bug?

My sqlachemy version is 0.9.4, below how to reproduce the error:

from sqlalchemy import create_engine, Column, Unicode, DateTime
from sqlalchemy import Float, ForeignKey, BigInteger, Enum, Integer, Boolean
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base


cmd= 'postgresql+psycopg2://lfiaschi:mypswd@localhost:5432/test_db'

Base = declarative_base()

class Test(Base):
    __tablename__ = 'test'
    db_id = Column(BigInteger, primary_key=True, autoincrement=True)
    name = Column(Unicode(255), nullable=False)


engine=create_engine(cmd, encoding='utf8', implicit_returning=False)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)


N=10
rows = [{'name': str(i)} for i in range(N)]

#If the values are specified at this point the primary keys seem correctly returned
ins_statement1 = Test.__table__.insert(returning=[Test.__table__.c.db_id]).values(rows)

connection = engine.connect()


res1 = connection.execute(ins_statement1)
correct_ids = res1.fetchall()
print correct_ids
assert len(correct_ids) == N


ins_statement1 = Test.__table__.insert(returning=[Test.__table__.c.db_id])

#If we specify the values here
res2 = connection.execute(ins_statement1,rows)

res2.fetchall() #BOOOM!

The error is

#!

Traceback (most recent call last):
  File "bug_sqlalchemy.py", line 42, in <module>
    res2.fetchall() #BOOOM!
  File "/home/lfiaschi/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 788, in fetchall
    self.cursor, self.context)
  File "/home/lfiaschi/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1108, in _handle_dbapi_exception
    exc_info
  File "/home/lfiaschi/anaconda/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 185, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/lfiaschi/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 782, in fetchall
    l = self.process_rows(self._fetchall_impl())
  File "/home/lfiaschi/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 749, in _fetchall_impl
    return self.cursor.fetchall()
sqlalchemy.exc.ProgrammingError: (ProgrammingError) no results to fetch None None

[Mike Bayer]

the psycopg2 DBAPI doesn't support RETURNING with executemany() (nor does any DBAPI). see http://stackoverflow.com/questions/21624844/getting-ids-of-multiple-rows-inserted-in-psycopg2 and http://initd.org/psycopg/docs/cursor.html#cursor.executemany ("any result set returned by the query is discarded.")

[Luca Fiaschi]

Thanks Mike. Could you shortly point me out the difference between the first way of inserting ant the second way... Why the first one works? Does't it use the psycopg2 executemany? Which is more appropriate in the general case?

[Mike Bayer]

the first one works because SQLAlchemy does not have a parameter list, and as there are one or zero parameters sent it uses cursor.execute(). The second one is being passed a list of parameters greater than length one, so it uses cursor.executemany(). executemany does not support usage of the fetchXXX() methods.