[sqlalchemy] Re: SQLAlchemy and unit tests
Perfect, thanks Michael - I'll pass this on to the TG list 2009/5/11 Michael Bayer mike...@zzzcomputing.com clear out the session (or make a new one) between tests. while the session attempts to weak reference its contents, it usually ends up holding onto a lot of stuff due to a particular reference cycle created by backrefs (I'm thinking of ways to eliminate that behavior). On May 11, 2009, at 10:54 PM, James wrote: Hi all, I'm trying to track down an error where running a full TurboGears unit test suite fails with a SQLAlchemy error, while running the single (failing) unit test passes OK. The SA error is of the form: FlushError: New instance terms...@0x2b15590 with identity key (class 'yel.model.select_source.TermSite', (1, 1), None) conflicts with persistent instance terms...@0x2b8ad50 TermSite is an association table for a many-to-many relationship, which has a multi-column primary key: term_site_table = Table('term_site', metadata, Column('term_id', Integer, ForeignKey(term.id, ondelete=CASCADE), primary_key=True), Column('site_id', Integer, ForeignKey(site.id, ondelete=CASCADE), primary_key=True), Column('weight', Integer, default=1, nullable=False), ) The error seems to signify that SA thinks there is already an object waiting to be flushed with the same term_id and site_id. The fact that the test only fails when a full test suite is run implies that there is some state left over in between unit test executions. Abbreviated setUp and tearDown methods: def setUp(self): cherrypy.root = root.Root() turbogears.startup.startTurboGears() metadata.create_all() def tearDown(self): metadata.drop_all() turbogears.startup.stopTurboGears() TurboGears provides a sqlalchemy_cleanup method (here: http://svn.turbogears.org/branches/1.0/turbogears/testutil.py), but that seemed to be too aggressive, producing errors of the form: InvalidRequestError: Class 'Visit' entity name 'None' has no mapper associated with it So: - what is the recommended database initialisation / cleanup strategy for unit tests involving SA? - can anyone suggest how ORM state could be hanging around between unit tests (I'm using an in-memory DB)? - is there a convenient way to check on objects in the ORM, waiting to be flushed? This is SA 0.4.3 and TG 1.0.8 running against an in-memory sqlite DB Thanks! James (Cross-posted to TG list) --~--~-~--~~~---~--~~ 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] Cascading deletes to children
Hi all,I'm trying to get deletes and updates cascaded down from a parent object to the child objects (connected by ForeignKey). It all seems pretty simple in the docs, but I can't get it to work! I'm using MySQL with the InnoDB engine, and have played with all the variation of the onupdate, ondelete and cascade arguments I can think of. The problem is that immediately before the DELETE command is sent to MySQL, there are UPDATE commands nulling out the foreign key references of the child objects, so MySQL doesn't trigger it's ON DELETE CASCADE action, and for some reason cascade=all, delete-orphan doesn't clean up the children with NULL FKs. Here's a simplified model: users_table = Table('tg_user', metadata, Column('user_id', Integer, primary_key=True), Column('created', DateTime, default=datetime.now), mysql_engine='InnoDB', ) hat_table = Table('hat', metadata, Column('id', Integer, primary_key=True), Column('name', Unicode(100)), Column('user_id', Integer, ForeignKey('tg_user.user_id'), onupdate='CASCADE', ondelete='CASCADE'), mysql_engine='InnoDB', ) class User(object): pass class Hat(object): pass mapper(User, users_table) mapper(Hat, hat_table, properties = { 'user': relation(User, backref=hats, cascade=all, delete, delete-orphan), } ) And the log from SA when I do a User.delete: BEGIN UPDATE hat SET user_id=%s WHERE hat.id = %s [None, 1L] DELETE FROM tg_user WHERE tg_user.user_id = %s [1L] COMMIT Any help would be much appreciated! James --~--~-~--~~~---~--~~ 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: TurboGears: best practices for SELECTing
Hi Florent, On Oct 11, 1:01 pm, Florent Aide [EMAIL PROTECTED] wrote: On 10/11/07, James Brady [EMAIL PROTECTED] wrote: Did you call assign_mapper on Ownership? [...] However, I'm using the identity framework (part of TurboGears) which unfortunately doesn't play nicely with assign_mapper... shame! If you use SA 0.3.10 I would advise you to use mapper (from sqlalchemy.orm import mapper) instead of assign_mapper. If you could send a post in the TurboGears mailing list about why identity does not play nice with assign_mapper we would surely find a solution to your issue :) So, using mapper rather than assign_mapper brings me back to my original problem of not being able to use the relations I've set up in my models - the ResultProxy object only has the basic keys from the table definition. I used a lot of assign_mapper myself with TG a few months back, I now switched to mapper in order to become 0.4 compliant, but I can tell you TG 1.0.2/1.0.3.x are assign_mapper friendly... 1.0.4beta1 should also be usable with assign_mapper. So is assign_mapper deprecated in SA 0.4? Is sqlalchemy.orm.mapper the direction I should be heading in? Thanks, James --~--~-~--~~~---~--~~ 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: TurboGears: best practices for SELECTing
On Oct 11, 8:05 am, Nebur [EMAIL PROTECTED] wrote: Can anyone answer these specific questions or point me in the direction of some further documentation? I assume that the SA doc, chapter object relational tutorial does best clearify how to create and use a session.http://www.sqlalchemy.org/docs/04/ormtutorial.html Or are there some shortcomings that experienced users cannot see anymore ? Hi Ruben, I'm actually using version 0.3, so hadn't seen that tutorial - that is the sort of thing I was looking for! To be honest, the hole in documentation seems to be on the TurboGears side, which currently has a lot more on SQLObject... So it seems from that tutorial that session.query... is the way to go for these selects, and I need to learn how to manage the sessions properly! Thanks, James --~--~-~--~~~---~--~~ 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: TurboGears: best practices for SELECTing
On Oct 10, 2:12 pm, Marco Mariani [EMAIL PROTECTED] wrote: James Brady ha scritto: The problem I'm hitting at the moment is how to properly select simple objects... There seems to be two main approaches, for example: session.query(Ownership).select() or ownership_table.select().execute() With the first approach, I get InvalidRequestError: Parent instance class 'tnf.model.Ownership' is not bound to a Session, and no contextual session is established; ... which I'm not sure how tackle - there's no mention I can find of when and where create_session should be called... Did you call assign_mapper on Ownership? Can anyone answer these specific questions or point me in the direction of some further documentation? You should be able to call Ownership.query() just fine, if you have a working mapper. Hi Marco, assign_mapper did help - the relations are now accessible through my models, and the query syntax is nicer than pure SA as well in my opinion. However, I'm using the identity framework (part of TurboGears) which unfortunately doesn't play nicely with assign_mapper... shame! James --~--~-~--~~~---~--~~ 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] TurboGears: best practices for SELECTing
Hello all, I'm a newcomer to SA, using it as the ORM for TurboGears at the moment. Is there documentation/tutorials/recipes somewhere about how to properly use SA from TurboGears? The problem I'm hitting at the moment is how to properly select simple objects... There seems to be two main approaches, for example: session.query(Ownership).select() or ownership_table.select().execute() With the first approach, I get InvalidRequestError: Parent instance class 'tnf.model.Ownership' is not bound to a Session, and no contextual session is established; ... which I'm not sure how tackle - there's no mention I can find of when and where create_session should be called... The the second approach, the SELECTs work fine, but then I'm working with RoxProxy objects, rather than my models, which don't have the relations and back-references I've set up and that I obviously need. Can anyone answer these specific questions or point me in the direction of some further documentation? Thanks, James --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---