Inserting complex json object into a sql schema

Question

I have a json object of the form

{   
    "483329": 
    {"Dairy_free": false,
     "Gluten_free": true,
     "Vegetarian": false,
     "Vegan": false, 
     "ketagonic": false},

     "577539": 
     {"Dairy_free": true,
      "Gluten_free": false, 
      "Vegetarian": true, 
      "Vegan": true, 
      "ketagonic": false}
}

Where the schema is

    | id | Dairy_free | Gluten_free | Vegetarian | Vegan|  ketagonic|
    +----+------------+-------------+------------+------+-----------+

Is there a way without re-generating the json to insert the json object to mysql, using mysqlcursors?

sql_insert = INSERT INTO allergies VALUES (Default, %s %s %s %s %s);"
cursor = config.cursor
for obj in json_objects <---- this is where I need to transfer to suitable object
try:
    affected_count = cursor.execute(sql_insert, (object))
    config.dbconnection.commit()
except Exception as e:
    print e

what interval is the JSON data being pulled at. Are you making any get requests? — Sumanth M
– Sumanth M, Commented Dec 30, 2018 at 21:29

WeiYuan · Accepted Answer · 2018-12-30 21:48:07Z

1

It can use executemany followed from MySQLdb User's Guide

data = list(map(lambda x: tuple(x.values()), json_objects.values()))
# [(False, True, False, False, False), (True, False, True, True, False)]

c.executemany(
    """INSERT INTO allergies VALUES (Default, %s, %s, %s, %s, %s)""",
    data
)

answered Dec 30, 2018 at 21:48

WeiYuan

5,9922 gold badges19 silver badges23 bronze badges

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

Collectives™ on Stack Overflow

Inserting complex json object into a sql schema

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