[sqlalchemy] ORM Event
Hi,
I found some weird case on using the mapper event, i setup the mapper
to firing after_insert and after_delete event. However i find some
issue, at least weird behaviour:
* after_insert and after_delete triggered only once on each committed
transaction.
dt_1 = Detail(1, 1, 'This is detail')
dt_2 = Detail(1, 2, 'This is detail')
session.add(dt_1)
session.add(dt_2)
session.commit() # this only fire before insert only once
* no additional data manipulation inside each event could be
persisted.
def model_after_insert(mapper, connection, target):
if target.__tablename__ == 'detail':
master = session.query(klass).filter_by(id = target.master).first()
if master:
master.total = master.total + 1
session.add(master) # nothing would be persisted on this
Full example attached below, is there anything i had missed?
--
Salam,
-Jaimy Azle
code snippet -
from sqlalchemy import create_engine, event
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker, mapper
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
klass = None
def model_after_insert(mapper, connection, target):
if target.__tablename__ == 'detail':
master = session.query(klass).filter_by(id = target.master).first()
if master:
master.total = master.total + 1
session.add(master)
print('after insert', master.total)
def model_after_delete(mapper, connection, target):
if target.__tablename__ == 'detail':
master = session.query(klass).filter_by(id = target.master).first()
if master:
master.total = master.total - 1
session.add(master)
print('after delete', master.total)
event.listen(mapper, 'after_insert', model_after_insert)
event.listen(mapper, 'after_delete', model_after_delete)
class Master(Base):
__tablename__ = 'master'
id = Column(Integer, primary_key=True)
name = Column(String)
total = Column(Integer)
def __init__(self, name):
self.name = name
self.total = 0
class Detail(Base):
__tablename__ = 'detail'
id = Column(Integer, primary_key=True)
master = Column(Integer, primary_key=True)
name = Column(String)
def __init__(self, id, master, name):
self.id = id
self.name = name
self.master = master
klass = Master
Base.metadata.create_all(engine)
session = Session()
master = Master('hello world')
session.add(master)
session.commit()
dt_1 = Detail(1, 1, 'This is detail')
dt_2 = Detail(1, 2, 'This is detail')
session.add(dt_1)
session.add(dt_2)
session.commit()
master = session.query(Master).filter_by(id = 1).first()
print('total ', master.total)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] ORM Event
The Session is not usable in the way you're using it inside of after_insert and
after_delete - in particular the modifications made to the state of the object
that was just inserted will be discarded, and the add() will have no effect as
the flush plan cannot be changed in these events.
To modify the Session's flush plan within a flush event, use the before_flush()
session event.
There are guidelines to this effect on before_insert()
http://www.sqlalchemy.org/docs/orm/events.html?highlight=after_insert#sqlalchemy.orm.events.MapperEvents.before_insert
but I'll need to add similar language to after_() also. look for that soon.
On Aug 25, 2011, at 6:45 AM, Jaimy Azle wrote:
Hi,
I found some weird case on using the mapper event, i setup the mapper
to firing after_insert and after_delete event. However i find some
issue, at least weird behaviour:
* after_insert and after_delete triggered only once on each committed
transaction.
dt_1 = Detail(1, 1, 'This is detail')
dt_2 = Detail(1, 2, 'This is detail')
session.add(dt_1)
session.add(dt_2)
session.commit() # this only fire before insert only once
* no additional data manipulation inside each event could be
persisted.
def model_after_insert(mapper, connection, target):
if target.__tablename__ == 'detail':
master = session.query(klass).filter_by(id = target.master).first()
if master:
master.total = master.total + 1
session.add(master) # nothing would be persisted on this
Full example attached below, is there anything i had missed?
--
Salam,
-Jaimy Azle
code snippet -
from sqlalchemy import create_engine, event
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker, mapper
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
klass = None
def model_after_insert(mapper, connection, target):
if target.__tablename__ == 'detail':
master = session.query(klass).filter_by(id = target.master).first()
if master:
master.total = master.total + 1
session.add(master)
print('after insert', master.total)
def model_after_delete(mapper, connection, target):
if target.__tablename__ == 'detail':
master = session.query(klass).filter_by(id = target.master).first()
if master:
master.total = master.total - 1
session.add(master)
print('after delete', master.total)
event.listen(mapper, 'after_insert', model_after_insert)
event.listen(mapper, 'after_delete', model_after_delete)
class Master(Base):
__tablename__ = 'master'
id = Column(Integer, primary_key=True)
name = Column(String)
total = Column(Integer)
def __init__(self, name):
self.name = name
self.total = 0
class Detail(Base):
__tablename__ = 'detail'
id = Column(Integer, primary_key=True)
master = Column(Integer, primary_key=True)
name = Column(String)
def __init__(self, id, master, name):
self.id = id
self.name = name
self.master = master
klass = Master
Base.metadata.create_all(engine)
session = Session()
master = Master('hello world')
session.add(master)
session.commit()
dt_1 = Detail(1, 1, 'This is detail')
dt_2 = Detail(1, 2, 'This is detail')
session.add(dt_1)
session.add(dt_2)
session.commit()
master = session.query(Master).filter_by(id = 1).first()
print('total ', master.total)
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] ORM Event
On Thursday, August 25, 2011, 7:36:53 PM, Michael Bayer wrote: Thanks Michael, The Session is not usable in the way you're using it inside of after_insert and after_delete - in particular the modifications made to the state of the object that was just inserted will be discarded, and the add() will have no effect as the flush plan cannot be changed in these events. Still, there is an odd issue here, those mapper event only triggered on first instance only. dt_1 = Detail(1, 1, 'This is detail') dt_2 = Detail(1, 2, 'This is detail') session.add(dt_1) session.add(dt_2) session.commit() # only the first instance call after_insert # event, not all To modify the Session's flush plan within a flush event, use the before_flush() session event. How to retrieve affected instances with their state (being inserted, updated, or deleted) that belong to a flush operation? for this case I need to validate each instance after they were inserted or before they were deleted from a persistence storage and adjust some rows from another table. -- Salam, -Jaimy Azle “+1 for stating fact: Perl is dead. Please don't bring it back” – Matt Joiner “-1 for spreading FUD about perl. It's absolutely not dead.” – Daenyth “+1 + -1 = 0, then, is perl a zombie?” – joaquin -- http://stackoverflow.com/questions/3384385/python-3-2-gil-good-bad -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] attempting to override __sa_instrumentation_manager__
Assume I have a class hierarchy: class Base(object): __sa_instrumentation_manager__ = AttrManager class Order(Base): __sa_instrumentation_manager__ = OrderAttrManager This causes TypeError: multiple instrumentation implementations specified in Order inheritance hierarchy: I don't understand why or how I can go about overriding the AttributeManager for a specific class. I want 95% of the classes to use AttrManager, but I want a specific one for OrderAttrManager. What am I doing wrong? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: attempting to override __sa_instrumentation_manager__
We are still running 0.6.4 so I think that was the only way to implement events (set/append/remove), correct? On Aug 25, 12:38 pm, Michael Bayer mike...@zzzcomputing.com wrote: ho boy. Why are you playing with instrumentation ? That's a really obscure feature we created literally for one product. I'd love to blow it away. Anyway, if that's the error its giving you, then that's definitely one of its limitations :). On Aug 25, 2011, at 11:10 AM, Kent wrote: Assume I have a class hierarchy: class Base(object): __sa_instrumentation_manager__ = AttrManager class Order(Base): __sa_instrumentation_manager__ = OrderAttrManager This causes TypeError: multiple instrumentation implementations specified in Order inheritance hierarchy: I don't understand why or how I can go about overriding the AttributeManager for a specific class. I want 95% of the classes to use AttrManager, but I want a specific one for OrderAttrManager. What am I doing wrong? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] ORM Event
On Aug 25, 2011, at 10:22 AM, Jaimy Azle wrote:
On Thursday, August 25, 2011, 7:36:53 PM, Michael Bayer wrote:
Thanks Michael,
The Session is not usable in the way you're using it inside of
after_insert and after_delete - in particular the modifications made
to the state of the object that was just inserted will be discarded,
and the add() will have no effect as the flush plan cannot be
changed in these events.
Still, there is an odd issue here, those mapper event only triggered
on first instance only.
I would ask why you believe that's true - from what I can tell, you're using
the Session.add() or something as a means to test that the event is triggered,
which as I mentioned will have side effects.
A proper test as below indicates two inserts for Detail, one insert for Master,
as expected:
import collections
total_insert_calls = collections.defaultdict(int)
total_delete_calls = collections.defaultdict(int)
def model_after_insert(mapper, connection, target):
total_insert_calls[mapper.class_] += 1
def model_after_delete(mapper, connection, target):
total_delete_calls[mapper.class_] += 1
...
assert total_insert_calls[Master] == 1
assert total_insert_calls[Detail] == 2
assert not total_delete_calls
script is attached.
dt_1 = Detail(1, 1, 'This is detail')
dt_2 = Detail(1, 2, 'This is detail')
session.add(dt_1)
session.add(dt_2)
session.commit() # only the first instance call after_insert
# event, not all
To modify the Session's flush plan within a flush event, use the
before_flush() session event.
How to retrieve affected instances with their state (being inserted,
updated, or deleted) that belong to a flush operation? for this case I
need to validate each instance after they were inserted or before they
were deleted from a persistence storage and adjust some rows from
another table.
Within before_flush() you work with the session.new, session.dirty, and
session.deleted collections, as well as with attributes.get_history() to look
at individual attribute changes. Any of the versioning examples on the wiki
at http://www.sqlalchemy.org/trac/wiki/UsageRecipes illustrate these techniques.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
from sqlalchemy import create_engine, event
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker, mapper
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
import collections
total_insert_calls = collections.defaultdict(int)
total_delete_calls = collections.defaultdict(int)
def model_after_insert(mapper, connection, target):
total_insert_calls[mapper.class_] += 1
def model_after_delete(mapper, connection, target):
total_delete_calls[mapper.class_] += 1
event.listen(mapper, 'after_insert', model_after_insert)
event.listen(mapper, 'after_delete', model_after_delete)
class Master(Base):
__tablename__ = 'master'
id = Column(Integer, primary_key=True)
name = Column(String)
total = Column(Integer)
def __init__(self, name):
self.name = name
self.total = 0
class Detail(Base):
__tablename__ = 'detail'
id = Column(Integer, primary_key=True)
master = Column(Integer, primary_key=True)
name = Column(String)
def __init__(self, id, master, name):
self.id = id
self.name = name
self.master = master
klass = Master
Base.metadata.create_all(engine)
session = Session()
master = Master('hello world')
session.add(master)
session.commit()
dt_1 = Detail(1, 1, 'This is detail')
dt_2 = Detail(1, 2, 'This is detail')
session.add(dt_1)
session.add(dt_2)
session.commit()
assert total_insert_calls[Master] == 1
assert total_insert_calls[Detail] == 2
assert not total_delete_calls
master = session.query(Master).filter_by(id = 1).first()
print('total ', master.total)
Re: [sqlalchemy] Re: attempting to override __sa_instrumentation_manager__
yikes! are my docs that bad ? attribute events, right here: http://www.sqlalchemy.org/docs/06/orm/interfaces.html#attribute-events On Aug 25, 2011, at 12:41 PM, Kent wrote: We are still running 0.6.4 so I think that was the only way to implement events (set/append/remove), correct? On Aug 25, 12:38 pm, Michael Bayer mike...@zzzcomputing.com wrote: ho boy. Why are you playing with instrumentation ? That's a really obscure feature we created literally for one product. I'd love to blow it away. Anyway, if that's the error its giving you, then that's definitely one of its limitations :). On Aug 25, 2011, at 11:10 AM, Kent wrote: Assume I have a class hierarchy: class Base(object): __sa_instrumentation_manager__ = AttrManager class Order(Base): __sa_instrumentation_manager__ = OrderAttrManager This causes TypeError: multiple instrumentation implementations specified in Order inheritance hierarchy: I don't understand why or how I can go about overriding the AttributeManager for a specific class. I want 95% of the classes to use AttrManager, but I want a specific one for OrderAttrManager. What am I doing wrong? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] foreign_keys changing cascade behavior for relationships? users going away.
Hi there. I have an application that has three different databases in MySQL (InnoDB). They are separated into distinct databases because sometimes I need to reload a database from scratch without impacting the other databases. For this reason, there are no ForeignKey() relationships defined at table creation time between these three databases, so I can wipe and reload an individual database at will. There are still relationships between the databases, from a SQLAlchemy perspective. Because I don't use ForeignKey(), I need to specify foreign_keys in my relationship, as follows: 'user' : relationship(User, foreign_keys=[User.__table__.c.email], primaryjoin=cls.__table__.c.user_email == User.__table__.c.email), When I have this relationship in my AllocatedHardware object, and I delete an AllocatedHardware record, this has the unfortunate site-effect of deleting the user account from the users table. :( When I leave the foreign_keys parameter out, then SQLAlchemy can't determine the join condition. The behavior I want is for the AllocatedHardware record to go away when I delete it, without impacting my user accounts. Can this be achieved with tweaking the cascade behavior? It seems like an unexpected side-effect that specifying foreign_keys will result in cascading deletes in tables that do not have any db-level foreign key relationships. Thanks and Regards, Daniel -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] ORM Event
On Thursday, August 25, 2011, 11:44:48 PM, Michael Bayer wrote: I would ask why you believe that's true - from what I can tell, you're using the Session.add() or something as a means to test that the event is triggered, which as I mentioned will have side effects. A proper test as below indicates two inserts for Detail, one insert for Master, as expected: Ups i believe it was my bad, combining two cases in one test. Anyway, I got it, thanks.. :) -- Salam, -Jaimy Azle “+1 for stating fact: Perl is dead. Please don't bring it back” – Matt Joiner “-1 for spreading FUD about perl. It's absolutely not dead.” – Daenyth “+1 + -1 = 0, then, is perl a zombie?” – joaquin -- http://stackoverflow.com/questions/3384385/python-3-2-gil-good-bad -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: attempting to override __sa_instrumentation_manager__
Obviously not; thanks for pointing me, sorry I couldn't find it myself. On Aug 25, 12:45 pm, Michael Bayer mike...@zzzcomputing.com wrote: yikes! are my docs that bad ? attribute events, right here: http://www.sqlalchemy.org/docs/06/orm/interfaces.html#attribute-events On Aug 25, 2011, at 12:41 PM, Kent wrote: We are still running 0.6.4 so I think that was the only way to implement events (set/append/remove), correct? On Aug 25, 12:38 pm, Michael Bayer mike...@zzzcomputing.com wrote: ho boy. Why are you playing with instrumentation ? That's a really obscure feature we created literally for one product. I'd love to blow it away. Anyway, if that's the error its giving you, then that's definitely one of its limitations :). On Aug 25, 2011, at 11:10 AM, Kent wrote: Assume I have a class hierarchy: class Base(object): __sa_instrumentation_manager__ = AttrManager class Order(Base): __sa_instrumentation_manager__ = OrderAttrManager This causes TypeError: multiple instrumentation implementations specified in Order inheritance hierarchy: I don't understand why or how I can go about overriding the AttributeManager for a specific class. I want 95% of the classes to use AttrManager, but I want a specific one for OrderAttrManager. What am I doing wrong? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Cascade in many-to-many relationships: when orphans are not orphans
Thanks for the help with my query the other day - as ever response was swift and bang on. I'm now trying to set up another m:n relationship in ORM correctly. Pseudocode: parentA.children.append(child1) parentA.children.append(child2) parentB.children.append(child2) session.delete(parentA) At this stage, I would like child1 to be deleted and child2 to survive. However, if I use (cascade = all), then both children will be deleted when ParentA is. I hoped that delete-orphan would be applicable in this situation, but that requires that single-parent be True, which I understand it cannot for a true many-to-many. So I guess what I am asking is - is it possible for child objects which have still have remaining parents to survive, while deleting those with no parents left? Thanks, Ben -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Cascade in many-to-many relationships: when orphans are not orphans
On Aug 25, 2011, at 7:06 PM, Benjamin Sims wrote: Thanks for the help with my query the other day - as ever response was swift and bang on. I'm now trying to set up another m:n relationship in ORM correctly. Pseudocode: parentA.children.append(child1) parentA.children.append(child2) parentB.children.append(child2) session.delete(parentA) At this stage, I would like child1 to be deleted and child2 to survive. However, if I use (cascade = all), then both children will be deleted when ParentA is. I hoped that delete-orphan would be applicable in this situation, but that requires that single-parent be True, which I understand it cannot for a true many-to-many. So I guess what I am asking is - is it possible for child objects which have still have remaining parents to survive, while deleting those with no parents left? That's not something supported by delete, delete-orphan cascade and its why the single_parent=True flag is required - so that users aren't misled into thinking it can work that way. You'd need to roll this using attribute events most likely. The potential expense is that you may have to emit SQL in order to load the full collection of parents for each child in order to detect the orphan condition (which is why SQLA doesn't support this automatically, it would be extremely inefficient implemented generically). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
