On 10/19/15 11:57 AM, Mike Bayer wrote: > > > On 10/19/15 11:44 AM, Steven Winfield wrote: >> The problem seems to be that the result set from an executemany() call >> in psycopg2 is discarded - here is a good stackoverflow article on this >> subject: >> http://stackoverflow.com/questions/21624844/getting-ids-of-multiple-rows-inserted-in-psycopg2 >> >> In _emit_update_statements in orm/persistence.py, and here >> <http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_10.html?highlight=version_id_col#update-statements-are-now-batched-with-executemany-in-a-flush> >> in the v1.0 migration docs, one of the conditions for using executemany is: >> "The mapping does not use a |version_id_col| >> <http://docs.sqlalchemy.org/en/rel_1_0/orm/mapping_api.html#sqlalchemy.orm.mapper.params.version_id_col>, >> or the backend dialect supports a “sane” rowcount for an executemany() >> operation;" >> >> ...but I'm not sure that is enough - psycopg2 does support sane >> rowcounts, and its dialect's supports_sane_rowcount and >> supports_sane_multi_rowcount are both rightly True (I work with Tom, and >> we're using psycopg2 v2.6 by the way - I know those values are >> predicated on the psycopg2 version). >> >> I'm working on a patch that adds another member to the default dialect >> called "supports_executemany_results" that defaults to True > > I wouldn't do that, no DBAPI has results for executemany. The flag is > not needed. > > This is very likely yet another 1.0 regression caused by > http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_10.html#update-statements-are-now-batched-with-executemany-in-a-flush > and not checking closely enough for the right conditions. I'll add a > new ticket soon.
issue https://bitbucket.org/zzzeek/sqlalchemy/issues/3556/update-executemany-enhancement-breaks which is now resolved. I will try to release 1.0.9 this week; please confirm the fix in the latest rel_1_0 branch fixes this issue for you as well, thanks! > > > > > but is set >> to False in PGDialect_psycopg2 in dialects/postgres/psycopg2.py. This >> can then be used in the determination of allow_multirow (line 646 in >> orm/persistence.py). This will preserve the current behaviour of other >> dialects, but of course there could be others with the same problem. >> >> Steve >> >> On Monday, October 19, 2015 at 4:22:36 PM UTC+1, Tom Flannaghan wrote: >> >> Hi, >> >> We are currently trying to upgrade to sqlalchemy 1.0.8 and have hit >> a problem with executemany() and row versioning. We are using >> postgres and psycopg2 as a backend. We have tables that use server >> side versioning (as discussed at >> >> http://docs.sqlalchemy.org/en/rel_0_9/orm/versioning.html#server-side-version-counters >> >> <http://docs.sqlalchemy.org/en/rel_0_9/orm/versioning.html#server-side-version-counters>), >> and when multiple rows are updated, the UPDATE statements are >> batched together using executemany() in the new version of >> sqlalchemy. Unfortunately, psycopg2 does not support iterating over >> the results of an executemany() query (see >> http://initd.org/psycopg/docs/cursor.html#cursor.executemany >> <http://initd.org/psycopg/docs/cursor.html#cursor.executemany>) so >> the new row versions returned by the update statement cannot be read. >> >> Here is an example that illustrates the problem: >> >> from sqlalchemy import Column, FetchedValue, Integer, String >> from sqlalchemy.ext.declarative import declarative_base >> from sqlalchemy.orm import sessionmaker >> >> Base = declarative_base() >> >> class Port(Base): >> ''' Table for storing ports. ''' >> __tablename__ = "port" >> >> name = Column(String, primary_key=True) >> port = Column(Integer) >> >> xmin = Column(Integer, server_default=FetchedValue(), >> server_onupdate=FetchedValue(), system=True) >> __mapper_args__ = {"version_id_col": xmin, >> "version_id_generator": False} >> >> >> engine = create_engine(...) >> Session = sessionmaker(bind=engine) >> >> session = Session() >> session.bind.echo = True >> >> p1 = Port(name='a', port=1) >> p2 = Port(name='b', port=2) >> session.add(p1) >> session.add(p2) >> session.flush() >> >> # this update fails >> p1.port = 3 >> p2.port = 4 >> session.flush() >> >> session.rollback() >> >> The SQL generated for the update looks like this: >> >> 2015-10-19 14:46:30,289 INFO sqlalchemy.engine.base.Engine UPDATE >> comm.port SET port=%(port)s WHERE comm.port.name >> <http://comm.port.name> = %(comm_port_name)s AND comm.port.xmin = >> %(comm_port_xmin)s RETURNING comm.port.xmin >> 2015-10-19 14:46:30,289 INFO sqlalchemy.engine.base.Engine >> ({'comm_port_xmin': '536394944', 'comm_port_name': 'a', 'port': 3}, >> {'comm_port_xmin': '536394944', 'comm_port_name': 'b', 'port': 4}) >> >> This code raises the following exception: >> >> File user!flannt!untitled6.py, line 52, in : session.flush() >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\session.py, >> line 2004, in flush : self._flush(objects) >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\session.py, >> line 2122, in _flush : transaction.rollback(_capture_exception=True) >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\util\langhelpers.py, >> line 60, in __exit__ : compat.reraise(exc_type, exc_value, exc_tb) >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\session.py, >> line 2086, in _flush : flush_context.execute() >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\unitofwork.py, >> line 373, in execute : rec.execute(self) >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\unitofwork.py, >> line 532, in execute : uow >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\persistence.py, >> line 170, in save_obj : mapper, table, update) >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\orm\persistence.py, >> line 692, in _emit_update_statements : execute(statement, multiparams) >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\base.py, >> line 914, in execute : return meth(self, multiparams, params) >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\sql\elements.py, >> line 323, in _execute_on_connection : return >> connection._execute_clauseelement(self, multiparams, params) >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\base.py, >> line 1010, in _execute_clauseelement : compiled_sql, distilled_params >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\base.py, >> line 1159, in _execute_context : result = >> context._setup_crud_result_proxy() >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\default.py, >> line 832, in _setup_crud_result_proxy : row = result.fetchone() >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\result.py, >> line 1026, in fetchone : self.cursor, self.context) >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\base.py, >> line 1341, in _handle_dbapi_exception : exc_info >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\util\compat.py, >> line 199, in raise_from_cause : reraise(type(exception), exception, >> tb=exc_tb) >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\result.py, >> line 1017, in fetchone : row = self._fetchone_impl() >> File >> >> I:\sw\external\20151014-0\python27\lib\site-packages\sqlalchemy-1.0.8-py2.7-win32.egg\sqlalchemy\engine\result.py, >> line 898, in _fetchone_impl : return self.cursor.fetchone() >> ProgrammingError: (psycopg2.ProgrammingError) no results to fetch >> >> -- >> You received this message because you are subscribed to the Google >> Groups "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send >> an email to sqlalchemy+unsubscr...@googlegroups.com >> <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com>. >> Visit this group at http://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
