how to return dictonary or json if i use psycopg2?

Last Updated : 28 Nov, 2021,GATE CS 2021 Syllabus

code: AF
name: Africa
concatenated_column: AFAfrica
code: NA
name: North America
concatenated_column: NANorth America
code: OC
name: Oceania
concatenated_column: OCOceania
code: AN
name: Antartica
concatenated_column: ANAntartica
code: AS
name: Asia
concatenated_column: ASAsia
code: EU
name: Europe
concatenated_column: EUEurope
code: SA
name: South America
concatenated_column: SASouth America

code: AF
name: Africa
concatenated_column: AFAfrica
code: NA
name: North America
concatenated_column: NANorth America
code: OC
name: Oceania
concatenated_column: OCOceania
code: AN
name: Antartica
concatenated_column: ANAntartica
code: AS
name: Asia
concatenated_column: ASAsia
code: EU
name: Europe
concatenated_column: EUEurope
code: SA
name: South America
concatenated_column: SASouth America

Row To Json And Psycopg2 Fetchall Results Are Lists Within A List Instead Of D, This method creates a new psycopg2.extensions.cursor object. Execute the select query using the cursor.execute () method. After successfully executing a Select operation, Use the fetchall () method of a cursor object to get all rows from a query result. it returns a list of rows. , 6 days ago When you have more then one rows you can use the following code result = [r[0] for r in cur.fetchall()] As a quick fix you can return an array: cursor.execute(" ... (row_to_json(t))) from ( select * from table where a = %s and b = %s limit 1000 ) t; """, (a_value, b_value)) As Psycopg adapts Postgresql arrays to Python lists then just get that ... , Python DB API allows us to fetch only a single row. To fetch a single row from a result set we can use cursor.fetchone(). This method returns a single tuple. It can return a none if no rows are available in the resultset. cursor.fetchone() increments the cursor position by one and return the next row. Let see the example now.

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)

You can use register_adapter() to adapt any Python dictionary to JSON, either registering Json or any subclass or factory creating a compatible adapter:,This setting is global though, so it is not compatible with similar adapters such as the one registered by register_hstore(). Any other object supported by JSON can be registered the same way, but this will clobber the default adaptation rule, so be careful to unwanted side effects.,The dict cursors allow to access to the retrieved records using an interface similar to the Python dictionaries instead of the tuples.,In order to pass a Python object to the database as query argument you can use the Json adapter:

>>> dict_cur = conn.cursor(cursor_factory = psycopg2.extras.DictCursor) >>>
   dict_cur.execute("INSERT INTO test (num, data) VALUES(%s, %s)",
      ...(100, "abc'def")) >>>
   dict_cur.execute("SELECT * FROM test") >>>
   rec = dict_cur.fetchone() >>>
   rec['id']
1
   >>>
   rec['num']
100
   >>>
   rec['data']
"abc'def"

>>> rec[2]
"abc'def"

from somewhere
import namedtuple
import collections
collections.namedtuple = namedtuple
from psycopg.extras
import NamedTupleConnection
#...

>>> nt_cur = conn.cursor(cursor_factory = psycopg2.extras.NamedTupleCursor) >>>
   rec = nt_cur.fetchone() >>>
   rec
Record(id = 1, num = 100, data = "abc'def") >>>
   rec[1]
100
   >>>
   rec.data "abc'def"

curs.execute("insert into mytable (jsondata) values (%s)",
   [Json({
      'a': 100
   })])

psycopg2.extensions.register_adapter(dict, psycopg2.extras.Json)

Some further operators also exist only for jsonb, as shown in Table 9-43. Many of these operators can be indexed by jsonb operator classes. For a full description of jsonb containment and existence semantics, see Section 8.14.3. Section 8.14.4 describes how these operators can be used to effectively index jsonb data.,Note: array_to_json and row_to_json have the same behavior as to_json except for offering a pretty-printing option. The behavior described for to_json likewise applies to each individual value converted by the other JSON creation functions.,Table 9-42 shows the operators that are available for use with the two JSON data types (see Section 8.14).,The standard comparison operators shown in Table 9-1 are available for jsonb, but not for json. They follow the ordering rules for B-tree operations outlined at Section 8.14.4.

 key | value
    -- -- - + -- -- -- -
    a | "foo"
 b | "bar"

 key | value
    -- -- - + -- -- -- -
    a | foo
 b | bar

 json_object_keys
 -- -- -- -- -- -- -- -- --
 f1
 f2

 a | b
    -- - + -- -
    1 | 2

 a | b
    -- - + -- -
    1 | 2
 3 | 4

   value
   -- -- -- -- -- -
   1
   true
      [2, false]