Getting a list from MySQL to JSON format with Python

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 simple:

#!/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()
        cursor.execute("""SELECT serial 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',), ('000000003ad192f2',), ('00000000ca91760d',), ('000000004c9898aa',))

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

{"devices": ['00000000762c1d3c', '000000003ad192f2', '00000000ca91760d', '000000004c9898aa']}

Thank you for your suggestions!

Note: Triple-quoted strings """...""" are only necessary for multi-line strings and just add needless clutter otherwise. — tadman
– tadman, Commented Jan 7, 2017 at 21:21

Daniel Roseman · Accepted Answer · 2017-01-07 21:17:30Z

1

data = {"devices": [item[0] for item in devices]}
json_data = json.dumps(data)

answered Jan 7, 2017 at 21:17

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

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

Collectives™ on Stack Overflow

Getting a list from MySQL to JSON format with Python

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