Hi Mike, Your fix worked - the example I gave and our other tests pass now.
Many thanks, Tom On Mon, 19 Oct 2015 at 17:19 Mike Bayer <mike...@zzzcomputing.com> wrote: > > > 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 a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/_ui4mhIUmwM/unsubscribe. > To unsubscribe from this group and all its topics, 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. > -- 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.
