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.
Consider two tables,
book with the following schema.
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.
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
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, None)
Here is a relevant issue in Pyscopg2.
- Build Plugins with Pluggy
- Render local images in datasette using datasette-render-local-images
- Parameterize Python Tests
- “Don’t touch your face” - Neural Network will warn you
- Capture all browser HTTP[s] calls to load a web page
This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.