Python converting mysql query result to json

Question

I'm trying to implement REST APIs and part of it is formatting data into json. I am able to retrieve data from a mysql database, however the object i receive is not what I expect. here is my code

from flask import Flask
from flask.ext.mysqldb import MySQL

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''')
   rv = cur.fetchall()
   return str(rv)

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

Outcome:

((1L, u'my_username', u'my_password'),)

How do I achieve to return a json format like this:

{
 "id":1, 
 "username":"my_username", 
 "password":"my_password"
}

Answer 1

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.

Answer 2

There is, perhaps, a simpler way to do this: return a dictionary and convert it to JSON.

Just pass dictionary=True to the cursor constructor as mentioned in MySQL's documents.

import json
import mysql.connector

db = mysql.connector.connect(host='127.0.0.1',
                             user='admin',
                             passwd='password',
                             db='database',
                             port=3306)

# This is the line that you need
cursor = db.cursor(dictionary=True)

name = "Bob"
cursor.execute("SELECT fname, lname FROM table WHERE fname=%s;", (name))

result = cursor.fetchall()

print(f"json: {json.dumps(result)}")

Which will print -

json: [{'fname': "Bob", 'lname': "Dole"}, {'fname': "Bob", 'lname': "Marley"}]

(Assuming those Bobs are in the table.)

Note that types are preserved this way, a good thing, BUT will need to be transformed, parsed, or serialized into a string; for instance, if there is a date, the SQL query may return a datetime object, which will need to be parsed or serialized depending on your next step. A great way to serialize is in this answer.

Answer 3

From your output it seems like you are getting a tuple back? In which case you should be able to just map it.

from flask import Flask, jsonify
from flask.ext.mysqldb import MySQL

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''')
   rv = cur.fetchall()
   payload = []
   content = {}
   for result in rv:
       content = {'id': result[0], 'username': result[1], 'password': result[2]}
       payload.append(content)
       content = {}
   return jsonify(payload)

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

Answer 4

Here's how I resove this issue by setting app.config['MYSQL_CURSORCLASS'] and using jsonify

from flask import Flask, jsonify
from flask_mysqldb import MySQL

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'
app.config['MYSQL_CURSORCLASS'] = 'DictCursor' # line of code you require

mysql = MySQL(app)

@app.route('/hello')
def index():
   cur = mysql.connection.cursor()
   cur.execute("SELECT * FROM Users WHERE id=1")
   rv = cur.fetchall()
   return jsonify(rv) # use jsonify here

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

Answer 5

For users using Django, you can import JsonResponse from django.http to do your work.

Example code snippet:

from django.http import JsonResponse
from django.db import connection

def home(request):
    with connection.cursor() as cursor:
        cursor.execute("select * from YOUR_TABLE")
        columns = [col[0] for col in cursor.description]
        return JsonResponse([
            dict(zip(columns, row))
            for row in cursor.fetchall()
        ], safe=False)

Note: safe=False has been added to convert the list of dictionaries to Json.

Answer 6

If you want to convert more select query results into JSON files, the simple program code below will do it. For more details, you have three solutions on github.

Given that a lot has already been clarified. Briefly about the methods:

• class DateTimeEncoder(JSONEncoder) - encoder to support datetime - doc.
• get_current_date_time - current time to distinguish if working with variable data
• query_db - Use cursor description to extract row headers, and You get an array of dictionary objects headers:values.
• write_json(query_path) - reading SQL and generating JSON in already created output folder
• convertion_mysql - with glob find all files in a directory with extension .sql and calling the described and defined method write_json

import json
from json import JSONEncoder
import datetime
import os
import glob

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

def get_current_date_time():
    return datetime.datetime.now().strftime('%a_%d.%b.%Y_%H.%M.%S')

def query_db(query):
    cur = mysql.connection.cursor()
    cur.execute(query)
    r = [dict((cur.description[i][0], value) for i, value in enumerate(row)) for row in cur.fetchall()]
    cur.connection.close()
    return r

def write_json(query_path):
    with open(f"{query_path}", 'r') as f:
        sql = f.read().replace('\n', ' ')
    file_name = os.path.splitext(os.path.basename(f"{query_path}"))[0]
    with open(f"../output/{file_name}_{get_current_date_time()}.json", 'w', encoding='utf-8') as f:
        json.dump(query_db(sql), f, ensure_ascii=False, indent=4, cls=DateTimeEncoder)

def convertion_mysql():
    mysql_query = [f for f in glob.glob("../myqls/*.sql")]
    for sql in mysql_query:
        write_json(sql)

if __name__ == "__main__":
    convertion_mysql()