row_to_json and psycopg2.fetchall() results are lists within a list instead of dictionaries in a list

Question

I use Postgres' row_to_json() function to retrieve data as json objects in order to work with the result like with a python dictionary.

conn = psycopg2.connect("<My_DB_DSN>")
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
query_sql = "SELECT row_to_json(row) FROM (SELECT id, name FROM products) row;" 
cur.execute(query_sql)
results = cur.fetchall()
print(results)

This results to:

 [ [{"id": 1, "name": "Bob"}],
   [{"id": 2, "name": "Susan"}]
 ]

I was expecting this result:

 [ {"id": 1, "name": "Bob"},
   {"id": 2, "name": "Susan"}
 ]

Any idea why I get the first result and how can I fix this?

Running the SQL query in postgres' command line will return the json as expected:

{"id": 1, "name": "Bob"},
{"id": 2, "name": "Susan"}

Answer 1

I think you want RealDictCursor, this returns each row as dict and you dont need to modify your SQL queries:

from psycopg2.extras import RealDictCursor

cur = conn.cursor(cursor_factory=RealDictCursor)
query_sql = "SELECT id, name FROM products where id < 10" 
cur.execute(query_sql)
results = cur.fetchall()
print(results)

Returns:

[{'id': 2L, 'name': 'Foo'}, {'id': 4L, 'name': 'Bar'}, ...]