mysql - SQLAlchemy: how exchange unique values in one transaction? -

my versions:

$ python python 3.4.0 (default, apr 11 2014, 13:05:11)  [gcc 4.8.2] on linux type "help", "copyright", "credits" or "license" more information. >>> import sqlalchemy >>> sqlalchemy.__version__ '1.0.0' 

let's example sqlite (in production use mysql, not matter here):

from sqlalchemy.ext.declarative import declarative_base sqlalchemy.orm import sessionmaker sqlalchemy import column sqlalchemy import create_engine sqlalchemy import integer, string  base=declarative_base() engine = create_engine('sqlite:///:memory:', echo=true)  class user(base):    __tablename__ = 'user'    id      = column(integer, primary_key=true)    name    = column(string(16))    tech_id = column(integer, unique=true, nullable=true)  base.metadata.create_all(engine)  session = sessionmaker(bind=engine) s = session() 

now add 2 records:

u1=user(name="user1", tech_id=none) u2=user(name="user2", tech_id=10) s.add(u1) s.add(u2) s.commit() 

next try modify them:

u1=s.query(user).filter(user.name=="user1").first() u2=s.query(user).filter(user.name=="user2").first()  u2.tech_id=none u1.tech_id=10 s.commit() 

after commit i've got exception:

<-- cut --> sqlalchemy.exc.integrityerror: (sqlite3.integrityerror) unique constraint failed: user.tech_id [sql: 'update user set tech_id=? user.id = ?'] [parameters: ((10, 1), (none, 2))] 

if this:

u2.tech_id=none s.commit() u1.tech_id=10 s.commit() 

it's right.

is possible requests 1 commit (by 1 transaction)?

you can in way:

#u1=s.query(user).filter(user.name=="user1").first() #u2=s.query(user).filter(user.name=="user2").first()  #u2.tech_id=none #u1.tech_id=10 #s.commit()  s.query(user).filter(user.name=="user2").update(     {user.tech_id: none} ) s.query(user).filter(user.name=="user1").update(     {user.tech_id: 10} )  s.commit() 

the essence of changes perform right sequence of actions. can't create duplicate values unique key (at least in mysql) before committing. can create multiple null values unique column.