On Jan 26, 2012, at 1:21 PM, Kent wrote:
> So, as a typical example of where it seems very natural to use
> "before_update", suppose you need to automatically update the not null
> sequence of a related table. This but to get the sequence you need to loop
> over the parent table's collection.
>
> You want the sequence to be "human friendly" (natural primary key) and also
> you want to be able to sort by sequence guaranteed in order without the
> possibility of a database sequence wrap around. So you want the sequence
> 1,2,3...
>
> This seems extremely well fit for "before_insert", like this:
> ==============================
> parents_table = Table("parents", metadata,
> Column("id", Integer, primary_key=True),
> )
>
> children_table = Table("children", metadata,
> Column("parentid", Integer, ForeignKey('parents.id'),),
> Column("sequence", Integer, primary_key=True),
> )
>
> class Parent(object):
> pass
>
> class Child(object):
> pass
>
> mapper(Parent, parents_table,
> properties={'children': relationship(Child,
> cascade='all,delete-orphan',
> backref='parent')
> })
>
> mapper(Child, children_table)
>
> @event.listens_for(Child, 'before_insert')
> def set_sequence(mapper, connection, instance):
> if instance.sequence is None:
> instance.sequence = (max(c.sequence for c in instance.parent.children)
> or 0) + 1
> ==============================
>
> But this reaches across relationships, so that is actually not desired here,
> is that correct?
that is correct.
>
> For this, you would loop over session.new in before_update, is that how you
> would approach this requirement?
If the value is based on what's already been INSERTed for previous rows, I'd
emit a SQL statement to get at the value. If it's based on some kind of
natural consideration that isn't dependent on the outcome of an INSERT
statement, then you can do the looping above within the before_flush() event
and assign everything at once. Basically you need to "batch" the same way
the UOW itself does.
--
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.
