PyGreSQL is returning unexpected result for a one column select statement

Question

I have the following PostgreSQL database table:

TABLE session_monitor
(
    id int,
    customer_name varchar(150)
)

When I am running the following code:

seq = pg_cur.execute("SELECT id ,customer_name from session_monitor")
for id, customer_name in seq:
    print(id)
    print(customer_name)

it is working fine.

But when I try

pg_cur.execute("SELECT max(id) as max_id from session_monitor")
for max_id in seq:
    print(max_id)

I am not getting a number, but rather: Row(max_id= 5)

I tried a workaround that sort of solves the problem:

seq = pg_cur.execute("SELECT max(id) as max_id,1 as one from session_monitor")
for max_id, one in seq:
    print(max_id)

but it seems kind of lame to use it like this.

What am I doing wrong here - is there a different way to iterate the query result in the second case?

Answer 1

You get this result due to the way how DB API 2 works (it returns tuples for rows from the database), combined with the Python quirk that you need to add a comma when you want to specify a tuple with one element. It has little to do with PyGreSQL.

So, when you first do this, you get a sequence of (named) tuples in seq:

seq = cur.execute("SELECT id, customer_name FROM session_monitor")

When you then do the following, you iterate over that sequence while unpacking the row tuple you get in each iteration to id and customer_name:

for id, customer_name in seq:
    print(id)
    print(customer_name)

When you only select one column, like this, you still get a sequence of (named) tuples, with only one element per tuple:

seq = cur.execute("SELECT max(id) AS max_id FROM session_monitor")

Now you tried to iterate over the sequence like this:

for max_id in seq:
    print(max_id)

The difference to the loop above is that this one does no tuple unpacking. So max_id will be the complete row, which is printed.

To make use of tuple unpacking like above, you need to add a comma after the max_id:

for max_id, in seq:
    print(max_id)

This will print what you expected. Btw, you can also add parens around the tuple unpacking expression. However, you still need a comma if a tuple only has one element.

Of course, if you only get one row with one column, you could just do:

seq = cur.execute("SELECT max(id) AS max_id FROM session_monitor")
max_id = list(seq)[0][0]
print(max_id)

Or, alternatively, using the fetchone() method:

cur.execute("SELECT max(id) as max_id FROM session_monitor")
max_id = cur.fetchone()[0]
print(max_id)

And of course, you can also make use of the fact that you get named tuples:

cur.execute("SELECT max(id) as max_id from session_monitor")
print(cur.fetchone().max_id)

As a side note, using the "classic" PyGreSQL interface instead of DB API 2, you would do this:

q = db.query('SELECT max(id) FROM session_monitor')
print(q.singlescalar())

The single() method gets a single row like fetchone(), and singlescalar() gets the first column of that row.