[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
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.
[sqlalchemy] Re: Mapping an existing database
Thanks. I'll get on it. a...@svilendobrev.com wrote: see sqlsoup and/or metadata( reflect=True) I'm new to SQLAlchemy. I am trying to use Pylons to expose an existing database in a browser. All the introductory material assumes I am going to create the database from python object definitions, but I want to create the python object definitions from the database. Am I on my own, or is there a well-worn path through these woods? Thanks for any advice you may have for me. -- John Francis Lee 1025/37 Thanon Jet Yod Mueang Chiangrai 57000 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapping an existing database
OK. I'll check that out too. I appreciate your help. Andreas Jung wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 29.03.2009 2:44 Uhr, j...@robinlea.com wrote: Hi, I'm new to SQLAlchemy. I am trying to use Pylons to expose an existing database in a browser. All the introductory material assumes I am going to create the database from python object definitions, but I want to create the python object definitions from the database. Am I on my own, or is there a well-worn path through these woods? 'autoload' support is your friend. - -aj -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (Darwin) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAknPnWgACgkQCJIWIbr9KYyH4gCeKuRKLTKhrRUF5UgkaBPKz0Mo bncAoMJsThPf4k5+tkk6iZEfIEdNTlBz =mSV5 -END PGP SIGNATURE- -- John Francis Lee 1025/37 Thanon Jet Yod Mueang Chiangrai 57000 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapping an existing database
Michael Bayer wrote: j...@robinlea.com wrote: Hi, I'm new to SQLAlchemy. I am trying to use Pylons to expose an existing database in a browser. All the introductory material assumes I am going to create the database from python object definitions, but I want to create the python object definitions from the database. Am I on my own, or is there a well-worn path through these woods? Thanks for any advice you may have for me. SQLA has no assumption that it would be creating tables. if you do call metadata.create_all(), which itself is optional, it actually checks for those tables already existing and will not affect them. You can load the definition of existing tables using Table(..., autoload=True), or create fully defined Table objects (or declarative mappings) that correspond to the existing schema. You can even leave out columns or tables you aren't concerned about. Thanks again. It sounds like there's no problem dealing with an existing database. -- John Francis Lee 1025/37 Thanon Jet Yod Mueang Chiangrai 57000 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
