On 04/19/2017 10:36 AM, Adrian wrote:
Here's a MVCE-style example showing the problem I have:
|
fromsqlalchemy import*
fromsqlalchemy.ext.declarative importdeclarative_base
fromsqlalchemy.orm import*
Base=declarative_base()
classType(Base):
__tablename__ ='types'
id =Column(Integer,primary_key=True)
def__repr__(self):
return''.format(self.id)
classBar(Base):
__tablename__ ='bars'
id =Column(Integer,primary_key=True)
deleted =Column(Boolean,nullable=False,default=False)
type_id =Column(ForeignKey('types.id'),nullable=True)
type
=relationship(Type,backref=backref('bars',primaryjoin='(Bar.type_id ==
Type.id) & ~Bar.deleted'))
def__repr__(self):
return''.format(self.id,self.type,self.deleted)
e =create_engine('postgresql:///test',echo=False)
Base.metadata.create_all(e)
s =Session(e,autoflush=False)
t =Type()
b1 =Bar(type=t)
b2 =Bar(type=t,deleted=True)
s.add(t)
s.commit()
s.delete(t)
s.flush()
|
So basically when I'm using the relationship in my code I never want
deleted items to show up. However, for cascading I still need them.
Using serverside cascades could work but if there's a way of doing that
without having to switch to serverside cascades it'd be nicer.
BTW the example doesn't work with SQLite, apparently it automatically
NULLs invalid FKs even without specifying `on delete set null` on the FK.
the general idea of SQLA emitting NULL should work on SQLite as it does
in my example.
the treatment of items in the collection when the parent object is
deleted is dependent on what items are actually loaded into the
collection. So if you set deleted=True on the related item, it won't
be loaded in the collection and it won't get modified.
Here you'd need to build another relationship just for the purposes of
this delete with the simpler primaryjoin condition. Or just use ON
DELETE SET NULL which is a lot simpler, not sure why you don't prefer that.
--
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.
--
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.
