[sqlalchemy] Re: Advice on modeling a many-to-many relationship
Still having a little trouble...here are the relevant mappings:
orm.mapper(Keyphrase, keyphrase_table, properties = {
'message':orm.relation(Message, backref='keyphrase'),
'campaign':orm.relation(Campaign, backref='keyphrase'),
'actions':orm.relation(KeyphraseAction),
})
orm.mapper(Action, action_table)
orm.mapper(KeyphraseAction, keyphrase_action_table, properties={
'action':orm.relation(Action),
'successMessage':orm.relation(Message, primaryjoin =
keyphrase_action_table.c.success_message_id == message_table.c.id),
'failureMessage':orm.relation(Message, primaryjoin =
keyphrase_action_table.c.failure_message_id == message_table.c.id),
'emailContent':orm.relation(EmailContent, primaryjoin =
keyphrase_action_table.c.email_content_id ==
email_content_table.c.id),
})
When I attempt to delete a KeyphraseAction from a Keyphrase instance
as follows:
# kp is a Keyphrase instance
for i, ka in enumerate(kp.actions):
del kp.actions[i]# this doesn't work!
It throws an error:
AssertionError: Dependency rule tried to blank-out primary key
column 'keyphrase_action.keyphrase_id' on instance
But this does work:
assoc = meta.Session.query(m.KeyphraseAction)\
.filter(and_(m.KeyphraseAction.keyphrase_id == kp.id,
m.KeyphraseAction.action_id == kp.actions[i].action.id))\
.one()
meta.Session.delete(assoc)
What am I doing wrong?
On Aug 5, 3:05 pm, Hollister a.hollister.willi...@gmail.com wrote:
That was exactly the conclusion I reached before I read your reply. I
modeled it that way and it seems to work perfectly. Guess I was just
overthinking it.
Thanks for getting back to me, Mike.
On Aug 3, 11:41 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 3, 2009, at 5:21 PM, Hollister wrote:
I have 2 tables which are related to each other through an M:N
relationship (Keyword Action). Additionally, the relationship itself
has attributes, which I have as non-key attributes in a third table
(KeywordAction). I've modeled this dozens of different ways, but have
yet to get exactly what I want from the model.
At the ORM level, I want Keyword to have a property that is a
collection of KeywordAction instances. Each KeywordAction instance
would have a single Action instance property, so I could do things
like this:
---
for ka in keyword.keyword_actions:
if ka.status == 'open':
ka.action.do_something()
keyword.keyword_actions.append(KeywordAction(action, status = 'open'))
---
I've tried using the association_proxy, but I get the feeling that's
not the right tool for this job.
the above example doesn't seem like it would need association proxy, a
simple collection of relation()s, i.e. Keyword.keyword_actions,
KeywordAction.action would do based on the navigation illustrated.
--~--~-~--~~~---~--~~
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: Advice on modeling a many-to-many relationship
Also, the following does nothing at all (does not delete and throws no
errors):
for ka in kp.actions:
del ka
But this:
del kp.actions
Throws the same assertion error.
On Aug 6, 11:01 am, Hollister a.hollister.willi...@gmail.com wrote:
Still having a little trouble...here are the relevant mappings:
orm.mapper(Keyphrase, keyphrase_table, properties = {
'message':orm.relation(Message, backref='keyphrase'),
'campaign':orm.relation(Campaign, backref='keyphrase'),
'actions':orm.relation(KeyphraseAction),
})
orm.mapper(Action, action_table)
orm.mapper(KeyphraseAction, keyphrase_action_table, properties={
'action':orm.relation(Action),
'successMessage':orm.relation(Message, primaryjoin =
keyphrase_action_table.c.success_message_id == message_table.c.id),
'failureMessage':orm.relation(Message, primaryjoin =
keyphrase_action_table.c.failure_message_id == message_table.c.id),
'emailContent':orm.relation(EmailContent, primaryjoin =
keyphrase_action_table.c.email_content_id ==
email_content_table.c.id),
})
When I attempt to delete a KeyphraseAction from a Keyphrase instance
as follows:
# kp is a Keyphrase instance
for i, ka in enumerate(kp.actions):
del kp.actions[i] # this doesn't work!
It throws an error:
AssertionError: Dependency rule tried to blank-out primary key
column 'keyphrase_action.keyphrase_id' on instance
But this does work:
assoc = meta.Session.query(m.KeyphraseAction)\
.filter(and_(m.KeyphraseAction.keyphrase_id == kp.id,
m.KeyphraseAction.action_id == kp.actions[i].action.id))\
.one()
meta.Session.delete(assoc)
What am I doing wrong?
On Aug 5, 3:05 pm, Hollister a.hollister.willi...@gmail.com wrote:
That was exactly the conclusion I reached before I read your reply. I
modeled it that way and it seems to work perfectly. Guess I was just
overthinking it.
Thanks for getting back to me, Mike.
On Aug 3, 11:41 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 3, 2009, at 5:21 PM, Hollister wrote:
I have 2 tables which are related to each other through an M:N
relationship (Keyword Action). Additionally, the relationship itself
has attributes, which I have as non-key attributes in a third table
(KeywordAction). I've modeled this dozens of different ways, but have
yet to get exactly what I want from the model.
At the ORM level, I want Keyword to have a property that is a
collection of KeywordAction instances. Each KeywordAction instance
would have a single Action instance property, so I could do things
like this:
---
for ka in keyword.keyword_actions:
if ka.status == 'open':
ka.action.do_something()
keyword.keyword_actions.append(KeywordAction(action, status = 'open'))
---
I've tried using the association_proxy, but I get the feeling that's
not the right tool for this job.
the above example doesn't seem like it would need association proxy, a
simple collection of relation()s, i.e. Keyword.keyword_actions,
KeywordAction.action would do based on the navigation illustrated.
--~--~-~--~~~---~--~~
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: Advice on modeling a many-to-many relationship
you're probably looking for delete-orphan cascade in this case, if objects
have foreign keys on their primary key columns you'd like them to be
deleted when deassociated with their parent object.
Hollister wrote:
Also, the following does nothing at all (does not delete and throws no
errors):
for ka in kp.actions:
del ka
But this:
del kp.actions
Throws the same assertion error.
On Aug 6, 11:01 am, Hollister a.hollister.willi...@gmail.com wrote:
Still having a little trouble...here are the relevant mappings:
orm.mapper(Keyphrase, keyphrase_table, properties = {
'message':orm.relation(Message, backref='keyphrase'),
'campaign':orm.relation(Campaign, backref='keyphrase'),
'actions':orm.relation(KeyphraseAction),
})
orm.mapper(Action, action_table)
orm.mapper(KeyphraseAction, keyphrase_action_table, properties={
'action':orm.relation(Action),
'successMessage':orm.relation(Message, primaryjoin =
keyphrase_action_table.c.success_message_id == message_table.c.id),
'failureMessage':orm.relation(Message, primaryjoin =
keyphrase_action_table.c.failure_message_id == message_table.c.id),
'emailContent':orm.relation(EmailContent, primaryjoin =
keyphrase_action_table.c.email_content_id ==
email_content_table.c.id),
})
When I attempt to delete a KeyphraseAction from a Keyphrase instance
as follows:
# kp is a Keyphrase instance
for i, ka in enumerate(kp.actions):
del kp.actions[i] # this doesn't work!
It throws an error:
AssertionError: Dependency rule tried to blank-out primary key
column 'keyphrase_action.keyphrase_id' on instance
But this does work:
assoc = meta.Session.query(m.KeyphraseAction)\
.filter(and_(m.KeyphraseAction.keyphrase_id == kp.id,
m.KeyphraseAction.action_id == kp.actions[i].action.id))\
.one()
meta.Session.delete(assoc)
What am I doing wrong?
On Aug 5, 3:05 pm, Hollister a.hollister.willi...@gmail.com wrote:
That was exactly the conclusion I reached before I read your reply. I
modeled it that way and it seems to work perfectly. Guess I was just
overthinking it.
Thanks for getting back to me, Mike.
On Aug 3, 11:41 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Aug 3, 2009, at 5:21 PM, Hollister wrote:
I have 2 tables which are related to each other through an M:N
relationship (Keyword Action). Additionally, the relationship
itself
has attributes, which I have as non-key attributes in a third
table
(KeywordAction). I've modeled this dozens of different ways, but
have
yet to get exactly what I want from the model.
At the ORM level, I want Keyword to have a property that is a
collection of KeywordAction instances. Each KeywordAction instance
would have a single Action instance property, so I could do things
like this:
---
for ka in keyword.keyword_actions:
if ka.status == 'open':
ka.action.do_something()
keyword.keyword_actions.append(KeywordAction(action, status =
'open'))
---
I've tried using the association_proxy, but I get the feeling
that's
not the right tool for this job.
the above example doesn't seem like it would need association proxy,
a
simple collection of relation()s, i.e. Keyword.keyword_actions,
KeywordAction.action would do based on the navigation illustrated.
--~--~-~--~~~---~--~~
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: Advice on modeling a many-to-many relationship
That was exactly the conclusion I reached before I read your reply. I modeled it that way and it seems to work perfectly. Guess I was just overthinking it. Thanks for getting back to me, Mike. On Aug 3, 11:41 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 3, 2009, at 5:21 PM, Hollister wrote: I have 2 tables which are related to each other through an M:N relationship (Keyword Action). Additionally, the relationship itself has attributes, which I have as non-key attributes in a third table (KeywordAction). I've modeled this dozens of different ways, but have yet to get exactly what I want from the model. At the ORM level, I want Keyword to have a property that is a collection of KeywordAction instances. Each KeywordAction instance would have a single Action instance property, so I could do things like this: --- for ka in keyword.keyword_actions: if ka.status == 'open': ka.action.do_something() keyword.keyword_actions.append(KeywordAction(action, status = 'open')) --- I've tried using the association_proxy, but I get the feeling that's not the right tool for this job. the above example doesn't seem like it would need association proxy, a simple collection of relation()s, i.e. Keyword.keyword_actions, KeywordAction.action would do based on the navigation illustrated. --~--~-~--~~~---~--~~ 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: Advice on modeling a many-to-many relationship
On Aug 3, 2009, at 5:21 PM, Hollister wrote: I have 2 tables which are related to each other through an M:N relationship (Keyword Action). Additionally, the relationship itself has attributes, which I have as non-key attributes in a third table (KeywordAction). I've modeled this dozens of different ways, but have yet to get exactly what I want from the model. At the ORM level, I want Keyword to have a property that is a collection of KeywordAction instances. Each KeywordAction instance would have a single Action instance property, so I could do things like this: --- for ka in keyword.keyword_actions: if ka.status == 'open': ka.action.do_something() keyword.keyword_actions.append(KeywordAction(action, status = 'open')) --- I've tried using the association_proxy, but I get the feeling that's not the right tool for this job. the above example doesn't seem like it would need association proxy, a simple collection of relation()s, i.e. Keyword.keyword_actions, KeywordAction.action would do based on the navigation illustrated. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
