Formatting select list from MySQLdb in Python to JSON

Question

I'm in a small dilemma. I'm using Python's version 2.7 module MySQLdb to grab a list from a table. The code is fairly simple thus far:

#!/usr/bin/python
import json
import MySQLdb

db_host = "localhost"
db_user = "xxx"
db_passwd = "yyy"
db_table = "table"

try:
        db = MySQLdb.connect(host=db_host, user=db_user, passwd=db_passwd, db=db_table)
        cursor = db.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute("""SELECT serial, registered_id FROM devices WHERE registered_id IS NOT NULL AND registered_id <>''""")
        devices = cursor.fetchall()
        print devices
except:
        print "Something went wrong with the MySQL"

Printing this comes out as:

(('00000000762c1d3c', '019'), ('000000003ad192f2', '045'),('000000004c9898aa', '027'))

(I shortened it down because it was quite lengthy.)

How do I get this to list to be parsed correctly into JSON so that it looks like:

{
            "device":
                [
                    { "serial": "00000000762c1d3c", "registered_id": "019" },
                    { "serial": "000000003ad192f2", "registered_id": "045" },
                    { "serial": "000000004c9898aa", "registered_id": "027" },
                ]
        }

I have figured out that by adding this in corespondence with the DictCursors:

for row in devices:
        print "%s, %s" % (row["serial"], row["registered_id"])

I'm able to print them each. But I'm still not able to figure out how to structure the JSON properly.

Thank you for your suggestions!

alecxe · Accepted Answer · 2017-01-07 23:25:49Z

1

A DictCursor might make things much simpler:

import json

db = MySQLdb.connect(host=db_host, 
                     user=db_user, 
                     passwd=db_passwd, 
                     db=db_table, 
                     cursorclass=MySQLdb.cursors.DictCursor)
cursor = db.cursor()
cursor.execute("""SELECT serial, registered_id FROM devices WHERE registered_id IS NOT NULL AND registered_id <>''""")
devices = cursor.fetchall()

data = {"device": list(devices)}

# dump to a json file
with open("output.json", "w") as f:
    json.dump(data, f)

answered Jan 7, 2017 at 23:25

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

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

Collectives™ on Stack Overflow

Formatting select list from MySQLdb in Python to JSON

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related