473,326 Members | 2,134 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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
Jul 18 '05 #1
5 5045

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

Jul 18 '05 #2
On Tue, 2 Sep 2003 11:00:58 -0500, Skip Montanaro <sk**@pobox.com> wrote in
comp.lang.python in article
<ma*********************************@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


That's not what this says?

http://groups.google.com/groups?selm...t%40python.org

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
--
Sheila King
http://www.thinkspot.net/sheila/
http://www.k12groups.org/
Jul 18 '05 #3

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

Jul 18 '05 #4
On Tue, 2 Sep 2003 14:04:20 -0500, Skip Montanaro <sk**@pobox.com> wrote in
comp.lang.python in article
<ma**********************************@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 <sk**@pobox.com> wrote in
comp.lang.python in article
<ma*********************************@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/
Jul 18 '05 #5
On Tue, 02 Sep 2003 20:30:26 GMT, Sheila King <sh****@spamcop.net>
wrote:
On Tue, 2 Sep 2003 14:04:20 -0500, Skip Montanaro <sk**@pobox.com> wrote in
comp.lang.python in article
<ma**********************************@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.


[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
Jul 18 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Max | last post by:
Hello everybody, I have a problem with javascript. Basically I have a frame that is split with this code: <frameset rows="0,*"> <frame name="top" src="nothing.html"> <frame name="center"...
2
by: Kiki | last post by:
Hi, just a small question. I've just looked at a friends code and he's done this: int rowsAffected = sql.ExecuteNonQuery("stored_procedure", cmdParams); if(rowsAffected < 1) { return false; }...
9
by: jensen bredal | last post by:
I have a dataView and i only want to get the first x rows . Is there a way of doing this without using Foreach? Thanks JB
3
by: Andy Fish | last post by:
Hi, when there are no rows to display in my asp.net datagrid control, I'd like to put in a message saying "No results" or some such Ideally I'd like the message to be centred across all the...
0
by: romancoelho | last post by:
Hey everyone, I know this has to be simple, but can't figure out how to do it. How can I display the grid lines in all rows in the gird. The default behavior of the DataGridView seems to be that it...
17
by: pbd22 | last post by:
Hi. How does one return a range of rows. I know that "Top 5" will return rows 0 - 5 but, how do I get 6 - 10? thanks
3
by: nkoriginal | last post by:
Hi: I want to create an store procedure for an ASP page. I've a form with 100 fields and I want to insert the information in: 1) one table with 100 rows or 2) in 5 tables Now, my idea is...
5
by: maury | last post by:
Hello, I have a DB table with data filled from a weather sensor probe, I have one row every 10 minutes and the data fields is not in DateTime format but in string format: yyyyMMddHHmm So for...
8
by: phub11 | last post by:
Hi - I have a function which appends row(s) to the bottom of a table: function mouseUpHandler() { var table = document.getElementById("mytable"); var rowCount = table.rows.length; var...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.