On 11/05/2016 02:45 PM, Yegor Roganov wrote:
Hello,
For 1-to-1 model relationships, when relationship value is updated, the
default SQLAlchemy behavior is to set the foreign key column of previous
relationship value to NULL.
I would like to configure SQLAlchemy to not set the foreign key column
to NULL, but instead to set a `deleted` flag to true. This will allow me
to have historical data.
Having read docs on cascade, I have not found an option that can
configure this. Is this doable?
Here is a test I want passing
thanks for making this easy w/ the test. we're asking here for a
pretty limited event, that is when user_id is UPDATED to be null, set it
back to user_id and set deleted=True, a before_update event works very
simply:
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
address = relationship(
"Address",
primaryjoin=lambda: and_(Address.user_id == User.id,
~Address.deleted),
uselist=False,
back_populates="user")
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email = Column(String, nullable=False)
deleted = Column(Boolean, nullable=False, default=False)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship("User")
from sqlalchemy import event
from sqlalchemy.orm import object_session
@event.listens_for(Address, "before_update")
def _update_address(mapper, connection, target):
if target.user_id is None:
object_session(target).expire(target, ['user_id'])
target.deleted = True
I tried getting this work in a different way using before_flush(), but
currently there's no way of disabling that "user_id=None" set, even
though there is a feature which is supposed to do this
(passive_deletes='all') so that's a bug.
|
from sqlalchemy import Column, Integer, String, ForeignKey, Boolean
from sqlalchemy import create_engine, and_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(bind=engine)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
_address = relationship("Address",
primaryjoin=lambda: and_(Address.user_id ==
User.id,
~Address.deleted),
uselist=False,
back_populates="user")
@property
def address(self):
return self._address
@address.setter
def address(self, new_address):
if self._address:
self._address.deleted = True
self._address = new_address
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email = Column(String, nullable=False)
deleted = Column(Boolean, nullable=False, default=False)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship("User")
def test():
Base.metadata.create_all(engine)
sess = Session()
a1 = Address(email='foo')
u = User(id=1, address=a1)
sess.add_all([u, a1])
sess.commit()
u.address = Address(email='bar')
sess.commit()
assert a1.user_id == 1, a1.user_id
test()
|
Thanks in advance
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
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.
