building json data from sql database cursor

Question

Without knowing the structure of the json, how can I return a json object from the database query? All of the the information is there, I just can't figure out how to build the object.

import MySQLdb
import json
db = MySQLdb.connect( host, user, password, db)
cursor = db.cursor()
cursor.execute( query )
rows = cursor.fetchall()

field_names = [i[0] for i in cursor.description]
json_string = json.dumps( dict(rows) )

print field_names[0]
print field_names[1]
print json_string

db.close()

count

severity

{"321": "7.2", "1": "5.0", "5": "4.3", "7": "6.8", "1447": "9.3", "176": "10.0"}

The json object would look like:

{"data":[{"count":"321","severity":"7.2"},{"count":"1","severity":"5.0"},{"count":"5","severity":"4.3"},{"count":"7","severity":"6.8"},{"count":"1447","severity":"9.3"},{"count":"176","severity":"10.0"}]}

Ehud Halamish · Accepted Answer · 2016-07-28 20:31:26Z

1

The problem you are encountering happens because you only turn the fetched items into dicts, without their description.

dict in python expects either another dict, or an iterable returning two-item tuples, where for each tuple the first item will be the key, and the second the value.

Since you only fetch two columns, you get the first one (count) as key, and the second (severity) as value for each fetched row.

What you want to do is also combine the descriptions, like so:

json_string = json.dumps([
{description: value for description, value in zip(field_names, row)}
for row in rows])

edited Jul 28, 2016 at 20:31

answered Jul 28, 2016 at 20:26

Ehud Halamish

1743 bronze badges

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

Comments

CentAu · Accepted Answer · 2016-07-28 20:17:41Z

1

1- You can use pymsql DictCursor:

import pymysql
connection = pymysql.connect(db="test")
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT ...")
row = cursor.fetchone()
print row["key"]

2- MySQLdb also includes DictCursor that you can use. You need to pass cursorclass=MySQLdb.cursors.DictCursor when making the connection.

import MySQLdb
import MySQLdb.cursors
connection = MySQLdb.connect(db="test",cursorclass=MySQLdb.cursors.DictCursor)
cursor = connection.cursor()
cursor.execute("SELECT ...")
row = cursor.fetchone()
print row["key"]

edited Jul 28, 2016 at 20:17

answered Jul 28, 2016 at 20:11

CentAu

11.3k19 gold badges65 silver badges88 bronze badges

Comments

squirrat · Accepted Answer · 2016-07-28 20:28:03Z

I got this to work using Collections library, although the code is confusing:

import MySQLdb
import json
import collections
db = MySQLdb.connect(host, user, passwd, db)
cursor = db.cursor()
cursor.execute( query )
rows = cursor.fetchall()
field_names = [i[0] for i in cursor.description]

objects_list = []
for row in rows:
    d = collections.OrderedDict()
    d[ field_names[0] ] = row[0]
    d[ field_names[1] ] = row[1]
    objects_list.append(d)

json_string = json.dumps( objects_list )

print json_string

db.close()

[{"count": 176, "severity": "10.0"}, {"count": 1447, "severity": "9.3"}, {"count": 321, "severity": "7.2"}, {"count": 7, "severity": "6.8"}, {"count": 1, "severity": "5.8"}, {"count": 1, "severity": "5.0"}, {"count": 5, "severity": "4.3"}]

Collectives™ on Stack Overflow

building json data from sql database cursor

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related