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

[Chris Stromberger]

When issuing updates in mysql (in the console window), mysql will tell
you if any rows matched and how many rows were updated (see below). I
know how to get number of rows udpated using MySQLdb, but is there any
way to get the number of rows matched? I want to find out, when rows
updated = 0, if there were no updates because the row wasn't found
(rows matched will = 0) or because the update would not have changed
any data (rows matched = 1).

mysql> select * from test;
+------+------+
| rver | s |
+------+------+
| 1 | new |
+------+------+
1 row in set (0.00 sec)

mysql> update test set rver = 1, s = 'new' where rver = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql> update test set rver = 1, s = 'new' where rver = 17;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> update test set rver = 1, s = 'neww' where rver = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Thanks,
Chris

 

[Skip Montanaro]

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

 

[Sheila King]

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

That's not what this says?

http://groups.google.com/[email protected]

Just for confirmation, a little experimentation:

MYSQL at the command line:

mysql> SELECT * from example;
+--------+------+---------+
| name | AGE | COUNTRY |
+--------+------+---------+
| sheila | 30 | US |
| arthur | 23 | NL |
| bob | 30 | US |
+--------+------+---------+
3 rows in set (0.00 sec)

mysql> UPDATE example SET AGE=30 WHERE AGE=30;
Query OK, 0 rows affected (0.00 sec)
ws matched: 2 Changed: 0 Warnings: 0


MySQLdb in Python:

c = conn.cursor()
c.execute("""UPDATE example SET AGE=30 WHERE AGE=30;""") 0L
c.messages []
c.info()

''


Being a MySQL and MySQLdb newbie, I'm not sure if there is any way to find
out how many matched, via Python's MySQLdb module. I thought, from the
source code, that .messages or .info() for the cursor attributes/methods
might do it, but apparently not???


I'm using MySQL 3.23.57 and MySQLdb 0.92

 

[Skip Montanaro]

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:
0L

Skip

 

[Sheila King]

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

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.

 

[Chris Stromberger]

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.

[snip]

Exactly correct. Since mysql provides this info "for free" when doing
an update, I was hoping to use that via MySQLdb, but it appears that
it's not available. Thanks for the help.

-Chris