Thank you for your answer. I think detaching and doing a session.flush() is a good enough solution for now.
Den onsdag 30 augusti 2017 kl. 16:31:03 UTC+2 skrev Mike Bayer: > > On Wed, Aug 30, 2017 at 7:52 AM, Levon Saldamli <levo...@gmail.com > <javascript:>> wrote: > > I'm trying to have to objects with a one-to-one relationship, e.g. User > and > > Address, with a UniqueConstraint for user.id in the address table > (names > > changed from the real code). But when user.address is updated, the > commit > > fails because the UniqueConstraint is broken, probably because > SqlAlchemy > > inserts the new address into the table before removing the old one. If I > set > > the address to None, commit and then set to a new address, it works > fine. Is > > this a known issue or limitation, and is there a possibility that this > will > > be supported in the future? > > You've hit a classic issue, which is known as the "DELETE before > INSERT" problem: > > > https://bitbucket.org/zzzeek/sqlalchemy/issues/2501/the-delete-before-insert-problem > > <https://www.google.com/url?q=https%3A%2F%2Fbitbucket.org%2Fzzzeek%2Fsqlalchemy%2Fissues%2F2501%2Fthe-delete-before-insert-problem&sa=D&sntz=1&usg=AFQjCNEfVND7dK1_OEq5fU2bpuNfRGodTQ> > > > There is in fact a solution attached to that issue in the form of a > new option that would be added to the mapper() where you provide a set > of columns that need to be maintained as unique. > > The reason it hasn't found its way in yet is because it's not clear > how well received this option would be, what the real demand is for it > as it's a fair it of work, or how well it works in real world use > cases - you can see in the comments that everyone who tries it out > still has some other aspects of their problem it does not solve, and > then for whatever reason seem to move on from it. I've yet to have > someone say hey this solved my problem. > > This is partially because the workaround is pretty simple, you just > detach the first object, do session.flush() to delete it, then attach > the new one. > > Let me know which approach you'd like to proceed with. > > > > > > Full example with the failing commit below. SqlAlchemy version used is > > 1.1.13. The problem is reproducible with both sqlite and postgresql. > > > > from sqlalchemy import create_engine > > from sqlalchemy.ext.declarative import declarative_base > > from sqlalchemy import Column, Integer, String > > from sqlalchemy import ForeignKey > > from sqlalchemy.orm import relationship > > from sqlalchemy import UniqueConstraint > > > > engine = create_engine('sqlite:///:memory:', echo=True) > > > > > > Base = declarative_base() > > > > class User(Base): > > __tablename__ = 'users' > > id = Column(Integer, primary_key=True) > > name = Column(String) > > fullname = Column(String) > > > > address = relationship("Address", uselist=False, > back_populates="user", > > cascade="all, delete-orphan") > > > > def __repr__(self): > > return "<User(name='%s', fullname='%s', id='%s')>" % ( > > self.name, self.fullname, self.id) > > > > > > class Address(Base): > > __tablename__ = 'addresses' > > id = Column(Integer, primary_key=True) > > email_address = Column(String, nullable=False) > > user_id = Column(Integer, ForeignKey('users.id')) > > > > user = relationship("User", back_populates="address") > > __table_args__ = (UniqueConstraint('user_id', > > name='_one_unique_address_per_user_uc'),) > > > > def __repr__(self): > > return "<Address(email_address='%s')>" % self.email_address > > > > > > Base.metadata.create_all(engine) > > > > ed_user = User(name='ed', fullname='Ed Jones') > > > > from sqlalchemy.orm import sessionmaker > > Session = sessionmaker(bind=engine) > > > > > > ed_user.address = Address(email_address='f..._at_bar.com <javascript:>') > > > > > session = Session() > > session.add(ed_user) > > session.commit() > > > > ed_user.address = Address(email_address='new_address_at_bar.com > <javascript:>') > > session.add(ed_user) > > session.commit() > > > > > > > > > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > You received this message because you are subscribed to the Google > Groups > > "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > an > > email to sqlalchemy+...@googlegroups.com <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
