python - 将多种食物从 JSON 写入数据库

Question

我目前在将食物从 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))

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

Answer 1

我解决了问题，并以更结构化的方式利用了 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))