Python Converting MySQL Query Result to Json

Python converting mysql query result to json

You can use cursor description to extract row headers:
row_headers=[x[0] for x in cursor.description] after the execute statement. Then you can zip it with the result of sql to produce json data.
So your code will be something like:

from flask import Flask
from flask.ext.mysqldb import MySQL
import json
app = Flask(__name__)
app.config['MYSQL_HOST'] = '127.0.0.1'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'password'
app.config['MYSQL_DB'] = 'hello_db'
mysql = MySQL(app)

@app.route('/hello')
def index():
   cur = mysql.connection.cursor()
   cur.execute('''SELECT * FROM Users WHERE id=1''')
   row_headers=[x[0] for x in cur.description] #this will extract row headers
   rv = cur.fetchall()
   json_data=[]
   for result in rv:
        json_data.append(dict(zip(row_headers,result)))
   return json.dumps(json_data)

if __name__ == '__main__':
   app.run(debug=True)

In the return statement you can use jsonify instead of json.dumps as suggested by RickLan in the comments.

how to get all mysql tuple result and convert to json

Now, in PyMysql, there is a facility to configure your connection to use the cursorClass which by default generates Dictionary as the output. (And thus works directly when returning in the API result as it gets converted to JSON)

From the documentation of PyMysql: Configure your connection as

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

result = cursor.fetchone()
        print(result)

Output for this result :

{'password': 'very-secret', 'id': 1}

return SQL table as JSON in python

Here is a really nice example of a pythonic way to do that:

import json
import psycopg2

def db(database_name='pepe'):
    return psycopg2.connect(database=database_name)

def query_db(query, args=(), one=False):
    cur = db().cursor()
    cur.execute(query, args)
    r = [dict((cur.description[i][0], value) \
               for i, value in enumerate(row)) for row in cur.fetchall()]
    cur.connection.close()
    return (r[0] if r else None) if one else r

my_query = query_db("select * from majorroadstiger limit %s", (3,))

json_output = json.dumps(my_query)

You get an array of JSON objects:

>>> json_output
'[{"divroad": "N", "featcat": null, "countyfp": "001",...

Or with the following:

>>> j2 = query_db("select * from majorroadstiger where fullname= %s limit %s",\
 ("Mission Blvd", 1), one=True)

you get a single JSON object:

>>> j2 = json.dumps(j2)
>>> j2
'{"divroad": "N", "featcat": null, "countyfp": "001",...

Convert SQL into json in Python

Well, if you simply do:

json_string = json.dumps(cursor.fetchall())

you'll get an array of arrays...

[["earning1", "date1"], ["earning2", "date2"], ...]

Another way would be to use:

json_string = json.dumps(dict(cursor.fetchall()))

That will give you a json object with earnings as indexes...

{"earning1": "date1", "earning2": "date2", ...}

If that's not what you want, then you need to specify how you want your result to look...

Converting result to json in python flask

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'}]

---

Related Topics