CODEWITHSUNDEEP
pythonmysqljson
edmamerto
edmamerto

Reputation: 8165

Python converting mysql query result to json

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"
}

Upvotes: 35

Views: 82512

Answers (6)

NonCreature0714
NonCreature0714

Reputation: 6024

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.

Upvotes: 24

Hashan Shalitha
Hashan Shalitha

Reputation: 875

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)

Upvotes: 4

Milovan Tomašević
Milovan Tomašević

Reputation: 8683

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()

Upvotes: 0

Nishit Doshi
Nishit Doshi

Reputation: 51

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.

Upvotes: 1

Mani
Mani

Reputation: 5648

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.

Upvotes: 67

Mike Tung
Mike Tung

Reputation: 4831

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)

Upvotes: 11

Related Questions