How can I switch two fields of a unique row within one commit using SQLAlchemy?

Question

Assume to have a object with unique name. Now you want to switch the name of two objects:

Here is the layout:

import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class MyObject(Base):
  __tablename__ = 'my_objects'
  id = sa.Column(sa.Integer, primary_key=True)
  name = sa.Column(sa.Text, unique=True)

if __name__ == "__main__":
  engine = sa.create_engine('sqlite:///:memory:', echo=True)
  Session = orm.sessionmaker(bind=engine)
  Base.metadata.create_all(engine)
  session = Session()

And I would like to do this:

a = MyObject(name="Max")
b = MyObject(name="Moritz")
session.add_all([a, b])
session.commit()

# Now: switch names!
tmp = a.name
a.name = b.name
b.name = tmp
session.commit()

This throws an IntegrityError. Is there a way to switch these fields within one commit without this error?

Answer 1

A more pure option would be to delete a, rename b, then re-add a renamed:

session.delete(a)
sqlalchemy.orm.session.make_transient(a)
a.name, b.name = b.name, a.name
session.flush()
session.add(a)
session.commit()

Answer 2

You gave unique=True in the name field so when you are trying to commit it will run the update query it will raise the error.

The situation is when you change the name it will set in memory. But when it will try to run the update query the old record already exist with same name so it will give the IntegrityError.

The way to change name is

a = MyObject(name="Max")
b = MyObject(name="Moritz")
session.add_all([a, b])
session.commit()

# Now: switch names!
atmp = a.name
btemp = b.name

a.name = a.name+btemp # Temp set the any random name
session.commit()

b.name = atemp
a.name = btemp
session.commit() # Run the update query for update the record.