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),
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) 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 = %(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.
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.
