Assign value from mysql into dict using python

Question

If I am only returning one row from my mysql query and I know the headers can I assign those values via dict in python? I do have some data that is nested. The data will eventually be returned as a JSON object.

Question: What is the correct syntax to assign those mysql values into my python array things and return that object as a json string?

For example my JSON would need to look something like you will notice the relative data in the query is nested for the school and address:

{
    “data”:[
        {
            “player”:
                {
                “id”:xxx-xxx-xxx, 
                “name”: “name1”, 
                “school”: 
                    {
                        “id”:xxxx, 
                        “address”:
                            {
                                “id”:xxx, 
                                “city”: “mycity”, 
                                “state”: “OH”
                            }, 

                        “name”: “myschool”, 
                        “mascot”: “wildcats”
                    }, 
                “height”: 72, 
                “weight”: 180
        },
    ]
}

And I want to be able to assign values similar to:

 cur.execute("select  pla.id, pla.name, sch.id, address.id, address.city, address.state, sch.name, sch.mascot, pla.height, pla.weight FROM Players pla LEFT JOIN SchoolData sch ON pla.School = sch.ID  LEFT JOIN LocationData address ON sch.Location = address.ID where pla.ID = %d"  %int(student_id))

        things = []
for row in cur.fetchall():
        things.append(dict([
                {"data":[
                        {
                        "player":
                                {
                                "id":row[0],
                                "name": row[1],
                                "school":
                                {
                                        "id":row[2],
                                        "address":
                                        {
                                                "id":row[3],
                                                "city": row[4],
                                                "state": row[5]
                                        },

                                        "name": row[6],
                                        "mascot": row[7]
                                },
                                "height": row[8],
                                "weight": row[9]
                        },
                ]
                }
        ]))

        return (jsonify({'players:' : things})

Sorry @AlanHoover for not being clear I have updated my questions with a specific request. — Denoteone
– Denoteone, Commented Apr 9, 2018 at 17:38
double that all of your opening and closing [],{}, and () match up up to that point — Alan Hoover
– Alan Hoover, Commented Apr 9, 2018 at 19:48

hunteke · Accepted Answer · 2018-04-09 19:01:29Z

1

The posted code has some syntax errors, addressed below. However, to answer your question, yes. You want the DictCursor:

cur = conn.cursor( MySQLdb.cursors.DictCursor )
cur.execute('SELECT id, name FROM Players')
for row in cur.fetchall():
    print("{id}: {name}".format( row ))

Meanwhile, the posted code would work just fine, provided the syntax errors are corrected: the code is missing both a closing brace (}) and a closing bracket (]). After that, you'll like encounter a missing key problem with the dict().

things.append(dict([...

A dictionary is defined by key-value pairs. The code supplies an apparent value, but no key. Consider:

things.append(dict(mydata=[...

Or, eschew the extra lookup and function call by utilizing the language intrinsic:

things.append( {'mydata': [...

edited Apr 9, 2018 at 19:01

answered Apr 9, 2018 at 18:38

hunteke

3,7161 gold badge10 silver badges18 bronze badges

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

1 Comment

Denoteone Over a year ago

Thanks @hunteke for the recommendation. I removed the dict and just assigned the values from row into my append. The order of my data is a little wonky but that is a different story.

Collectives™ on Stack Overflow

Assign value from mysql into dict using python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related