Hi Mike,
Thank you very much. I've managed to get it working after a fashion.
Unfortunately this technique seems to interfere with the automatic
clean-up of the secondary (mapping) table in the case of a many-to-many
relationship. In the example below I have a contact_group table and user
table and a many-to-many relationship set up via a contact_group_user_map
table. Since I've used backref in defining the relationship, I expect the
row in contact_group_user_map to automatically be deleted when I do
session.delete(userObj). This works perfectly as long as the before_flush
handler is disabled. If I intercept the delete of userObj and turn it into
an update, the delete from contact_group_user_map no longer happens. I've
noticed that the list of objects in session.deleted which is passed to the
before_flush handler does not include the contact_group_user_map entity, so
I guess the determination to delete the row from contact_group_user_map
takes place after the before_flush handler returns. Do you have any advice
on resolving this issue?
from sqlalchemy import create_engine, event, BigInteger, Column, DateTime,
Float, ForeignKey, Index, Integer, String, text
from sqlalchemy.orm import attributes, relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_repr import PrettyRepresentableBase
Base = declarative_base(cls=PrettyRepresentableBase)
metadata = Base.metadata
engine = None
Session = None
def handle__before_flush(session, flush_context, instances):
print("handle__before_flush()")
for instance in session.deleted:
print(instance)
if not attributes.instance_state(instance).has_identity:
continue
if not hasattr(instance, '_deleted'):
continue
# instance._deleted = True
# session.add(instance)
def getSession(conn):
global engine, Session
if engine is None or Session is None:
engine = create_engine(conn, echo=True)
Session = sessionmaker(bind=engine, query_cls=SoftDeleteQuery)
event.listen(Session, 'before_flush', handle__before_flush)
return Session()
class ContactGroup(Base):
__tablename__ = 'contact_group'
id = Column(BigInteger, primary_key=True)
_deleted = Column(Integer, nullable=False, server_default=text("'0'"))
name = Column(String(200), nullable=False)
description = Column(String(1000))
users = relationship("User", secondary="contact_group_user_map",
backref="contact_groups")
class ContactGroupUserMap(Base):
__tablename__ = 'contact_group_user_map'
id = Column(BigInteger, primary_key=True)
contact_group_id = Column(ForeignKey(u'contact_group.id'),
nullable=False, index=True)
user_id = Column(ForeignKey(u'user.id'), nullable=False, index=True)
contact_group = relationship(u'ContactGroup')
user = relationship(u'User')
class User(Base):
__tablename__ = 'user'
id = Column(BigInteger, primary_key=True)
_deleted = Column(Integer, nullable=False, server_default=text("'0'"))
username = Column(String(200), nullable=False, unique=True)
password = Column(String(1000))
Thank you and kind regards,
Jaco
On Wednesday, 17 May 2017 23:30:25 UTC+1, Mike Bayer wrote:
>
>
>
> On 05/17/2017 05:25 PM, Jaco Breitenbach wrote:
> > Thank you, Jonathan, that wiki example has helped a lot. I've extended
> > it a bit to also add the extra condition to joined tables.
> >
> > I want to take it one step further now and automatically convert
> > session.delete()s into update statements (set is_deleted = true) instead
> > of delete statements. I've experimented with a custom SessionExtension,
>
> don't use "Extensions", use the event system:
>
>
> http://docs.sqlalchemy.org/en/rel_1_1/orm/events.html?highlight=before_flush#sqlalchemy.orm.events.SessionEvents.before_flush
>
>
>
>
> > session.deleted.pop() # Remove the instance from the
> > list of entities to be deleted
>
> ouch...that's not going to do anything. everything in Python is
> mutable and it's kind of hard to make every collection everywhere that
> only is a view of things immutable (though this is custom collection,
> could be made immutable with a flag or something). To undo the delete,
> add the object back, session.add(object).
>
>
>
>
>
>
>
> >
> >
> > Thank you and kind regards,
> > Jaco
> >
> >
> > On Tuesday, 16 May 2017 17:20:12 UTC+1, Jonathan Vanasco wrote:
> >
> > The FAQ
> >
> http://docs.sqlalchemy.org/en/latest/faq/sessions.html#how-do-i-make-a-query-that-always-adds-a-certain-filter-to-every-query
>
> > <
> http://docs.sqlalchemy.org/en/latest/faq/sessions.html#how-do-i-make-a-query-that-always-adds-a-certain-filter-to-every-query>
>
>
> >
> > points to an example in the wiki:
> >
> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PreFilteredQuery
> > <
> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PreFilteredQuery>
>
> >
> >
> > --
> > 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:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto: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.
