mysqldb: Rows READ or Processed

[mcl]

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.

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.

I suppose I could use count(*), but would that process all the
records, which would seem a bit silly.

What is the best method ?

Richard

 

[Steve Holden]

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

 

[Carsten Haese]

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

It doesn't? Isn't that what cursor.rowcount does?

 

[Carsten Haese]

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.

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.

I suppose I could use count(*), but would that process all the
records, which would seem a bit silly.

What is the best method ?

Please define what you mean by "processed". If you simply need to know
how many rows are in a table, "select count(*) from that_table" is the
obvious solution. If the database engine is sufficiently intelligent, it
won't have to read the data in every single row to count the rows.

If that's not what you're looking for, please feel free to be more
specific about what you need to achieve.

 

[mcl]

Please define what you mean by "processed". If you simply need to know
how many rows are in a table, "select count(*) from that_table" is the
obvious solution. If the database engine is sufficiently intelligent, it
won't have to read the data in every single row to count the rows.

If that's not what you're looking for, please feel free to be more
specific about what you need to achieve.

Thanks for all the helpful replies.

If you think Count(*) is intelligent, then that is the easy answer. I
will attempt to do some timings.

I did mean number of rows in a table - by processed.

 

[Steve Holden]

It doesn't? Isn't that what cursor.rowcount does?

When it works, yes. Perhaps I should have said that the method provided
by the DB API isn't reliably implemented (for example, on SQL Server
2000 via mx.ODBC it would use a forward cursor which had no predictive
ability about its contents).

The specification says: """the attribute is -1 in case no executeXXX()
has been performed on the cursor or the rowcount of the last operation
is not determinable by the interface. [7]"""

and the footnote points out that the value is "dynamically updatable"
(i.e. not reliable 100% of the time).

Anyway, since the OP just wanted total row count this discussion is moot.

regards
Steve

 

[Carsten Haese]

When it works, yes. Perhaps I should have said that the method provided
by the DB API isn't reliably implemented (for example, on SQL Server
2000 via mx.ODBC it would use a forward cursor which had no predictive
ability about its contents).

Yes, and the same will happen with Informix on a SELECT statement, for
pretty much the same reason. However, you have claimed that the number
of rows updated by an UPDATE statement is not provided, and I have yet
to see a DB-API implementation that doesn't set cursor.rowcount
correctly after executing an UPDATE statement. (The spec doesn't require
that the rowcount be set reliably after an update, but any sane database
engine ought be able to tell you how many rows it touched in an update!)

Anyway, as you said, the point is moot, I just wanted to make sure we're
all on the same page.