Get psycopg2 count(*) number of results

Question

Whats 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}

Thanks.

`type(r)` will **not** return as `{'count': 0L}`. Most likely it'll print `` or similar. — Martijn Pieters, Oct 04 '13 at 22:35
type(r) returning {'count': 0L} was directly from the console. — Matt, Oct 05 '13 at 03:47

Martijn Pieters · Accepted Answer · 2013-10-04T23:02:23.533

47

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]

edited Oct 04 '13 at 23:02

answered Oct 04 '13 at 22:35

Martijn Pieters

1,048,767
296
4,058
3,343

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? – BertC Dec 20 '21 at 13:43
1

@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. – Martijn Pieters Jan 19 '22 at 21:32

score 0 · Answer 2 · answered Sep 23 '22 at 13:51

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)

alejandro · Answer 3 · 2018-11-15T07:15:53.460

-4

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.

edited Nov 15 '18 at 07:15

answered May 31 '17 at 01:27

alejandro

794
7
9

1

I know this is old but surely it will always return 1 row? so len(rows) will always be 1? – David Waterworth Nov 13 '18 at 23:36
2

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 – alejandro Nov 15 '18 at 07:11
@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 – David Waterworth Nov 15 '18 at 22:16
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. – alejandro Nov 17 '18 at 00:03

Get psycopg2 count(*) number of results

3 Answers3