Get psycopg2 count(*) number of results

Question

What is the correct way to get the number or rows returned by this query? I'm specifically looking to see if no results are returned.

sql = 'SELECT count(*) from table WHERE guid = %s;'
data=[guid]
cur.execute(sql,data)
results = cur.fetchone()
for r in results:
  print type(r) # Returns as string {'count': 0L} Or {'count': 1L}

Shirashirah

count will return only 1 row (in this case) – Proportionate

type(r) will not return as {'count': 0L}. Most likely it'll print <type 'dict'> or similar. – Aileen

type(r) returning {'count': 0L} was directly from the console. – Shirashirah

Answer 1

results is itself a row object, in your case (judging by the claimed print output), a dictionary (you probably configured a dict-like cursor subclass); simply access the count key:

result = cur.fetchone()
print result['count']

Because you used .fetchone() only one row is returned, not a list of rows.

If you are not using a dict(-like) row cursor, rows are tuples and the count value is the first value:

result = cur.fetchone()
print result[0]

Aileen

You write: "rows are tuples and the count value is the first value". What is the second value? When I do a count(*), I get "(1,)" back (for a table with count=1). What is the second value in the Tuple? – Kafka

@BertC: there is just one value, unless you change the SELECT list to produce more values. Like SELECT COUNT(*), MIN(some_column) FROM table would produce a single row with two values. – Aileen

Answer 2

This may be helpful to those coming across this thread, here is a way to count all the rows for each table in your database using Python:

total_count = 0

with con.cursor() as cur:
    cur.execute("""SELECT table_name FROM information_schema.tables
           WHERE table_schema = 'public'""")

    for table in cur.fetchall():
        table_name = table[0]
        cur.execute(sql.SQL("SELECT COUNT(*) FROM {table}").format(table=sql.Identifier(table_name)))
        table_count = cur.fetchone()
        result = f'TABLE NAME: {table_name}, COUNT: {table_count}'
        total_count += int(table_count[0])
        print(result)

print(total_count)

Quinquevalent

Answer 3

The following worked for me

cur.execute('select * from table where guid = %s;',[guid])
rows = cur.fetchall()
print 'ResultCount = %d' % len(rows)

Drawback: This will not be very efficient for the DBMS if all you need is the count.

Cote

I know this is old but surely it will always return 1 row? so len(rows) will always be 1? – Diazo

I am using the fetchall function. If I were using the fetchone function, I believe you would be right. EDIT: I see what you mean: the SQL query will always return one row, which means len(rows) will always be 1. I think I should remove the aggregate count function from the query to show what I meant. Thank you – Cote

@alejando either that, or leave the aggregate in the query, use fetchone, and then extract the aggregate from row tuple returned. i.e. cur.execute('select count(*) from table'); row = cur.fetchone(); count = row[0]; this will be much more efficient if the rowset is large – Diazo

Agreed. That's why I mentioned the drawback in my answer. However, if I change it to what you suggest I might as well erase my answer as it would stop contributing anything to anyone that comes to this question. I think some people could benefit from using the fetchall function. – Cote