How can I toggle two unique row fields in a single commit using SQLAlchemy?

Question

Suppose you have an object with a 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()

It causes a IntegrityError. Is there a way to switch these fields inside a single commit without this error?

Answer 1

You gave unique=Truea name to the field, so when you try to commit it, it will launch an update request, it will cause an error.

, , . , , IntegrityError.

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.

Answer 2

a, b, :

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 3

Python allows you to use this syntax (using tuples):

a.name, b.name = b.name, a.name

It is absolutely normal to switch two ordinary arguments this way, but not check in your situation, maybe you can try?