[sqlalchemy] Re: strange problem with relation(..)
On Wed, Jul 29, 2009 at 9:54 PM, Michael Bayer wrote: > > > On Jul 29, 2009, at 10:48 PM, Jon Nelson wrote: > >>> sure just use default=my_callable(ctx). the ctx contains the bind >>> parameters for the current row. you could also just use a >>> MapperExtension. >> >> I am not sure how to apply a default when not defining the columns - I >> am sadly using database reflection. >> >> I tried using MapperExtension (using before_insert) but nodeid is >> still None at that point that before_insert is called. > > oh this is MySQL right ? yeah you're out of luck unless you manually > generate the ID beforehand. There is no way, well *maybe* a trigger > can do it, to insert such a row on MySQL using its normal > autoincrement feature and that has nothing to do with SQLAlchemy. see > if triggers can do it. otherwise you need to change your schema. > I've used a schema like this many times and parent_id simply must be > nullable. PostgreSQL. For testing purposes, I've altered the schema so it can be nullable and I'm able to go forward for now. Thanks for the help! -- Jon --~--~-~--~~~---~--~~ 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: strange problem with relation(..)
On Jul 29, 2009, at 10:48 PM, Jon Nelson wrote: >> sure just use default=my_callable(ctx). the ctx contains the bind >> parameters for the current row. you could also just use a >> MapperExtension. > > I am not sure how to apply a default when not defining the columns - I > am sadly using database reflection. > > I tried using MapperExtension (using before_insert) but nodeid is > still None at that point that before_insert is called. oh this is MySQL right ? yeah you're out of luck unless you manually generate the ID beforehand. There is no way, well *maybe* a trigger can do it, to insert such a row on MySQL using its normal autoincrement feature and that has nothing to do with SQLAlchemy. see if triggers can do it. otherwise you need to change your schema. I've used a schema like this many times and parent_id simply must be nullable. --~--~-~--~~~---~--~~ 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: strange problem with relation(..)
On Wed, Jul 29, 2009 at 9:16 PM, Michael Bayer wrote: > > On Jul 29, 2009, at 10:10 PM, BigJon wrote: > >> On Jul 21, 2:53 pm, "Michael Bayer" wrote: >>> >>> Jon Nelson wrote: >>> The parent_id is NOT NULL and has no default. Doing something like this doesn't seem to help, with or without thepost_update=True (or False) configured on the mapper. >>> >>> the NOT NULL makes it impossible, unless you execute a sequence yourself >>> and populate both columns before flushing. >>> >>> >>> node = Node() node.parent = node sess.add(node) sess.flush() >>> The parentid attribute is always None. Is there an easy way to fix this? >>> >>> post_update=True on the parent relation() will run a second UPDATE >>> statement to populate parent_id. >> >> Is there any way to make sure of ColumnDefault, DefaultClause, >> DefaultGenerator, FetchedValue, or PassiveDefault to automatically use >> the nodeid upon INSERT? > > sure just use default=my_callable(ctx). the ctx contains the bind > parameters for the current row. you could also just use a MapperExtension. I am not sure how to apply a default when not defining the columns - I am sadly using database reflection. I tried using MapperExtension (using before_insert) but nodeid is still None at that point that before_insert is called. > Also please direct emails to the mailing list so that everyone can benefit. Of course. An accident. Maybe I just can't do what I'd like to do here. Using post_update=True, I get some desirable behavior and some errors. The desireable behavior: node = Node() node.parent = node ... sess.add(node) sess.flush() that works But now I can't delete nodes. Any of them. The first thing that happens is an UPDATE which tries to set the parentid to None, which then fails. node = get_any_node() sess.delete(node) sess.flush() # boom. an UPDATE statement setting node's parentid to None is attempted, which fails. I am lucky (I guess) that the table has a DDL-level DEFAULT which works, thus even though the column is specified NOT NULL it also has a DEFAULT. This is what allows the initial INSERT to work, then the post_update is run UPDATE'ing the parentid to the correct value (if specified). However, this same NOT NULL bites me because the post_update also executes an UPDATE of the same node being deleted. -- Jon --~--~-~--~~~---~--~~ 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: strange problem with relation(..)
Jon Nelson wrote: > > > The parent_id is NOT NULL and has no default. Doing something like > this doesn't seem to help, with or without the post_update=True (or > False) configured on the mapper. the NOT NULL makes it impossible, unless you execute a sequence yourself and populate both columns before flushing. > > node = Node() > node.parent = node > sess.add(node) > sess.flush() > > The parentid attribute is always None. Is there an easy way to fix this? post_update=True on the parent relation() will run a second UPDATE statement to populate parent_id. --~--~-~--~~~---~--~~ 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: strange problem with relation(..)
On Tue, Jul 21, 2009 at 1:10 PM, Jon Nelson wrote: > On Tue, Jul 21, 2009 at 10:47 AM, Michael Bayer > wrote: >> >> Jon Nelson wrote: >>> >>> I encountered an odd issue today that I can't explain, and it seems like a >>> bug. >>> I've checked 0.5.4p2 and 0.5.5, and the behavior is the same. >> >> its a mistake I've seen before. Backrefs change the collection, so the >> iteration ends up granting only every other item. >> >> # solution >> for child in list(root.children): >> child.parent = new_root ... >> also the root.parent = root thing will require post_update, see the mapper >> docs for this. I tried using post_update today, both True and False, and maybe I'm just using it wrong. The parent_id is NOT NULL and has no default. Doing something like this doesn't seem to help, with or without the post_update=True (or False) configured on the mapper. node = Node() node.parent = node sess.add(node) sess.flush() The parentid attribute is always None. Is there an easy way to fix this? -- Jon --~--~-~--~~~---~--~~ 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: strange problem with relation(..)
On Tue, Jul 21, 2009 at 10:47 AM, Michael Bayer wrote: > > Jon Nelson wrote: >> >> I encountered an odd issue today that I can't explain, and it seems like a >> bug. >> I've checked 0.5.4p2 and 0.5.5, and the behavior is the same. > > its a mistake I've seen before. Backrefs change the collection, so the > iteration ends up granting only every other item. > > # solution > for child in list(root.children): > child.parent = new_root Aha! Could some note be added to the docs? CAVEAT: operations which change collections should not be used in loops. I'm well aware of the same situation with dicts and lists and so on but for some reason completely missed it here. > also the root.parent = root thing will require post_update, see the mapper > docs for this. Sweet. Thanks! -- Jon --~--~-~--~~~---~--~~ 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: strange problem with relation(..)
Jon Nelson wrote: > > I encountered an odd issue today that I can't explain, and it seems like a > bug. > I've checked 0.5.4p2 and 0.5.5, and the behavior is the same. its a mistake I've seen before. Backrefs change the collection, so the iteration ends up granting only every other item. # solution for child in list(root.children): child.parent = new_root also the root.parent = root thing will require post_update, see the mapper docs for this. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
