MySQLdb -- any way to get "rows matched"?

[Sheila King]

On Tue, 2 Sep 2003 14:04:20 -0500, Skip Montanaro <skip at pobox.com> wrote in
comp.lang.python in article
<mailman.1062529509.26494.python-list at python.org>:

>     mysql> UPDATE example SET AGE=30 WHERE AGE=30;
>     Sheila> Query OK, 0 rows affected (0.00 sec)
>     Sheila> ws matched: 2  Changed: 0  Warnings: 0
> 
>     Sheila> MySQLdb in Python:
> 
>     >>>> c = conn.cursor()
>     >>>> c.execute("""UPDATE example SET AGE=30 WHERE AGE=30;""")
>     Sheila> 0L
>     >>>> c.messages
>     Sheila> []
>     >>>> c.info()
>     Sheila> ''
> 
>     Sheila> Being a MySQL and MySQLdb newbie, I'm not sure if there is any
>     Sheila> way to find out how many matched, via Python's MySQLdb module. I
>     Sheila> thought, from the source code, that .messages or .info() for the
>     Sheila> cursor attributes/methods might do it, but apparently not???
> 
> I'm not sure what the return value means when executing an update.  My guess
> is that it returns the number of rows changed.  If you want to know the
> number of rows matched, execute a SELECT which matches your WHERE clause:
> 
>     >>> db = MySQLdb.Connection(...)
>     >>> c = db.cursor()
>     >>> c.execute("select * from cities where city='San Jose'")
>     4L
>     >>> c.execute("update cities set city='San Jose' where city='San Jose'")
>     0L
> 
> Skip

Right. That is what I was saying, except that the OP wants to get the
number of matches from an UPDATE statement, even if they entries are not
updated.

IOW, he doesn't want to have to do both SELECT and UPDATE but is hoping to
get the information from only an UPDATE statement.

I was correcting what I believe was incorrect information in an earlier
post. i.e. you wrote:

On Tue, 2 Sep 2003 11:00:58 -0500, Skip Montanaro <skip at pobox.com> wrote in
comp.lang.python in article
<mailman.1062518530.3141.python-list at python.org>:

>     Chris> When issuing updates in mysql (in the console window), mysql will
>     Chris> tell you if any rows matched and how many rows were updated (see
>     Chris> below).  I know how to get number of rows udpated using MySQLdb,
>     Chris> but is there any way to get the number of rows matched?  
> 
> I believe the return value of the cursor's execute() method gives you the
> number of rows which matched.
> 
> Skip

which is not correct.

Roberto Gomez from Argentina contacted me in private email in response to
my last post (apparently having trouble posting to the mailing list today
for some reason) and pointed out that the MySQLdb exceeds the required
DB-API for Python, as the result of "execute" for a cursor is undefined
according to the DB-API. True enough. His point was, that if there is a
concern that the program be compatible with possible other databases in the
future, besides MySQLdb, that this feature of the MySQLdb cursor.execute
method should not be assumed.

In summary the "gee I'm a newbie" remark was meant to indicate that I do
not know the answer to the OP's question, not that I didn't know how to get
the result from a SELECT statement. Sorry for being unclear. 


-- 
Sheila King
http://www.thinkspot.net/sheila/
http://www.k12groups.org/