Fetch result from postgres and convert it to json in python

Question

I have to fetch output from postgres db and send as output as JSON when a request is made from Postman My code is

import psycopg2

conn = psycopg2.connect(host="localhost", port="5000", dbname="stores", password="*****", user="postgres")
cursor = conn.cursor()

def check():
    db = conn.cursor()
    db.execute("select * from store1 as name;")
    dbop = db.fetchall()
    jop = dict(dbop)
    return jop


print(check())

My DB structure:

 name   | price

--------+-------

 chair  | 10.12

and the output of above python code is:

{'chair': 10.12}

But i need output as,

{
"name" : "chair",
"price" : 10.12
}

Marek Piotrowski · Accepted Answer · 2020-08-13 08:25:38Z

As per http://zetcode.com/python/psycopg2/ fetchall() fetches all the (remaining) rows of a query result, returning them as a list of tuples.

$ fetch_all.py
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600

You could either do the mapping yourself (create list of dictionaries out of the tuples by explicitly naming the columns in the source code) or use dictionary cursor (example from aforementioned tutorial):

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import psycopg2
import psycopg2.extras

con = psycopg2.connect(database='testdb', user='postgres',
                    password='s$cret')

with con:

    cursor = con.cursor(cursor_factory=psycopg2.extras.DictCursor) # <-- notice the factory
    cursor.execute("SELECT * FROM cars")

    rows = cursor.fetchall()

    for row in rows:
        print(f"{row['id']} {row['name']} {row['price']}")

Eduardo Moya · Accepted Answer · 2020-08-13 08:54:19Z

1

I don't have enough information about the dbop variable data type so you could use the Adapter Design Pattern like this:

import psycopg2

conn = psycopg2.connect(host="localhost", port="5000", dbname="stores", password="*****", user="postgres")
cursor = conn.cursor()

def checkAdapter(queryResult):
    name = list(queryResult)[0]
    return {
        "name": name,
        "price": queryResult[name]
    }

def check():
    db = conn.cursor()
    db.execute("select * from store1 as name;")
    dbop = db.fetchall()
    jop = dict(dbop)
    return checkAdapter(jop)


print(check())

Edit

As Marek Piotrowskim states, there are more efficient solutions and I strongly recommend you to implement them.

edited Aug 13, 2020 at 8:54

answered Aug 13, 2020 at 8:39

Eduardo Moya

264 bronze badges

Collectives™ on Stack Overflow

Fetch result from postgres and convert it to json in python

2 Answers 2

Comments

Comments

Your Answer

Collectives™ on Stack Overflow

Fetch result from postgres and convert it to json in python

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest