mercredi 22 avril 2015

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" for more information.
>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.0.0'

Let's look to the example for sqlite (in production I use mysql, but it does not matter here):

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column
from sqlalchemy import create_engine
from 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 we add two 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 to 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 the exception:

<-- cut -->
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: user.tech_id [SQL: 'UPDATE user SET tech_id=? WHERE user.id = ?'] [parameters: ((10, 1), (None, 2))]

If I do like this:

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

It's all right.

Is it possible to do requests by only one commit (by only one transaction)?

Aucun commentaire:

Enregistrer un commentaire