Convert the Result set of the SQL query to JSON Object in Python 

Often we land up in a situation where we write a rest service and have to convert the data retrieved from the database to JSON Format . Moreover the below snippet will help you to do the same . The package which i have used to expose the method as service is "Flask" and the package used to access the table data in the MYSQL database in "flask.ext.mysqldb" . 

Moreover the below snippet also contains the logic to convert the JSON data to dataframe and access another rest service to get the data and convert in to dataframes. The package which i have used to convert to dataframes is "panda" and access the rest service URL is "request" .

click here to access the snippet from git hub.

   from flask import Flask,request

from flask.ext.mysqldb import MySQL

import json as jsonInstance

import pandas as pandaInstance

import requests as requestInstance

app = Flask(__name__)

# My SQL Server Configurations

app.config['MYSQL_HOST'] = '127.0.0.1'

app.config['MYSQL_USER'] = 'vignesh'

app.config['MYSQL_PASSWORD'] = 'vignesh'

app.config['MYSQL_DB'] = 'visualization'

mysqlInstance = MySQL(app)

@app.route('/getRecordsAsJSON')

def getRecordsAsJSON():

# Get the input Parameter from the request Object

inputSource = request.args.get('inputSource')

jsonData = {}

if inputSource == "database":

# Create Connection and cursor instance

cursorInstance = mysqlInstance.connection.cursor()

# Prepared statement execution

cursorInstance.execute('''SELECT * FROM IRIS''')

# Extract the row headers / Metadata of the query

rowMetadata=[x[0] for x in cursorInstance.description]

# Fetch all the records

resultset = cursorInstance.fetchall()

json_data=[]

for result in resultset:

json_data.append(dict(zip(rowMetadata,result)))

jsonData = jsonInstance.dumps(json_data)

elif inputSource == "restservice":

# Access a Get URL to get the response

responseInstance = requestInstance.get("http://services.groupkt.com/country/get/all")

print(jsonInstance.loads(responseInstance.content)['RestResponse']['result'])

jsonData = jsonInstance.dumps(jsonInstance.loads(responseInstance.content)['RestResponse']['result'])

#print("The Type of the output data is :: ",jsonData)

# Convert output data to Data frame

print(pandaInstance.read_json(jsonData))

return jsonData

if __name__ == '__main__':

app.run(debug=True)