8

I try to use RealDictCursor:

cur = conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor)
cur.execute('SELECT * FROM items')
res = cur.fetchall()
print(res)
print(type(res[0]))

But it doesn't work. Result:

[RealDictRow([('id', 1), ('name', 'apple')]), RealDictRow([('id', 2), ('name', 'pen')])]
<class 'psycopg2.extras.RealDictRow'>

I need a dictonary, output like this:

[{"id": 1, "name": "apple"}, {"id": 2, "name": "pen"}]
<class 'dict'>

Yes, I know that I can to make dict with cycle for. But I have the table with 10000 rows and I need to show 10000 items fast. (I think that cycle for isn't very fast to solve my problem. Is it true? Can you give me a advice to solve my problem very fast with a minimum amount of time)

How can I get it?

PS: I need it for API service by Flask so after this I need to return it like this:

return jsonify({my_dictonary_sql_query})
Vlad
  • 87
  • 1
  • 1
  • 6

1 Answers1

5

You're making an assumption from the printed humanized representation of your retrieved data, internally it is the dictionary:

import json
#
return json.dumps(cur.fetchall())
ipaleka
  • 3,745
  • 2
  • 13
  • 33
  • 3
    To be totally clear, according to the [docs](http://initd.org/psycopg/docs/extras.html#real-dictionary-cursor), `RealDictRow` is a subclass of `dict`. – alkasm Jul 25 '19 at 20:42
  • Thanks for answer, but can you give me a advice to solve my problem very fast with a minimum amount of time? Is `json.dumps(cur.fetchall())` faster then cycle **for**? – Vlad Jul 25 '19 at 20:48
  • If you use well known shortcuts, in Python you may say that smaller code without involving extra variables is faster. – ipaleka Jul 25 '19 at 21:03
  • Internally this may be a dictionary, but it doesn't serialize cleanly like a dictionary without converting it first, so wanting a regular dict is valid, despite if it walk, talks, and acts like a dict otherwise. The RealDictRow text is included in the text serialization when naively serializing (e.g. using airflow's xcom_push) – Brendan Jun 20 '21 at 23:33
  • 2
    Also, not all fields returned by Postgres will be naively serializable with json.dumps. Datetime fields as an example. You can either stringily these by default with json.dumps(cur.fetchall(), default=str) or add your own encoder (see: https://docs.python.org/3/library/json.html#json.JSONEncoder) – Brendan Jun 21 '21 at 18:32