Automatically populate a field in the association table when using attribute_mapped_collection & association_proxy

[Franck]

Hi folks,

I have a structure where a poll is associated with one vote per user, and each vote is associated with several results.

To manipulate this as easily as possible I use attribute_mapped_collection and association_proxy - please see the attached file or here : http://pastebin.com/CR2PCbCZ

It works great but I have a question. The POLL_VOTES table, i.e. my association table between the polls & the results, has a special field called VOTE_DT. It's correctly populated at insert time but I'd like it to be automatically updated whenever the underlying collection (the results) is updated.

Is it possible ? I documented the expected behaviour in the code snippet (bottom of the code).

Thanks a lot !

Franck

PS my snippet is based on Michael's advice written last year - thanks for that !

http://groups.google.com/group/sqlalchemy/browse_thread/thread/e476915147804941/c94ee256eccc0bc1

[Michael Bayer]

On Apr 11, 2012, at 7:45 AM, Franck wrote:

Hi folks,

I have a structure where a poll is associated with one vote per user, and each vote is associated with several results.

To manipulate this as easily as possible I use attribute_mapped_collection and association_proxy - please see the attached file or here : http://pastebin.com/CR2PCbCZ

It works great but I have a question. The POLL_VOTES table, i.e. my association table between the polls & the results, has a special field called VOTE_DT. It's correctly populated at insert time but I'd like it to be automatically updated whenever the underlying collection (the results) is updated.

Is it possible ? I documented the expected behaviour in the code snippet (bottom of the code).

yeah there's a couple of things here -

class PollVote(Base):

    __tablename__ = "POLL_VOTES"

    vote_dt = Column(DateTime, default=datetime.datetime.now())

    def __init__(self, user=None, result_values=None):

        self.user = user

        self.result_values = result_values

First off when you use "default", you need to pass it a Python function, so above you'd want to say "datetime.now", without actually calling now().

The other thing is, from an ORM perspective the __init__() is the in-Python equivalent to "a new row".  So just set the date there (this time actually calling now()):

class PollVote(Base):

    __tablename__ = "POLL_VOTES"

    vote_dt = Column(DateTime, default=datetime.datetime.now)

    def __init__(self, user=None, result_values=None):

        self.user = user

        self.result_values = result_values

        self.vote_dt = datetime.datetime.now()

[Franck]

Hi Mike,

Thanks for your prompt answer !

Thanks for fixing my "default" field - makes complete sense.

Besides I added the "vote_dt" assignment in the PollVote constructor but it seems that this constructor is called only once, i.e. after the first "result_values_by_user" assignment : poll.result_values_by_user[joe] = set(["A", "B", "C"])

It seems that the 2 further "result_values_by_user" assignments don't trigger the constructor - please see the SQL trace below. What I'm trying to achieve is to automatically update this date not only at row creation time, but also when the underlying collection is modified. 

Am I missing something here ?

Thanks !

Franck

*** Calling the PollVote constructor ***

2012-04-11 18:15:59,668   INSERT INTO "POLL_VOTES" (poll_id, user_id, vote_dt) VALUES (?, ?, ?)

2012-04-11 18:15:59,668   (1, 1, '2012-04-11 18:15:59.666212')

2012-04-11 18:15:59,669   INSERT INTO "POLL_RESULTS" (poll_vote_id, value) VALUES (?, ?)

2012-04-11 18:15:59,669   (1, 'C')

2012-04-11 18:15:59,669   INSERT INTO "POLL_RESULTS" (poll_vote_id, value) VALUES (?, ?)

2012-04-11 18:15:59,669   (1, 'A')

2012-04-11 18:15:59,669   INSERT INTO "POLL_RESULTS" (poll_vote_id, value) VALUES (?, ?)

2012-04-11 18:15:59,669   (1, 'B')

2012-04-11 18:15:59,670   COMMIT

2012-04-11 18:15:59,673   BEGIN (implicit)

2012-04-11 18:15:59,675   SELECT "POLLS".id AS "POLLS_id" 

FROM "POLLS" 

WHERE "POLLS".id = ?

2012-04-11 18:15:59,675   (1,)

2012-04-11 18:15:59,675   SELECT "POLL_VOTES".id AS "POLL_VOTES_id", "POLL_VOTES".poll_id AS "POLL_VOTES_poll_id", "POLL_VOTES".user_id AS "POLL_VOTES_user_id", "POLL_VOTES".vote_dt AS "POLL_VOTES_vote_dt", "USERS_1".id AS "USERS_1_id", "USERS_1".name AS "USERS_1_name", "POLL_RESULTS_1".id AS "POLL_RESULTS_1_id", "POLL_RESULTS_1".poll_vote_id AS "POLL_RESULTS_1_poll_vote_id", "POLL_RESULTS_1".value AS "POLL_RESULTS_1_value" 

