Btukfyl

I am currently having major issues writing foods from a JSON to my database. The GET method works just fine. However, when I receive a JSON, it is not properly writing to the database.

What I do is the following

• Write a new meal to the database (creates a new meal/cart id via autoincrement)


• Utilize the LAST_INSERT_ID() command to write new foods to that meal/cart.

Once I get here writing one food is fine, however if there is numerous foods, I can't seem to get it to write the other foods from the JSON to the database.

The JSON I am receiving is as follows:

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

}

The Code I currently have for my post is below:

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))

Essentially I'd like it to gather every food that comes in, and write the entire food objects in the array to the food table seamlessly.

I fixed my issue and utilized LAST_INSERT_ID() in a more structured way, as well as to properly loop through the foods incoming.

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))