[sqlalchemy] Re: suggestion to use deferrable foreign key constraints
Wow, I didn't know that MapperExtensions made Mappers so flexible. Thanks, I'll look into doing that. On Feb 24, 2:35 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > well there would have to be some flag to not register a "dependency" > between classes A and B (or rows C and D). the "post_update" flag > actually does this, but comes with the extra "update this value > later" behavior. > > with the dependency removed, the topological sort wont need to > fulfill that part of the sort and wont raise the circular sorting > issue. however, with constraints removed, doesnt mean we can do > operations in just any old order. namely, if a row in table B > references table A, and A is to be inserted, if A does not yet have a > primary key value generated we would still have to insert A first, > since the generation of primary keys is necessarily bundled with > INSERT operations, which of course is because not every database > supports sequences and we have to rely on cursor.lastrowid and stuff > like that. > > this might be something youre better off doing by not even > establishing the post_updated relation() at all (or establishing the > relation as "viewonly"), and just implementing yourself a series of > before_insert() MapperExtensions that populate the "post_updated" > value and possibly also pre-generates the primary key column ahead of > when its normally generatedsince the model you want depends on > the primary key value of the child object being generated before the > parent object is inserted, and thats not normal ORM behavior. > remember that you can set all the PK/FK attributes you want on your > instances, either before flush() or within before_insert() > operations, and SA will use those values when inserting the rows for > the instance if they are present. > > On Feb 23, 2007, at 2:03 PM, Luke Stebbing wrote: > > > > > PG and Oracle allow you to defer foreign key constraints (Oracle > > apparently lets you defer *all* constraints, mmm), and MySQL and > > SQLite (of course) don't. I'm not sure about other databases. The SQL > > keyword in question is DEFERRABLE. > > > References: > > >http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html > >http://www.postgresql.org/docs/8.2/interactive/sql-set- > > constraints.html > > >http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/ > > general.htm#i1006803 > >http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/ > > clauses002.htm#sthref2933 > >http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/ > > statements_10003.htm#i2066960 > > > On Feb 22, 3:20 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > >> ive heard of foreign key constraints that dont take effect until the > >> tranasction actually commits, but I have never actually seen this in > >> practice. which databases support this feature ? i didnt think it > >> was so common (though not surprised PG supports it). > > >> On Feb 22, 2007, at 1:15 PM, Luke Stebbing wrote: > > >>> Are there any plans to handle circular dependencies by using > >>> deferrable foreign key constraints when available? > > >>> In my case, I had made the foreign key constraints deferred, but > >>> SQLAlchemy didn't pick up on that when I reflected the database > >>> metadata. I eliminated the circular dependency by using > >>> post_update=True, but that meant dropping a NOT NULL constraint > >>> since > >>> postgres can't defer those (sigh). --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Query.get(), minor request
This is really just a minor detail, but I noticed that Query.get(None) will fail with an exception instead of returning None. It has the mapper call "identity_key_from_primary_key", and that tries to do "tuple(util.to_list(None))" -> "tuple(None)" -> TypeError. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: suggestion to use deferrable foreign key constraints
PG and Oracle allow you to defer foreign key constraints (Oracle apparently lets you defer *all* constraints, mmm), and MySQL and SQLite (of course) don't. I'm not sure about other databases. The SQL keyword in question is DEFERRABLE. References: http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html http://www.postgresql.org/docs/8.2/interactive/sql-set-constraints.html http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/general.htm#i1006803 http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm#sthref2933 http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10003.htm#i2066960 On Feb 22, 3:20 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > ive heard of foreign key constraints that dont take effect until the > tranasction actually commits, but I have never actually seen this in > practice. which databases support this feature ? i didnt think it > was so common (though not surprised PG supports it). > > On Feb 22, 2007, at 1:15 PM, Luke Stebbing wrote: > > > > > Are there any plans to handle circular dependencies by using > > deferrable foreign key constraints when available? > > > In my case, I had made the foreign key constraints deferred, but > > SQLAlchemy didn't pick up on that when I reflected the database > > metadata. I eliminated the circular dependency by using > > post_update=True, but that meant dropping a NOT NULL constraint since > > postgres can't defer those (sigh). --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] suggestion to use deferrable foreign key constraints
Are there any plans to handle circular dependencies by using deferrable foreign key constraints when available? In my case, I had made the foreign key constraints deferred, but SQLAlchemy didn't pick up on that when I reflected the database metadata. I eliminated the circular dependency by using post_update=True, but that meant dropping a NOT NULL constraint since postgres can't defer those (sigh). --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Cascade-Delete causes AssertionError (Tries to blank-out primary key ...)
Right, "delete-orphan" is what adds the lifecycle relationship between parent and child. It means that the child can't exist without a parent. That lets SA know that it should eliminate the child rather than trying to null out the relationship. You probably want "all" so that all actions performed on the parent will propagate to the child, which will remove the need to save children directly. See here also: http://www.sqlalchemy.org/docs/unitofwork.myt#unitofwork_cascade Cheers, Luke On Feb 11, 1:49 pm, "Nebur" <[EMAIL PROTECTED]> wrote: > > The minimum correction of my above code seems to be 2 lines: > > > 1.The cascade rule changed from "delete" into "all,delete-orphan" > > No, it turned out there is a still smaller change: > The cascade rule changed from "delete" into "delete, delete-orphan" > will work, too. > The delete-orphan makes up the difference. > Ruben --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Cascade-Delete causes AssertionError (Tries to blank-out primary key ...)
See http://www.sqlalchemy.org/docs/
datamapping.myt#datamapping_relations_lifecycle
I think you want a parent-child relationship between User and
Userdata. Here's how I would change it (disclaimer: I'm new to SA
myself):
class User(object):
pass
class Userdata(object):
pass ### we'll let SQLAlchemy update the relationship
automatically instead of setting it manually
if __name__=="__main__":
db = create_engine("mysql://[EMAIL PROTECTED]/test_cascade")
session = create_session()
metadata = BoundMetaData(db)
t_user = Table("user",metadata,
Column("id",Integer,primary_key=True),
)
t_userdata = Table("userdata",metadata,
Column("user_id",Integer,ForeignKey("user.id"),primary_key=True),
)
metadata.create_all()
### we create the relationship here instead, for clarity.
uselist=False on the relation and the backref makes it one-to-one.
Compare to the example in the link I gave
mapper(User, t_user, properties = {
"userdata": relation(Userdata, uselist=False,
cascade="all, delete-orphan", backref=backref("myuser",
uselist=False))
})
mapper(Userdata, t_userdata)
# create 1 instance of each object:
user1 = User()
session.save(user1)
session.flush()
user1.userdata = Userdata() ### add userdata to user, relationship
is automatically created
session.flush()
# now delete the user
session.delete(user1)
session.flush()
Cheers,
Luke
On Feb 11, 9:44 am, "Nebur" <[EMAIL PROTECTED]> wrote:
> The example below raises an:
> sqlalchemy.exceptions.AssertionError: Dependency rule tried to blank-
> out primary key column 'userdata.user_id' on instance
> '[EMAIL PROTECTED]'
>
> The code creates 2 objects having a 1:1 relation with cascade-delete.
> The ForeignKey is declared as a primary key. This seems to cause the
> Error.
> Versions: Python 2.4, SA 0.3.1, SA 0.3.4
>
> class User(object):
> pass
>
> class Userdata(object):
> def __init__(self, user):
> self.user_id = user.id
>
> if __name__=="__main__":
> db = create_engine("mysql://[EMAIL PROTECTED]/test_cascade")
> session = create_session()
> metadata = BoundMetaData(db)
>
> t_user = Table("user",metadata,
> Column("id",Integer,primary_key=True),
> )
> t_userdata = Table("userdata",metadata,
>
> Column("user_id",Integer,ForeignKey("user.id"),primary_key=True),
> )
> metadata.create_all()
> mapper(User, t_user)
> mapper(Userdata, t_userdata, properties = {
>
> "myuser":relation(User,backref=backref("meta",cascade="delete"))
> })
>
> # create 1 instance of each object:
> user1 = User()
> session.save(user1)
> session.flush()
> data1 = Userdata(user1)
> session.save(data1)
> session.flush()
>
> # now delete the user,
> # expecting the cascade to delete userdata,too:
> session.delete(user1)
> session.flush() #AssertionError: Dependency rule tried to blank-
> out ...
>
> I can workaround this error by using a separate primary key in table
> userdata:
> t_userdata = Table("userdata",metadata,
> Column("id",Integer,primary_key=True),
> Column("user_id",Integer,ForeignKey("user.id")),
> )
> and everything works fine.
> I'm wondering whether this is an SA bug, or a bad table design ?
> Thanks and regards,
> Ruben
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