FROM "POLL_VOTES" LEFT OUTER JOIN "USERS" AS "USERS_1" ON "USERS_1".id = "POLL_VOTES".user_id LEFT OUTER JOIN "POLL_RESULTS" AS "POLL_RESULTS_1" ON "POLL_VOTES".id = "POLL_RESULTS_1".poll_vote_id 

WHERE ? = "POLL_VOTES".poll_id

2012-04-11 18:15:59,675   (1,)

2012-04-11 18:15:59,678   INSERT INTO "POLL_RESULTS" (poll_vote_id, value) VALUES (?, ?)

2012-04-11 18:15:59,678   (1, 'A')

2012-04-11 18:15:59,678   INSERT INTO "POLL_RESULTS" (poll_vote_id, value) VALUES (?, ?)

2012-04-11 18:15:59,678   (1, 'B')

2012-04-11 18:15:59,679   DELETE FROM "POLL_RESULTS" WHERE "POLL_RESULTS".id = ?

2012-04-11 18:15:59,679   ((1,), (2,), (3,))

2012-04-11 18:15:59,679   COMMIT

2012-04-11 18:15:59,681   BEGIN (implicit)

2012-04-11 18:15:59,683   SELECT "POLLS".id AS "POLLS_id" 

FROM "POLLS" 

WHERE "POLLS".id = ?

2012-04-11 18:15:59,683   (1,)

2012-04-11 18:15:59,683   SELECT "POLL_VOTES".id AS "POLL_VOTES_id", "POLL_VOTES".poll_id AS "POLL_VOTES_poll_id", "POLL_VOTES".user_id AS "POLL_VOTES_user_id", "POLL_VOTES".vote_dt AS "POLL_VOTES_vote_dt", "USERS_1".id AS "USERS_1_id", "USERS_1".name AS "USERS_1_name", "POLL_RESULTS_1".id AS "POLL_RESULTS_1_id", "POLL_RESULTS_1".poll_vote_id AS "POLL_RESULTS_1_poll_vote_id", "POLL_RESULTS_1".value AS "POLL_RESULTS_1_value" 

FROM "POLL_VOTES" LEFT OUTER JOIN "USERS" AS "USERS_1" ON "USERS_1".id = "POLL_VOTES".user_id LEFT OUTER JOIN "POLL_RESULTS" AS "POLL_RESULTS_1" ON "POLL_VOTES".id = "POLL_RESULTS_1".poll_vote_id 

WHERE ? = "POLL_VOTES".poll_id

2012-04-11 18:15:59,683   (1,)

2012-04-11 18:15:59,685   DELETE FROM "POLL_RESULTS" WHERE "POLL_RESULTS".id = ?

2012-04-11 18:15:59,685   ((4,), (5,))

2012-04-11 18:15:59,686   COMMIT

[Michael Bayer]

On Apr 11, 2012, at 12:22 PM, Franck wrote:

> Hi Mike,
>
> Thanks for your prompt answer !
>
> Thanks for fixing my "default" field - makes complete sense.
> Besides I added the "vote_dt" assignment in the PollVote constructor but it seems that this constructor is called only once, i.e. after the first "result_values_by_user" assignment : poll.result_values_by_user[joe] = set(["A", "B", "C"])
>
> It seems that the 2 further "result_values_by_user" assignments don't trigger the constructor - please see the SQL trace below. What I'm trying to achieve is to automatically update this date not only at row creation time, but also when the underlying collection is modified.
>
> Am I missing something here ?

OK so look at the terminiology - "update this date" - you're looking for an UPDATE to the row, so an __init__ does not correspond to that. Sometimes we do on-updates at the SQL level using "onupdate", but that's to respond to something else being updated. In this case, you want the date on POLL_VOTES updated whenever its related POLL_RESULTS entries change as well (right ?) - so to get every kind of change on an attribute we can use attribute events:

from sqlalchemy import event

@event.listens_for(PollVote.results, "append")
@event.listens_for(PollVote.results, "remove")
@event.listens_for(PollVote.results, "set")
def _on_vote(target, value, initiator):
target.vote_dt = datetime.datetime.now()

I think I'm going to beef up the @validates decorator, which is a convenience decorator for the above, to include the ability to intercept deletes.

[Franck]

Right ! Thanks *a lot* Mike, that's what I was trying to achieve.

I think I don't need to listen to the "set" event since _AssociationSet does not support item assignment but it's a detail.

Also, the attribute events don't intercept the creation of an initially empty set (poll.result_values_by_user[joe] = set() ) but it's not an issue either since the default value of vote_dt takes care of this.

Besides, yeah it would be awesome to beef up the @validates decorator cause it would make this kind of code easier to write.

On a side note, when validators are bound to a collection, the validator fires for *every* modified item - do you think it could be possible in the future to have a validator which executes only once per collection modification, even if 50 rows for instance are appended at the same time ?

Thanks again for your support !

Franck