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?

score 5 · Answer 1 · answered Feb 04 '12 at 12:22

5

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()

answered Feb 04 '12 at 12:22

Mu Mind

10,935
4
38
69

score 4 · Accepted Answer · answered Feb 02 '12 at 10:32

4

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.

answered Feb 02 '12 at 10:32

Nilesh

20,521
16
92
148

1

Thank you. Indeed, it seems to be a basic SQL property that is passed directly through SQLAlchemy. Here is a link to a related question: http://stackoverflow.com/questions/644/swap-unique-indexed-column-values-in-database – Philipp der Rautenberg Feb 02 '12 at 13:55
For some reason I also had to set `b.name` to a random value (besides `a.name`) because, in some cases, the second `session.commit()` was raising an IntegrityError. – William Aug 23 '14 at 04:02
The first commit should probably be a flush(), because otherwise you violate ACID semantics (your change is not atomic). – Giovanni Mascellani May 04 '15 at 09:30

score 1 · Answer 3 · answered Feb 02 '12 at 09:37

1

Python allows this syntax(using tuples):

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

It's absolutely okay to switch two normal arguments this way, but not tested in your situation, maybe you can give it a try?

answered Feb 02 '12 at 09:37

Felix Yan

14,841
7
48
61

As long as I don't commit to the database that works fine. The problem seems to be the database. – Philipp der Rautenberg Feb 02 '12 at 09:44
I don't seem to find a equivalent one-statement SQL to do this, too. Maybe this is something SQLAlchemy did not take into account. – Felix Yan Feb 02 '12 at 09:50

Karol Zlot · Answer 4 · 2021-09-25T22:32:58.673

Thanks to Philipp der Rautenberg's comment I found nicer solution which works with PostgreSQL, but not SQLite.

More information on how it works is in this answer and in PostgreSQL docs and in SQLAlchemy docs.

The idea is to first declare unique constraint as deferrable and then set it as DEFERRED either by using it in initially:

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

Base = declarative_base()

class MyObject(Base):
    __tablename__ = 'my_object'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.Text)
    name_unique_constraint = sa.UniqueConstraint(name, deferrable=True, initially='DEFERRED')


if __name__ == "__main__":
    engine = sa.create_engine('postgresql+psycopg2://user:password@hostname/database_name', echo=True)
    Session = orm.sessionmaker(bind=engine)
    Base.metadata.create_all(engine)
    session = Session()

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

    # Now: switch names!
    a.name, b.name = b.name, a.name

    session.add_all([a, b]) # this line seems to be necessary to raise exception if swap woudn't be possible
    session.commit()

or with explicit sql statement:

db.execute('SET CONSTRAINTS ALL DEFERRED')

or

db.execute('SET CONSTRAINTS uq_my_object_name DEFERRED')

but remember when using SET CONSTRAINTS that:

This command only alters the behavior of constraints within the current transaction.

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

4 Answers4