python - 从JSON将多种食物写入DB

我目前在将食物从JSON写入数据库的过程中遇到重大问题。 GET方法可以正常工作。但是，当我收到JSON时，它无法正确写入数据库。

我要做的是以下


将新的餐点写入数据库（通过自动增量创建新的餐点/购物车ID）
利用LAST_INSERT_ID（）命令向该餐/小车中添加新食物。


一旦到达这里，编写一种食物就可以了，但是，如果有很多食物，我似乎就无法将其他食物从JSON写入数据库。

我收到的JSON如下：

Endpoint: /meallog
Request params:{
                 method: ”post”,
                 headers: headers,
                 url: string,
                 data:{
                       userId: string,
                       date: string,
                       mealData:{
                                 mealName: String,
                                 food: [
                                        {
                                         id:string,
                                         foodname:string,
                                         numCal:int,
                                         servingSize:int,
                                         servingSizeUnit:string,
                                         totalCalories:int
                                        },
                                        {
                                         (repeat above)
                                        }
                                       ]
                                 }

                       }
}
Response: (JSON Object)
{
     code: 200/400,
     message: String
}

我目前在我的帖子中使用的代码如下：

elif request.method == 'POST':
    jsondata = {}
    code={}

    user_id = request.json['user_id']
    date = request.json['date']
    mealName = request.json['mealName']
    food_id = request.json['id']
    food_name = request.json['foodname']
    food_cal = request.json['numCal']
    serving_size = request.json['servingSize']
    serving_unit = request.json['servingSizeUnit']
    totalCal = request.json['totalCalories']

    postmeal = conn.cursor()
    INS_meal = "INSERT INTO user_cart (user_id, datetime, cart_cal, cart_name) VALUES (%s, %s, %s, %s);"
    postmeal.execute(INS_meal, (user_id, date, totalCal, mealName))
    conn.commit()

    postfood = conn.cursor()
    INS_food = "INSERT INTO food_log VALUES (LAST_INSERT_ID(), %s, %s, %s, %s, %s);"
    postfood.execute(INS_food, (food_id, food_name, food_cal, serving_size, serving_unit))
    conn.commit()

    if postfood.execute and postmeal.execute:
        code['code'] = '200'
        code['message'] = 'Success! INSERTED values into both food_log and user_cart'
    else:
        code['code'] = '400'
        code['message'] = 'Error Connecting to DB. Cant insert into food_log and-or user_cart'

    jsondata['code'] = code['code']
    jsondata['message'] = code['message']

    return(json.dumps(jsondata))

本质上，我希望它收集进来的所有食物，并将数组中的整个食物对象无缝地写入食物表。

最佳答案

我解决了问题，并以更有条理的方式利用了LAST_INSERT_ID（），并正确地循环了进来的食物。

elif request.method == 'POST':
    jsondata = {}
    code={}

    #with open('inc.json') as json_data:
        #d = json.load(json_data)
        #print(d)
    user_id = request.json['userId']
    date = request.json['date']

    mealName = request.json['mealData']['mealName']

    foodlist = request.json['mealData']['food']

    postmeal = conn.cursor()
    INS_meal = "INSERT INTO user_cart (user_id, datetime, cart_name) VALUES (%s, %s, %s);"
    postmeal.execute(INS_meal, (user_id, date, mealName))
    conn.commit()
    postmeal.execute("select LAST_INSERT_ID();")
    SEL_MEAL_ID = postmeal.fetchone()[0]

    food_str = ""
    for food in foodlist:
        print (food)
        food_str+="({}, '{}', '{}', {}, {}, '{}', {}), ".format(SEL_MEAL_ID, food['id'], food['foodname'], food['numCal'], food['servingSize'], food['servingSizeUnit'], food['totalCalories'])

    postfood = conn.cursor()
    INS_food = "INSERT INTO food_log VALUES {};".format(food_str[:-2]) # To exclude the last comma in the food string
    postfood.execute(INS_food)
    conn.commit()

    if postfood.execute and postmeal.execute:
        code['code'] = '200'
        code['message'] = 'Success! INSERTED values into both food_log and user_cart'
    else:
        code['code'] = '400'
        code['message'] = 'Error Connecting to DB. Cant insert into food_log and-or user_cart'

    jsondata['code'] = code['code']
    jsondata['message'] = code['message']

    return(json.dumps(jsondata))