Converting sql table data to json

Question

I am using flask at server side , I have following api :

class DateTimeEncoder(json.JSONEncoder):
    def default(self, o):
        if isinstance(o, datetime):
            return o.isoformat()

        return json.JSONEncoder.default(self, o)


@app.route('/getTransaction', methods=['GET','POST'])
def getTransaction():

uid = request.form["uid"]
db = my.connect("somehost.com","someuser","somepwd","someDB")
cur = db.cursor()

cur.execute("select * from Transactions where uid='%s'" %(uid))

json_string = json.dumps(cur.fetchall(),cls=DateTimeEncoder)

db.close()
return json.dumps(json_string)

After this i get response as :

http://sairav.pythonanywhere.com/getTransaction

Is there a way i can convert this data in json format with keys(such as name , description , amount , type , id , time for which value you can see in the response)

Required format in json array :

{"data":[
    {"Name":"John", "desc":"Doe","amount":"123.0","type":"credit","uid":"213","time":"12-11-2016 12:23:22"},
    {"Name":"John2", "desc":"Doe","amount":"1234.0","type":"credit","uid":"213","time":"12-11-2016 12:23:22"},
    {"Name":"John3", "desc":"Doe","amount":"1235.0","type":"credit","uid":"213","time":"12-11-2016 12:23:22"},
]}

the default cursor is a row cursor i.e. it sends out `["john", "doe", "123.0", "credit"]`. You could create a `dictcursor` instead, which will emit rows as dictionaries. Otherwise, you can access `cursor.description` like in [this so answer](http://stackoverflow.com/a/5058950/1520594) and zip the row with the field names. Once you have that, you can proceed with json.dumps — algrebe, Nov 18 '16 at 07:04

score 1 · Accepted Answer · edited May 23 '17 at 11:54

Found really helpul answer here which completely solves my purpose , it sets my table column names as the keys for json objects in json array . Thanks to algrebe for the link.

My running code with required json output is :

  @app.route('/getTransaction', methods=['GET','POST'])
  def getTransaction():

  uid = request.form["uid"]
  db = my.connect("somehost.com","someuser","somepwd","someDB")
  cur = db.cursor()

  cur.execute("select * from Transactions where uid='%s'" %(uid))


  **columns = cur.description
  result = [{columns[index][0]:column for index, column in enumerate(value)}   for value in cur.fetchall()]**


  db.close()

  **return json.dumps(result,cls=DateTimeEncoder)**

Converting sql table data to json

1 Answers1