21

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?

bqui56
  • 2,091
  • 8
  • 22
  • 40

4 Answers4

32

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()
Community
  • 1
  • 1
zero323
  • 322,348
  • 103
  • 959
  • 935
4

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
zzzirk
  • 1,582
  • 2
  • 14
  • 18
4

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.

John Powell
  • 12,253
  • 6
  • 59
  • 67
  • 1
    This does not seem to work in my case (`None` is returned and I cannot iterate). `fetchall()` works fine but this would be a more pythonic way to do it. – PritishC Feb 26 '15 at 07:23
  • I'm misunderstanding something because cur is not iterable for me either with version 2.8.2 although the docs suggest that it should be. I have to call `fetchone()` or `fetchall()` in order for cur to be anything other than `None`. – Alexander Jun 21 '19 at 16:26
  • My mistake was reassigned cur to a new variable when calling execute. `cur = p.connect(host='h', database='d', user='u').cursor() cur.execute("select * from foo where name = 'bar'")` then cur is iterable: but if you do `q = cur.execute("select * from foo where name = 'bar'")` q is not iterable. – Alexander Jun 24 '19 at 08:29
3

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')
  • TBH putting the examples in the middle of the page and a ton of API reference at the top makes it really easy to overlook... – Adam Hughes Jan 28 '20 at 20:56