[Sqlalchemy-tickets] Issue #4346: update multiple rows fails when using unique constraint on multiple fields (zzzeek/sqlalchemy)

[Marcel B. <iss...@bi...>]

New issue 4346: update multiple rows fails when using unique constraint on multiple fields
https://bitbucket.org/zzzeek/sqlalchemy/issues/4346/update-multiple-rows-fails-when-using

Marcel Blöcher:

When using a unique constraint on multiple fields, e.g.,
__table_args__ = (UniqueConstraint('position', "whatever", name='_whatever_position_uc'),)
updating multiple rows within a session does not work.

Any idea what is going wrong? Seems to be that the query is not executed at once. So it looks like the query issues integrity checks after each single row update.

Tested with driver sqlite in memory, and also mysql (PyMySQL 0.9.2) 

SQLAlchemy 1.2.12
Python 3.6.6
OS 

minimal working example:

```
#!python

from sqlalchemy import create_engine, Column, Integer, UniqueConstraint
from sqlalchemy.orm import sessionmaker

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

engine = create_engine('sqlite:///:memory:')
SessionM = sessionmaker(bind=engine)
session = SessionM()

class Entry(Base):
    __tablename__ = 'entry'
    
    id = Column(Integer, primary_key=True)
    whatever = Column(Integer, nullable=False)
    position = Column(Integer, nullable=False)

    __table_args__ = (UniqueConstraint('position', 'whatever', name='_whatever_position_uc'),)
    
Base.metadata.create_all(engine)

session.add(Entry(whatever=1, position=1))
session.add(Entry(whatever=1, position=2))
session.add(Entry(whatever=1, position=3))
session.add(Entry(whatever=1, position=4))

session.commit()

# no increase the position of some of the entries by 1
to_update = session.query(Entry).filter(Entry.position >= 2).all()
for entry in to_update:
    entry.position += 1
    
session.commit()


```

Error

```
#!bash

/***/env/bin/python /***/bug-sql.py
Traceback (most recent call last):
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1170, in _execute_context
    context)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 506, in do_executemany
    cursor.executemany(statement, parameters)
sqlite3.IntegrityError: UNIQUE constraint failed: entry.position, entry.whatever

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/***/bug-sql.py", line 34, in <module>
    session.commit()
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 943, in commit
    self.transaction.commit()
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 467, in commit
    self._prepare_impl()
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 447, in _prepare_impl
    self.session.flush()
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2254, in flush
    self._flush(objects)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2380, in _flush
    transaction.rollback(_capture_exception=True)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 249, in reraise
    raise value
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2344, in _flush
    flush_context.execute()
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute
    rec.execute(self)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 556, in execute
    uow
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 177, in save_obj
    mapper, table, update)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 768, in _emit_update_statements
    execute(statement, multiparams)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1170, in _execute_context
    context)
  File "/***/env/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 506, in do_executemany
    cursor.executemany(statement, parameters)
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: entry.position, entry.whatever [SQL: 'UPDATE entry SET position=? WHERE entry.id = ?'] [parameters: ((3, 2), (4, 3), (5, 4))] (Background on this error at: http://sqlalche.me/e/gkpj)
```