[lnkForumImage]
TotalShareware - Download Free Software

Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

comp.lang.python

mysqldb: Rows READ or Processed

mcl

2/6/2008 10:51:00 PM

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

6 Answers

Steve Holden

2/6/2008 11:54:00 PM

0

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

Carsten Haese

2/7/2008 12:15:00 AM

0

On Wed, 2008-02-06 at 18:53 -0500, Steve Holden wrote:
> 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
http://informixdb.sourc...


Carsten Haese

2/7/2008 12:20:00 AM

0

On Wed, 2008-02-06 at 14:51 -0800, 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.
>
> 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.

--
Carsten Haese
http://informixdb.sourc...


mcl

2/7/2008 1:12:00 AM

0

On Feb 7, 12:19 am, Carsten Haese <cars...@uniqsys.com> wrote:
> On Wed, 2008-02-06 at 14:51 -0800, 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.
>
> > 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.
>
> --
> Carsten Haesehttp://informixdb.sourc...

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

2/7/2008 2:35:00 AM

0

Carsten Haese wrote:
> On Wed, 2008-02-06 at 18:53 -0500, Steve Holden wrote:
>> 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?
>
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
--
Steve Holden +1 571 484 6266 +1 800 494 3119
Holden Web LLC http://www.hold...

Carsten Haese

2/7/2008 5:18:00 AM

0

On Wed, 2008-02-06 at 21:35 -0500, Steve Holden wrote:
> Carsten Haese wrote:
> > On Wed, 2008-02-06 at 18:53 -0500, Steve Holden wrote:
> >> 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?
> >
> 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.

--
Carsten Haese
http://informixdb.sourc...