On 05/18/2017 09:20 AM, Jaco Breitenbach wrote:
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.
well you are turning delete into update, so if you also want to mutate
that collection, you need to do that manually. should be easy w/ the
backref, e.g.:
my_soft_deleted_instance.users = []
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
<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>
>
<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>
>
<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
<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
<https://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
<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
<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.
