Issue #3088 closed

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

lfiaschi
created an issue

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

Comments (3)

  1. lfiaschi reporter

    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?

  2. Log in to comment