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. 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.
