mysqldb: Rows READ or Processed

[Steve Holden]

mcl wrote:
> I have looked through Python Database API Specification v2.0, but can
> not find any reference to the number of records processed in a select
> query.
> 
> I know I can get the number of records returned with cursor.rowcount,
> but I want to know the number of records processed.
> 
If you mean the number of (say) rows updated by a SQL UPDATE statement, 
the DB API does not provide any way to access that information, although 
some implementations do have cursor.execute return it. I think MySQLdb 
is one of those ...

> I suppose the info is in one of the internal tables, but I can not
> find any info on that, because phpMyAdmin shows the number of rows in
> a table.
> 
Looky here:

 >>> cn = db.connect(user="root", db="test", passwd="...")
 >>> cu = cn.cursor()
 >>> cu.execute("create table t1 (f1 integer primary key, f2 varchar(50))")
0L
 >>> for i in range(20):
...   cu.execute("INSERT INTO t1(f1, f2) VALUES(%s, %s)", (i, str(i)*i))
...
1L
1L
1L
1L
1L
1L
1L
1L
1L
1L
1L
1L
1L
1L
1L
1L
1L
1L
1L
1L
 >>> cn.commit()
 >>> cu.execute("UPDATE t1 SET f2='changed' WHERE f1<12")
12L
 >>>

As you can see, the execute method returns the number of rows affected 
by an operation.

> I suppose I could use count(*), but would that process all the
> records, which would seem a bit silly.
> 
I have written code that does exactly that: it keeps a field list 
separate from a set of conditions, and executes a "SELECT count(*) FROM 
table WHERE " + condition to determine how many rows will be affected. 
This is useful to maintain uniqueness constraints that aren't in the 
database, for example, and it's a valid technique.

Don't worry about inefficiency until you have evidence that it occurs!

> What is the best method ?
> 
If you're happy to stick with MySQL, use the count returned from the 
cursor.execute() method. Otherwise, SELECT count(*) with the same 
conditions you'll be using for UPDATE.

regards
  Steve
-- 
Steve Holden        +1 571 484 6266   +1 800 494 3119
Holden Web LLC              http://www.holdenweb.com/