Auto-update MYSQL table with JSON using python

Question

I have a JSON

{
"data": {
    "message": { “success” }
    "user": {
        "id": "1234",
        "playlist": [
            [
                "title",
                "album",
                "artist",
                "info"
            ],
            [
                "title",
                "album",
                "artist",
                "info"
            ]
        ]
    }
}

}

I want to fill MYSQL tables using the same. However i am a novice at python thus not sure how to do the same. How should i make sure that MYSQL tables are auto-updated when the JSON hits the server.

what i have tried is

db.execute("INSERT INTO json_col VALUES %s", title,album,artist,info)

are you trying to store json data as a whole in your table or update individual tables and fields in your database based on the json values? — Gil
– Gil, Commented May 20, 2015 at 11:50
You could use django + django rest famework and add a ModelViewSet to create an interface for this purpose. The benefit will be limited as you only store JSON in a column, without any intelligence. Other databases with JSON support may be more suitable. — Freek Wiekmeijer
– Freek Wiekmeijer, Commented May 20, 2015 at 11:52
Parse the values with python in order to insert the value for the corresponding column key, but you need to specify what value to what column, and your schema needs to match this. — Jared
– Jared, Commented May 20, 2015 at 12:45
@ Gil i am trying to update individual tables and fields in your database based on the json values. — vedami
– vedami, Commented May 21, 2015 at 6:07

konart · Accepted Answer · 2015-05-20 13:37:59Z

1

I guess you are trying to insert your playlist values into corresponding fields in a table:

for list in dict['data']['user']['playlist']:
    db.execute("INSERT INTO json_col (title, album, artist, info) VALUES (%s, %s, %s, %s)" % 
        (list[0], list[1], list[2], list[3]))
db.commit()

edited May 20, 2015 at 13:37

answered May 20, 2015 at 13:31

konart

1,8541 gold badge15 silver badges19 bronze badges

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

2 Comments

vedami Over a year ago

@ konart: thats precisely what i am trying to do. add playlist values from json to my tables.

konart Over a year ago

@vedami So... was my answer helpful than?)

Collectives™ on Stack Overflow

Auto-update MYSQL table with JSON using python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related