Thank you for this very instructive answer! As I've never really used anything else than MySQL, I didn't know this. But as you said, there should probably be a note about this in the documentation, at least to define what "affected" means in the context of SQLAlchemy.
On Tuesday, June 12, 2012 10:49:53 AM UTC-4, Michael Bayer wrote: > > > On Jun 12, 2012, at 7:07 AM, Francis wrote: > > I posted this on StackOverflow: > > We have a MySQL database and we're switching from DB API to SQLAlchemy. >> From the documentation, the ResultProxy.rowcount is supposed to report the >> number of affected rows by an UPDATE statement. >> If I execute this query with SQLAlchemy: >> UPDATE table >> SET field = >> IF(field < 10, 10, field) WHERE id = 1 >> It will return a rowcount of 1 if there's a matching row, but for any >> value of "field" (even when greater or equal than 10). When I executed this >> query with DB API, it returned the correct number of affected rows (0 when >> field was greater or equal than 10 and 1 if lower). >> That's causing us some troubles because we'll have to first execute a >> select query to determine if there was a change or not. Is it a bug in >> SQLAlchemy? The documentation clearly states that if should return the >> number of affected rows, not the number of matching rows. > > > I got an answer saying that if CLIENT_FOUND_ROWS is set, the number of > affected rows becomes the number of matching rows. I've checked and > SQLAlchemy sets this flag. This is clearly in contradiction with the > documentation, so I think that it's a bug that should be fixed. > > > First off, let's note the new documentation which reorganizes the many > misunderstandings this attribute has caused over the years, and now > includes a removal of the word "affected": > > > http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html#sqlalchemy.engine.base.ResultProxy.rowcount > > MySQL-specific notes are also added at: > > http://docs.sqlalchemy.org/en/rel_0_7/dialects/mysql.html#rowcount-support > > these new notes are in versions 0.6, 0.7, 0.8. > > Next, here's why "affected" is not clearly a contradiction based on where > you're coming from. > > The DBAPI spec says this: > > This read-only attribute specifies the number of rows that > the last .execute*() produced (for DQL statements like > 'select') or affected (for DML statements like 'update' or > 'insert'). > > First note that the rowcount on "select" is not something anyone uses > consistently since this is not a common feature of database client > libraries. > > For UPDATE and DELETE, someone familiar with a wide array of databases > would read this paragraph and link it to the knowledge they have of what > database client libraries usually offer in this regard. The client APIs > of: SQLite, Postgresql, Oracle, Sybase, Microsoft SQL Server, Firebird, and > all the rest of them, *except* for MySQL, consider the "rowcount" to be the > number of rows *matched* by the WHERE criterion. So most people who read > this paragraph link it to a broader field of knowledge about client APIs > and based on established convention know that "affected" here means "number > of rows matched", where MySQL is, as they so often are, the one backend > that re-interprets the meaning/behavior of standard features to be > something different (but as always, with flags/switches added at some point > to make it act the "normal" way). > > It was in this way that the word "affected" found its way directly in the > SQLAlchemy docs for rowcount. However, taking the word "affected" > literally without the benefit of context, yes this is totally unclear. > I've emailed DB-SIG to get their thoughts on if/how they'd like to clarify > this. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/M7PyI_DGh0oJ. 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.
