CODEWITHSUNDEEP
pythonmysqlsql
DsCpp
DsCpp

Reputation: 2489

Inserting complex json object into a sql schema

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

Upvotes: 0

Views: 61

Answers (1)

WeiYuan
WeiYuan

Reputation: 5982

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
)

Upvotes: 1

Related Questions