json_pg_without_cast.py

krace@hotbox /m/u/code> cat json_pg.py 
import psycopg2


def run(stmt):
     cur =  psycopg2.connect(database='test', user='postgres', password='password', host='localhost').cursor()
     cur.execute(stmt)
     result = cur.fetchall()
     print(list(result))


stmt = 'select row_to_json(row) from (select book.id, book.name, book.author_id, author.name as author_name from book inner join author on book.author_id = author.id) row;'
run(stmt)
krace@hotbox /m/u/code> python json_pg.py 
[({'id': 1, 'author_id': 1, 'name': 'War and Peace', 'author_name': 'Leo Tolstoy'},),
({'id': 2, 'author_id': 2, 'name': 'The Trial', 'author_name': 'Kafka'},),
({'id': 3, 'author_id': 2, 'name': 'The metamorphosis', 'author_name': 'Kafka'},)]