Yes, this is so a 'Delete' button can return a list of references that need to be broken before the row can be deleted. I don't want cascades in this case, as there is too much potential for the user to blow away hours of data entry with a single deletion.
Yeah, I fully expect that what I'm trying to do will require queries at some level, but I was hoping that the SQLA API might have something built in for getting references to an object, or just for seeing if there are any. When I mentioned having a usesless transaction, what I meant was that the user may have done a bunch of valid changes, without clicking 'Save' (which does a commit()), and then if they attempt a deletion that causes an IntegrityError, all of their valid changes are lost, because the session has to be rolled back before it can be used again. I was hoping there was a way to... I guess, 'insulate' all of the other uncommited DB changes from one dodgy deletion. Anyway, it kind of looks to me like any attempt to do this in some sort of clever automatic way is going to be more trouble than its worth, so I think I'll just bite the bullet and put backrefs on all of the relationships that are going to the table in question, and then just check all of the backrefs before deletion. This was always the most obvious solution to me, but I was hoping for some perfect magical function that would do it automatically. One can dream! :) Thanks Mike. Cameron Jackson Engineering Intern Air Operations Thales Australia Thales Australia Centre, WTC Northbank Wharf, Concourse Level, Siddeley Street, Melbourne, VIC 3005, Australia Tel: +61 3 8630 4591 cameron.jack...@thalesgroup.com.au<mailto:cameron.jack...@thalesgroup.com.au> | www.thalesgroup.com.au<http://www.thalesgroup.com.au> From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: Monday, 23 January 2012 12:41 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Anticipating an IntegrityError before it happens (or noticing it immediately after) On Jan 22, 2012, at 7:50 PM, Jackson, Cameron wrote: I have a certain ORM model that is being referenced from several other models. I don't want the user to be able to delete rows from that table that are still being referenced elsewhere. Ideally I would like something like: refs = get_references(row) if refs: print "Can't delete row because of:" for ref in refs: print ref else: self.session.delete(row) Or even just: if is_being_referenced(row): print "You can't delete that!" else: self.session.delete(row) If there's no way to do either of those, then I would settle for: try: self.session.delete(row) except sql.exc.DeletionError: print "You can't delete that!" #Again, a way to get a list of the offending references would be nice! So I'm assuming you'd like some kind of GUI-friendly indicator here. If people are just writing code I'd argue the IntegrityError should be enough, as you know SQLA tries hard to not do redundant work versus what the database can do much better. The error is much more complete, efficient, and accurate than the ORM could ever do. Anyway, to do this reliably would definitely need the database to be accessed - any relationship that isn't loaded would need to be queried against, at least a "count" to see if rows exist. Then, it gets more complicated if the operation is to cascade appropriately, such as if deleting A naturally deletes B, but then you get IntegrityError because row C depends on B - if you needed that then the functionality would have to cascade throughout everything, loading all the relationships in its path to get at the related records. The implementation can also be very complicated if you expect it to work without your target object having relationships out to these other objects. Not only would you need to have some kind of registry that allows you to track all the other classes that might depend on this one, you'd also need to scan through all of them and query the database for every possible relationship pointing at your object. So assuming you want just a really simple thing here, and also you can have all the relationships coming off the target class, an event-based approach would look through Session.deleted and look at all the relationship-bound references associated with the object: from sqlalchemy.orm.interfaces import ONETOMANY, MANYTOMANY from sqlalchemy.orm.properties import RelationshipProperty @event.listens_for(Session, "before_flush") def check_illegal_deletes(session, flush_context, instances): for obj in session.deleted: mapper = object_mapper(obj) for prop in mapper.get_properties(): if isinstance(prop, RelationshipProperty) and \ prop.direction in (ONETOMANY, MANYTOMANY): if ( # check for those that are loaded prop.key in obj.__dict__ and bool(obj.__dict__[key]) ) or \ # do a count if not loaded. this is if you need unloaded # things to be included session.query(prop.mapper).with_parent(obj, prop.key).count(): raise Exception("object %s has referred objects in relationship %s" % (obj, key)) if you truly need this to happen at the instant session.delete() is called, note that this isn't as complete as the event since it won't account for the "delete" cascade, but you can also subclass Session and override Session.delete() to perform a similar check as above. I tried doing the above, catching IntegrityError, but the problem is that SQLA doesn't 'notice' the error and tell me about it until another action occurs that causes other queries to be run. Is there some way I can make SQLA check the integrity of the database immediately after the deletion? Preferably without running unnecessary queries if that's going to cause a performance hit. Session.delete() doesn't hit the database, it only marks the item for deletion on the next flush. So you'd never get the IntegrityError until the flush. Also once you get the IntegrityError, that's the end of the line - unless you're catching the exception and then doing more queries yourself, there's no other queries being run. Cheers, Cam Cameron Jackson Engineering Intern Air Operations Thales Australia Thales Australia Centre, WTC Northbank Wharf, Concourse Level, Siddeley Street, Melbourne, VIC 3005, Australia Tel: +61 3 8630 4591 cameron.jack...@thalesgroup.com.au<mailto:cameron.jack...@thalesgroup.com.au> | www.thalesgroup.com.au<http://www.thalesgroup.com.au/> ------------------------------------------------------------------------- DISCLAIMER: This e-mail transmission and any documents, files and previous e-mail messages attached to it are private and confidential. They may contain proprietary or copyright material or information that is subject to legal professional privilege. They are for the use of the intended recipient only. Any unauthorised viewing, use, disclosure, copying, alteration, storage or distribution of, or reliance on, this message is strictly prohibited. No part may be reproduced, adapted or transmitted without the written permission of the owner. If you have received this transmission in error, or are not an authorised recipient, please immediately notify the sender by return email, delete this message and all copies from your e-mail system, and destroy any printed copies. Receipt by anyone other than the intended recipient should not be deemed a waiver of any privilege or protection. Thales Australia does not warrant or represent that this e-mail or any documents, files and previous e-mail messages attached are error or virus free. ------------------------------------------------------------------------- -- 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<mailto:sqlalchemy@googlegroups.com>. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com<mailto:sqlalchemy+unsubscr...@googlegroups.com>. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- 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. ------------------------------------------------------------------------- DISCLAIMER: This e-mail transmission and any documents, files and previous e-mail messages attached to it are private and confidential. They may contain proprietary or copyright material or information that is subject to legal professional privilege. They are for the use of the intended recipient only. Any unauthorised viewing, use, disclosure, copying, alteration, storage or distribution of, or reliance on, this message is strictly prohibited. No part may be reproduced, adapted or transmitted without the written permission of the owner. If you have received this transmission in error, or are not an authorised recipient, please immediately notify the sender by return email, delete this message and all copies from your e-mail system, and destroy any printed copies. Receipt by anyone other than the intended recipient should not be deemed a waiver of any privilege or protection. Thales Australia does not warrant or represent that this e-mail or any documents, files and previous e-mail messages attached are error or virus free. ------------------------------------------------------------------------- -- 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.
