CODEWITHSUNDEEP
pythonsqljson
squirrat
squirrat

Reputation: 405

building json data from sql database cursor

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

Upvotes: 0

Views: 2205

Answers (3)

Ehud Halamish
Ehud Halamish

Reputation: 174

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

Upvotes: 1

squirrat
squirrat

Reputation: 405

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

Upvotes: 0

CentAu
CentAu

Reputation: 11200

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

Upvotes: 1

Related Questions