Postgres Pro
Downloads
Home   >   mailing lists

Thread: Fetching query result problem

Fetching query result problem

From
Jacek Kałucki
Date:
Hi.

I have a problem with some query.
When I issue it from within postgresql console, it returns correct rows count as a result,
but when executed within psycopg2 cursor.execute() row count differs.
In first situation I get 5 rows versus 3 at second time.
This is my query:
select "inventory_line"."iid" as "iid",
         "inventory_line"."il_depot_id" as "il_depot_id",
         "inventory_line"."il_expiry_date" as "il_expiry_date",
         "inventory_line"."il_info" as "il_info",
         "inventory_line"."il_location_x_id" as "il_location_x_id",
         "inventory_line"."il_location_y_id" as "il_location_y_id",
         "inventory_line"."il_location_z_id" as "il_location_z_id",
         "inventory_line"."il_lot_no" as "il_lot_no",
         "inventory_line"."il_package_id" as "il_package_id",
         "inventory_line"."il_pass_id" as "il_pass_id",
         "inventory_line"."il_product_id" as "il_product_id",
         "inventory_line"."il_production_date" as "il_production_date",
         "inventory_line"."il_quantity" as "il_quantity",
         "inventory_line"."il_quantity_spare" as "il_quantity_spare",
         "inventory"."in_close_date" as "in_close_date",
         "inventory"."in_inventory_name" as "in_inventory_name"
     from "inventory_line"
     inner join "inventory_pass" on "inventory_pass"."iid" = "inventory_line"."il_pass_id"
     inner join "inventory" on "inventory"."iid" = "inventory_pass"."ip_inventory_id"
     where "inventory_line"."il_product_id" = 6326 and "inventory"."in_branch_id" = 1 limit 100

Could you give me any suggestions how to figure the reason out?

--
Regards
Jacek Kałucki

Re: Fetching query result problem

From
Daniele Varrazzo
Date:
2012/1/16 Jacek Kałucki <laborm@rz.onet.pl>:
> Hi.
>
> I have a problem with some query.
> When I issue it from within postgresql console, it returns correct rows
> count as a result,
> but when executed within psycopg2 cursor.execute() row count differs.
> In first situation I get 5 rows versus 3 at second time.
[...]
> Could you give me any suggestions how to figure the reason out?

There's no reason why this should happen.

- It could be a different in transactions usage: a query run in psql
is not wrapped in a transaction whereas psycopg has an implicit
"begin" before, and data is not committed until you explicitly commit:
make sure the data you are expecting is visible. Try running the query
in psycopg with autocommit = true to get the same data visibility you
have in psql.

- could you have python data types converted to something unexpected?
Enable server logging to see the query the backend receives, and use
diff to compare the logs and spot differences between what received
from psql and what from psycopg.

- check the connection string: you may be talking to the wrong database.

-- Daniele

Re: Fetching query result problem

From
Jacek Kałucki
Date:
Użytkownik Daniele Varrazzo napisał:
> - check the connection string: you may be talking to the wrong database.

I already figured it out and this was the reason for.
I shouldn't make any coding while sick headache.
Sorry for the noise and thanks for quick reply.
--
Regards
Jacek Kałucki