Converting result to json in python flask

Question

I am creating apis in python flask. I am using MySQL for database and got data successfully using SELECT query. I have returned the data in json format using jsonify of flask. What i want to ask is, I am getting json in following format after jsonify that uses integer as index

[
 [
  1, 
  "FURNITURE", 
  "INDOOR FURNITURE"
 ], 
 [
  2, 
  "AUTOMOBILES", 
  "CARS, BIKES"
 ], 
 [
  3, 
  "LAPTOP & ACCESSORIES", 
  "LAPTOP, MOUSE, KEY BOARD, PENDRIVE"
 ]
]

It is fine and i can work with this but I am looking for json that has database table attribute as its index like we can get in nodejs so it will be easier to work in font end. I want to have json in following format where index are my database table column name.

[
 { "CATEGORY_ID":1,
   "CATEGORY_NAME":"FURNITURE",
   "DESCRIPTION":"INDOOR FURNITURE"
 },
 { "CATEGORY_ID":2,
   "CATEGORY_NAME":"AUTOMOBILES",
   "DESCRIPTION":"CARS, BIKES"
 },
 { "CATEGORY_ID":3,
   "CATEGORY_NAME":"LAPTOP & ACCESSORIES",
   "DESCRIPTION":"LAPTOP, MOUSE, KEY BOARD, PENDRIVE"
 }
]

user3327034 · Accepted Answer · 2019-02-01 07:26:18Z

4

import pymysql
cur = mysql.connect().cursor(pymysql.cursors.DictCursor)
cur.execute(sql)
row = cur.fetchall()
print row

[{u'symbol': u'AAPL'}, {u'symbol': u'SQ'}]

answered Feb 1, 2019 at 7:26

user3327034

4055 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Elliot B. · Accepted Answer · 2018-12-24 22:32:11Z

3

The question doesn't specify your SQL adapter, but if you're using SQLAlchemy with Flask, here's an example of how to query a database and output the result in your desired format:

from flask import Flask
from models import db
import json

app = Flask(__name__)

app.config['SQLALCHEMY_DATABASE_URI'] = 'your_database_connection_string'
db.init_app(app)

result = db.session.execute("SELECT * FROM categories WHERE id = :id", {"id":999})

# If no rows were returned (e.g., an UPDATE or DELETE), return an empty list
if result.returns_rows == False:
    response = []

# Convert the response to a plain list of dicts
else:
    response = [dict(row.items()) for row in result]

# Output the query result as JSON
print(json.dumps(response))

edited Dec 24, 2018 at 22:32

answered Dec 24, 2018 at 21:51

Elliot B.

17.7k11 gold badges85 silver badges103 bronze badges

1 Comment

Lama Madan Over a year ago

I am using Flask-MySQL

sahasrara62 · Accepted Answer · 2018-12-25 01:20:01Z

1

i find solution with sql-alchemy better approach. still if want the solution as per problem statement then , here is a solution

import json 

def conv_func(list_data):
    dic ={ "CATEGORY_ID":list_data[0],
          "CATEGORY_NAME":list_data[1],
          "DESCRIPTION":list_data[2]
          }
    return dic


data = '''[[
  1, 
  "FURNITURE", 
  "INDOOR FURNITURE"
 ], 
 [
  2, 
  "AUTOMOBILES", 
  "CARS, BIKES"
 ], 
 [
  3, 
  "LAPTOP & ACCESSORIES", 
  "LAPTOP, MOUSE, KEY BOARD, PENDRIVE"
 ]
]'''

data = json.loads(data)



new_data=[]
for i in data:
        new_data.append(conv_func(i))

print(new_data)

answered Dec 25, 2018 at 1:20

sahasrara62

11.4k3 gold badges35 silver badges48 bronze badges

Collectives™ on Stack Overflow

Converting result to json in python flask

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related