On Saturday, April 26, 2014 12:29:50 AM UTC+1, Michael Bayer wrote: > > > On Apr 25, 2014, at 6:54 PM, Tim Kersten <t...@io41.com <javascript:>> > wrote: > > This is an unusual use case because it seems like you’d like to outright >> ignore the row if it doesn’t match? or are you throwing an exception if >> you don’t get the expected count? >> > > Yes, I'm ignoring the row if it doesn't match. One use case for this: A > misbehaving piece of code set a "bad" value in a column in many rows. The > code was fixed, and thus when customers used that code in future the values > would be updated to "good" values. A data migration to automatically adjust > the "bad" values would have the potential to overwrite one that's just been > altered by a customer, and this is what I'm trying to avoid, with as > minimal an impact on the running system. I'd only like to avoid doing so > while still using the ORM and was wondering if there's a way to do this > without having a version column? > > > I can think of some potentially very exotic ways of injecting this > behavior with cursor_execute() events, but it would be quite awkward. The > ORM still might complain when it sees zero rows updated. >
> But the real problem is if you want the ORM to update a row, then have it > ignored, now you’ve totally got the wrong data in memory. I’m not sure how > your application is constructed that A. you want to use the ORM but B. you > don’t care if it has the wrong data loaded after it thinks it’s > synchronized. If you just need some simplistic CRUD interface (e.g. > object.save()) you can build those on top of Core pretty easily. The ORM > has a specific usage model and this goes counter to that, hence there’s a > Core. > In my initial the sql expression example no updates happen because no matching rows are found and thus they're ignored, however it's not important that they're ignored, just that no update can happen. I'd be happy to have a way to do this via the ORM if that raises an exception instead. I guess what I've been looking for is here is the exact same behaviour as the version col id behaviour, except there is no version id column. Instead there are one or more columns used in a similar manor instead, basically a mechanism to add additional expressions. If zero rows are matched, an exception is raised, similar to if a version column was updated by another transaction. Session.set_update_where(my_instance, MyModel.name == 'old_value') The resulting behaviour would be identical to using a version col id, but only for this transaction and the instance passed to the update_where() method, and instead of "UPDATE ... WHERE <pk> = %s AND version = %s" you'd have "UPDATE ... WHERE <pk> = %s AND name = %s". An alternative approach might be to do infer all changed column values and add their old values to the WHERE part before a flush. This behaviour would need to be turned on for a table or perhaps just for an instance. The above approaches provide the same behaviour that version col id does, but has different trade offs. Advantages of using the version col id approach instead of the above: - Smaller SQL queries as the WHERE clause contains only the PK and the version column, vs all lots of columns and their old values. Advantages of using the above approach instead of version col id: - Much finer grain changes possible without raising an exception, provided that the various transactions all modify different columns. (i.e. txn A changes the 'name' col, and txn B changes the 'description' col, both would be accepted, where as with the version col id one of the transactions would have failed.) I don't know half as much as I'd like about how queries are run in the DB, so my suggested alternative approaches above may have serious performance implications other than increasing SQL traffic. Thank you very much for your feedback. For now I'll focus on adding version columns to most of my tables and that should solve not only my current issues but will also protect the system from the lost update issues in general. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
