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?
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='[email protected]')
session = Session()
session.add(ed_user)
session.commit()
ed_user.address = Address(email_address='[email protected]')
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
