Assign value from mysql into dict using python

177
April 10, 2018, at 09:08 AM

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})
Answer 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': [...
Rent Charter Buses Company
READ ALSO
code Igniter Failover Array

code Igniter Failover Array

I'm not needing help with failover connection, just what might be wrong with this failover array? I have tested the alternate db connection and works greatIssue is when I turn off primary, failover does not connect as it should

139
Setting php generated html table in parts side by side

Setting php generated html table in parts side by side

I made this code to extract data from mysql and bring the results on the pageThe results that the code generated goes down the page

164
Possible to do a MySQL foreign key to one of two possible tables?

Possible to do a MySQL foreign key to one of two possible tables?

Well here's my problem I have three tables; regions, countries, statesCountries can be inside of regions, states can be inside of regions

134
Delete a row after 1 month or 3 month based on selected validity

Delete a row after 1 month or 3 month based on selected validity

I want to delete old records in my database that stores an entry from an HTML form which consists of

142