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/
--
http://mail.python.org/mailman/listinfo/python-list
