MySQLdb: execute query results...

[Sheila King]

I have searched for an answer to this question on the comp.lang.python
archives at Google Groups, and also looked through the (sparse) MySQLdb
documentation, and cannot confirm for a FACT what I think I know.

Looking for affirmation before I go "assuming" something and getting myself
into trouble somewhere along the line...

Here is what I *think* I know...let me know if I'm wrong, for crying out
loud!!!

If I do something like:

>>> db = MySQLdb.connect(passwd="mypass", host="localhost",
... db="mydb", user="myusername")
>>> c = db.cursor()

and then later do:

>>> result = c.execute("""<some proper MySQL query here>""")

doesn't result always return the number of affected rows?

As a more concrete example:

I made the following table:

mysql> CREATE TABLE example(
-> name VARCHAR(14),
-> AGE INT,
-> COUNTRY VARCHAR(15));
Query OK, 0 rows affected (0.03 sec)

And then in Python did the following INSERTS and UPDATE:

>>> c.execute("""INSERT INTO example (name, AGE, COUNTRY) VALUES ("sheila", 29, "US");""")
1L
>>> c.execute("""INSERT INTO example (name, AGE, COUNTRY) VALUES ("arthur", 23, "NL");""")
1L
>>> c.execute("""INSERT INTO example (name, AGE, COUNTRY) VALUES ("bob", 29, "US");""")
1L
>>> result = c.execute("""UPDATE example SET AGE=30 WHERE AGE=29;""")
>>> result
2L

Can I *RELY* on that "2L" result of the UPDATE statement under the cursor's
"execute" method representing the number of rows updated? Or am I assuming
too much?

Like I say, I looked through several sources, including my O'Reilly MySQL
book and cannot verify this for a fact from docs.

I did find this, however:
http://www.mysql.com/doc/en/UPDATE.html

"UPDATE returns the number of rows that were actually changed. In MySQL
Version 3.22 or later, the C API function mysql_info() returns the number
of rows that were matched and updated and the number of warnings that
occurred during the UPDATE. "

Which seems to corroborate, although they are not referring to the Python
DB-API specifically.

Thanks for any clarification on this matter,

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

[mackstann]

On Sun, Aug 31, 2003 at 11:57:07PM +0000, Sheila King wrote:
> Here is what I *think* I know...let me know if I'm wrong, for crying out
> loud!!!
>

> [...]

>
> >>> result = c.execute("""<some proper MySQL query here>""")
>
> doesn't result always return the number of affected rows?
>

> [...]

>
> Can I *RELY* on that "2L" result of the UPDATE statement under the cursor's
> "execute" method representing the number of rows updated? Or am I assuming
> too much?

Looked in site-packages/MySQLdb/cursors.py, and it seems you are
correct.

>>> from MySQLdb import cursors
>>> help(cursors.BaseCursor.execute)

Help on method execute in module MySQLdb.cursors:

execute(self, query, args=None) unbound MySQLdb.cursors.BaseCursor
method
Execute a query.

[...]

Returns long integer rows affected, if any

HTH,
--
Nick Welch aka mackstann | mack @ incise.org | http://incise.org
Harris's Lament:
All the good ones are taken.

[Sheila King]

On Sun, 31 Aug 2003 19:14:00 -0500, mackstann <ma...@incise.org> wrote in
comp.lang.python in article
<mailman.1062375303...@python.org>:

> Looked in site-packages/MySQLdb/cursors.py, and it seems you are
> correct.
>
> >>> from MySQLdb import cursors
> >>> help(cursors.BaseCursor.execute)
>
> Help on method execute in module MySQLdb.cursors:
>
> execute(self, query, args=None) unbound MySQLdb.cursors.BaseCursor
> method
> Execute a query.
>
> [...]
>
> Returns long integer rows affected, if any
>
> HTH,

Thanks for the confirmation. I didn't look at the source code. :/

Much appreciated,