Return Postgres data as JSON in Python

Postgres supports JSON and JSONB for a couple of years now. The support for JSON-functions landed in version 9.2. These functions let Postgres server to return JSON serialized data. This is a handy feature. Consider a case; Python client fetches 20 records from Postgres. The client converts the data returned by the server to tuple/dict/proxy. The application or web server converts tuple again back to JSON and sends to the client. The mentioned case is common in a web application. Not all API’s fit in the mentioned. But there is a use case.

Postgres Example

Consider two tables, author and book with the following schema.

Postgres function row_to_json convert a particular row to JSON data. Here is a list of authors in the table.

This is simple, let me show a query with an inner join. The book table contains a foreign key to author table. While returning list of books, including author name in the result is useful.

As you can see the query construction is verbose. The query has an extra select statement compared to a normal query. The idea is simple. First, do an inner join, then select the desired columns, and finally convert to JSON using row_to_json. row_to_json is available since version 9.2. The same functionality can be achieved using other function like json_build_object in 9.4. You can read more about it in the docs.

Python Example

Postgres drivers pyscopg2 and pg8000 handles JSON response, but the result is parsed and returned as a tuple/dictionary. What that means, if you execute raw SQL the returned JSON data is converted to Python dictionary using json.loads. Here is the function that facilitates the conversion in pyscopg2 and pg8000.

The psycopg2 converts returned JSON data to list of tuples with a dictionary.

One way to circumvent the problem is to cast the result as text. The Python drivers don’t parse the text. So the JSON format is preserved.

Carefully view the printed results. The printed result is a list of tuple with a string.

For SQLAlchemy folks here is how you do it

Another way to run SQL statement is to use text function.

The other workaround is to unregister the JSON converter. These two lines should do

import psycopg2.extensions as ext
ext.string_types.pop(ext.JSON.values[0], None)

Here is a relevant issue in Pyscopg2.

See also

• Python Typing Koans
• Model Field - Django ORM Working - Part 2
• Structure - Django ORM Working - Part 1
• jut - render jupyter notebook in the terminal
• Five reasons to use Py.test