CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
bqui56
bqui56

Reputation: 2111

psycopg2 not returning results

I am trying to use psycopg2 with my postgresql database just running on my local machine can't get it to return results no matter what I try. It seems to connect to the database ok, since if I alter any of the config parameters it throws errors, however, when I run seemingly valid and result worthy queries, I get nothing.

My db is running and definitely has a table in it:

postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=# select * from foos;
  name   | age 
---------+-----
 Sarah   |  23
 Michael |  35
 Alice   |  12
 James   |  20
 John    |  52
(5 rows)

My python code connects to this database but no matter what query I run, I get None:

Python 2.7.3 (default, Apr 10 2013, 06:20:15) 
[GCC 4.6.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>> conn = psycopg2.connect("dbname='postgres' user='postgres' host='localhost'")
>>> cur = conn.cursor()
>>> print cur.execute("select * from foos;")
None
>>> print cur.execute("select * from foos")
None
>>> print cur.execute("select name from foos")
None
>>> print cur.execute("select f.name from foos f")
None

Am I doing something obviously wrong? How can I start debugging this, I don't know where to start since it connects just fine?

Upvotes: 22

Views: 40735

Answers (5)

AshKatchEm
AshKatchEm

Reputation: 7

In case you were looking for the operation results, such as how many rows were selected/deleted/inserted/etc. you can do something like this:

>>> import psycopg2
>>> conn = psycopg2.connect("dbname='postgres' user='postgres' host='localhost'")
>>> cur = conn.cursor()
>>> cur.execute("select * from foos;")
>>> print(cur.statusresults)

>>> cur.execute("select * from foos")
>>> print(cur.statusresults)

>>> cur.execute("select name from foos")
>>> print(cur.statusresults)

>>> cur.execute("select f.name from foos f")
>>> print(cur.statusresults)

Upvotes: -1

zero323
zero323

Reputation: 330063

cursor.execute prepares and executes query but doesn’t fetch any data so None is expected return type. If you want to retrieve query result you have to use one of the fetch* methods:

print cur.fetchone()

rows_to_fetch = 3
print cur.fetchmany(rows_to_fetch)

print cur.fetchall()

Upvotes: 34

John Powell
John Powell

Reputation: 12571

Note, as it says in the docs: http://initd.org/psycopg/docs/cursor.html "cursor objects are iterable, so, instead of calling explicitly fetchone() in a loop, the object itself can be used"

Therefore, it is just as valid to write:

>>> cur.execute("select foo, bar from foobars")
>>> for foo, bar in cur:
....    print foo, bar

without explicitly calling fetchone(). We pythonistas are supposed to prefer terse code, so long as it doesn't impair understanding and, imho, this feels more natural.

Upvotes: 5

zzzirk
zzzirk

Reputation: 1582

The execute() method of a cursor simply executes the SQL that you pass to it. You then have a couple of options for getting responses from the cursor. You can use the fetchone() method which will return the next result. In the case of the first time you call it you will get the very first result, the second time the second result and so on. The fetchall() method returns all rows and may be used as an iterator.

Examples:

>>> # This is an example of the fetchone() method
>>> cur.execute("select * from foos")
>>> # This call will return the first row 
>>> result = cur.fetchone()
>>> # This call will return the second row
>>> result = cur.fetchone()


>>> # This is an example of the fetchall() method
>>> cur.execute("select * from foos")
>>> results = cur.fetchall()
>>> for r in results:
...     print r
>>> # Now we'll reset the cursor by re-executing the query
>>> cur.execute("select * from foos")
>>> for r in cur.fetchall():
...     print r

Upvotes: 4

user2665694
user2665694

Reputation:

You did not read basic documentation which has perfect examples

http://initd.org/psycopg/docs/cursor.html

>>> cur.execute("SELECT * FROM test WHERE id = %s", (3,))
>>> cur.fetchone()
(3, 42, 'bar')

Upvotes: 3

Related Questions