[sqlalchemy] SQLAlchemy ResultProxy.rowcount not zero when UPDATE has no affected rows
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. -- 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/-/rA953M70mu4J. 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.
Re: [sqlalchemy] SQLAlchemy ResultProxy.rowcount not zero when UPDATE has no affected rows
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.
Re: [sqlalchemy] SQLAlchemy ResultProxy.rowcount not zero when UPDATE has no affected rows
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.
Re: [sqlalchemy] SQLAlchemy ResultProxy.rowcount not zero when UPDATE has no affected rows
Well, my reply doesn't really make sense as the note is there. I mean that it should be accessible from the rowcount description. That would be nice if affected row was an hyperlink to it's definition in the context of SQLAlchemy. -- 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/-/4-7klHGBkUsJ. 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.
