Possible bug with cursor.execute()

[kerby2000]

It seems that I'm the first one who is posting on this discussion
list :)

I'm trying to execute the following SQL query, but ocpgdb gives me
only 1-row result (only for the first typeid item)

cursor.execute( "SELECT MAX(id) AS id FROM settings WHERE id<=5800
GROUP BY typeid" )

If I execute the same query through the Postgres command line
interface I'm getting as many results as many typeid's I have.

Don't you allow to use GROUP BY command ?
What I'm doing wrong?

Kind regards,

Sergey

[Andrew McNamara]

>It seems that I'm the first one who is posting on this discussion
>list :)

Yep.

>I'm trying to execute the following SQL query, but ocpgdb gives me
>only 1-row result (only for the first typeid item)
>
>cursor.execute( "SELECT MAX(id) AS id FROM settings WHERE id<=5800
>GROUP BY typeid" )
>
>If I execute the same query through the Postgres command line
>interface I'm getting as many results as many typeid's I have.
>
>Don't you allow to use GROUP BY command ?

"GROUP BY" certainly works (I use it extensively in my own work), and
when I try to replicate your problem it works correctly:

andrewm=# create table settings ( id int, typeid int );
CREATE TABLE
andrewm=# insert into settings values (1, 1);
INSERT 0 1
andrewm=# insert into settings values (2, 1);
INSERT 0 1
andrewm=# insert into settings values (2, 3);
INSERT 0 1
andrewm=# insert into settings values (4, 3);
INSERT 0 1
andrewm=# insert into settings values (3, 1);

>>> db=connect()
>>> c=db.cursor()
>>> c.execute('select max(id) as id from settings where id < 4 group by typeid')
<ocpgdb.connection.Cursor instance at 0x92faaac>
>>> c.fetchall()
[(2,), (3,)]

I'm not sure what is going wrong in your case. The module returns as
many results as postgres gives it (there isn't special handling for
1-row results). It does, however, use a different (newer) protocol to
talk to postgres than psql, and this can cause subtle differences in
the behaviour (mainly it's stricter about types).

What version of postgres are you using?

--
Andrew McNamara, Senior Developer, Object Craft
http://www.object-craft.com.au/

[kerby2000]

Hi Andrew,

Thank you for you prompt answer.
I think I realized what was the problem
Apparently I misunderstood how I should use fetchmany and fetchall

I was trying to get results using fetchmany. Here is what I've got wit
your example

fetchmany case
>>> db = ocpgdb.connect( database = "postgres", user = "postgres", password = "postgres", host = "localhost")

>>> c=db.cursor()
>>> c.execute('select max(id) as id from settings where id < 4 group by typeid')

<ocpgdb.connection.Cursor instance at 0xb7880a0c>
>>> c.fetchmany()
[(2,)]

fetchall case
>>> db = ocpgdb.connect( database = "postgres", user = "postgres", password = "postgres", host = "localhost")

>>> c=db.cursor()
>>> c.execute('select max(id) as id from settings where id < 4 group by typeid')

<ocpgdb.connection.Cursor instance at 0xb7d3f10c>
>>> c.fetchall()
[(2,), (3,)]

Do they have to produce the same result?

Kind regards,

Sergey






On Sep 24, 12:41 am, Andrew McNamara <andr...@object-craft.com.au>
wrote:

[Andrew McNamara]

>Thank you for you prompt answer.
>I think I realized what was the problem
>Apparently I misunderstood how I should use fetchmany and fetchall
>
>I was trying to get results using fetchmany. Here is what I've got wit
>your example
>
>fetchmany case
>>>> db = ocpgdb.connect( database = "postgres", user = "postgres", password = "postgres", host = "localhost")
>>>> c=db.cursor()
>>>> c.execute('select max(id) as id from settings where id < 4 group by typeid')
><ocpgdb.connection.Cursor instance at 0xb7880a0c>
>>>> c.fetchmany()
>[(2,)]
>
>fetchall case
>>>> db = ocpgdb.connect( database = "postgres", user = "postgres", password = "postgres", host = "localhost")
>>>> c=db.cursor()
>>>> c.execute('select max(id) as id from settings where id < 4 group by typeid')
><ocpgdb.connection.Cursor instance at 0xb7d3f10c>
>>>> c.fetchall()
>[(2,), (3,)]
>
>Do they have to produce the same result?

Fetchmany returns cursor.arraysize rows each time it's called, and
cursor.arraysize is 1 by default (curious, but true - see the dbapi
specs in PEP 249). If you called it a second time, you would get [(3,)].

Typically, you use fetchmany in a loop like:

while True:
rows = c.fetchmany(100)
if not rows:
break
for row in rows:
do stuff

(Note I'm overriding the cursor.arraysize default in the fetchmany
method call).