CODEWITHSUNDEEP
pythonmysqljson
user5740843
user5740843

Reputation: 1620

Getting a list from MySQL to JSON format with Python

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!

Upvotes: 0

Views: 466

Answers (1)

Daniel Roseman
Daniel Roseman

Reputation: 600059

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

Upvotes: 1

Related Questions