CODEWITHSUNDEEP
pythonpostgresqlcursorpsycopg2
user3516773
user3516773

Reputation: 23

psycopg2 each execute yields different table and cursor not scrolling to the beginning

I am querying a postgresql database through python's psycopg2 package.

In short: The problem is psycopg2.fetchmany() yields a different table everytime I run a psydopg2.cursor.execute() command. 

import psycopg2 as ps
conn = ps.connect(database='database', user='user')
nlines = 1000
tab = "some_table" 
statement= """ SELECT * FROM """ + tab + " LIMIT %d;" %(nlines)
crs.execute(statement)

then I fetch the data in pieces. Running the following executes just fine and each time when I scroll back to the beginning I get the same results. 

rec=crs.fetchmany(10)
crs.scroll(0, mode='absolute')
print rec[-1][-2]

However, if I run the crs.execute(statement) again and then fetch the data, it yields a completely different output. I tried running ps.connect again, do conn.rollback(), conn.reset(), crs.close() and nothing ever resulted in consisted output from the table. I also tried a named cursor with scrollable enabled

crs= conn.cursor(name="cur1")
crs.scrollable=1
...
crs.scroll(0, mode= 'absolute')

still no luck.

Upvotes: 1

Views: 751

Answers (1)

khampson
khampson

Reputation: 15306

You don't have any ORDER BY clause in your query, and Postgres does not guarantee any particular ordering without one. It's particularly likely to change ordering for tables which have lots of churn (i.e. lots of inserts/updates/deletes).

See the Postgres SELECT doc for more details, but the most salient snippet here is this:

If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

I wouldn't expect any query, regardless of the type of cursor used, to necessarily return the exact same result set given a query of this type.

What happens when you add an explicit ORDER BY?

Upvotes: 1

Related Questions