MSSQL merge errors with cannot update identity

If I try to merge an item in sqlalchemy using a mssql db it errors saying

item = Item(id=1)
session.merge(item)

File "/usr/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2659, in first
    ret = list(self[0:1])
  File "/usr/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2457, in __getitem__
    return list(res)
  File "/usr/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2761, in __iter__
    return self._execute_and_instances(context)
  File "/usr/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2774, in _execute_and_instances
    close_with_result=True)
  File "/usr/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2765, in _connection_from_session
    **kw)
  File "/usr/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 893, in connection
    execution_options=execution_options)
  File "/usr/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 898, in _connection_for_bind
    engine, execution_options)
  File "/usr/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 313, in _connection_for_bind
    self._assert_active()
  File "/usr/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 214, in _assert_active
    % self._rollback_exception
sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (pyodbc.ProgrammingError) ('42000', "[42000] [FreeTDS][SQL Server]Cannot update identity column 'id'. (8102) (SQLExecDirectW)") [SQL: 'UPDATE item SET id=? WHERE item.id = ?'] [parameters: ('1', 1)]

This is caused by sqlalchemy finding the item and doing an update of the identity/primary key column, which mssql does not like.

Comments (1)