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 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.
