未加星标

Kracekumar Ramaraju: Return Postgres data as JSON in Python

字体大小 | |
[开发(python) 所属分类 开发(python) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

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.

https://gist.github.com/kracekumar/322a2fd5ea09ee952e8a7720fd386184

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

https://gist.github.com/kracekumar/3f4bcdd16d080b5a36436370823e0495

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.

https://gist.github.com/kracekumar/eb9f1009743ccb47df2b3a5f078a4444

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 thedocs.

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 .

https://gist.github.com/kracekumar/2d1d0b468cafa5197f5e21734047c46d

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.

https://gist.github.com/kracekumar/b8a832cd036b54075a2715acf2086d62

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

https://gist.github.com/kracekumar/287178bcb26462a1b34ead4de10f0529

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 .

本文开发(python)相关术语:python基础教程 python多线程 web开发工程师 软件开发工程师 软件开发流程

主题: SQLPython
分页:12
转载请注明
本文标题:Kracekumar Ramaraju: Return Postgres data as JSON in Python
本站链接:http://www.codesec.net/view/529758.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 开发(python) | 评论(0) | 阅读(50)