时间戳

首页 > 解决方案 > 如何在 python 中使用单个 json 命令添加多个表条目？

python - 如何在 python 中使用单个 json 命令添加多个表条目？

问题描述

我想使用单个 JSON 输入将多个条目放入表中，但我不知道如何从这里开始。一旦用户声明，我就会到达哪里

[
 {
  "VIN": "kjasdfh",
  "Make": "Toyota",
  "model": "Corolla",
  "Year": 1998
 },
 {
  "VIN": "wqeiryu",
  "Make": "Honda",
  "model": "Civic",
  "Year": 1997
 }
]

我不会让它第一个有自己的条目，而第二个有另一个条目。

 @app.route('/api/addcar', methods = ['POST']) # This is a post method because the user needs to be able to add info
 def adding_stuff():
     request_data = request.get_json() # Gets the info from the table and converts to JSON format
     new_vin = request_data['VIN']
     new_make = request_data['Make']
     new_year = request_data['Year']
     new_color = request_data['Color']
     sql = "INSERT INTO carsTEST (VIN, Make, Year, Color, username) VALUES ('%s', '%s', %s, '%s')" % (new_vin, new_make, new_year, new_color) # This sql statement will then be uploaded to the databse to add a new record
     conn = create_connection()
     execute_query(conn, sql) # This will execute the query
     return 'Post worked'

标签： pythonmysqljson

解决方案

您必须将您的 json 数组转换为模型数组（使用序列化程序或您的框架提供的东西）。或者尝试使用以下内容构建原始 sql：

json_data = [
 {
  "VIN": "kjasdfh",
  "Make": "Toyota",
  "model": "Corolla",
  "Color": "red",
  "Year": 1998
 },
 {
  "VIN": "wqeiryu",
  "Make": "Honda",
  "model": "Civic",
  "Color": "white",
  "Year": 1997
 }
]

sql = "INSERT INTO carsTEST (VIN, Make, Year, Color, username) VALUES"

for jo in json_data:
    new_vin = jo['VIN']
    new_make = jo['Make']
    new_year = jo['Year']
    new_color = jo['Color']
    value_sql = "('{}', '{}', '{}', '{}'),".format(new_vin, new_make, new_year, new_color)
    sql = sql + value_sql

sql = sql.rstrip(',') + ";"

print(sql)

Edit1：该方法应该类似于：

@app.route('/api/addcar', methods=['POST'])
def adding_stuff():
    request_data = request.get_json() # post body must be a json array
    sql = "INSERT INTO carsTEST (VIN, Make, Year, Color) VALUES"
    for jo in request_data:
        new_vin = jo['VIN']
        new_make = jo['Make']
        new_year = jo['Year']
        new_color = jo['Color']
        value_sql = "('{}', '{}', '{}', '{}'),".format(new_vin, new_make, new_year, new_color)
        sql = sql + value_sql
    sql = sql.rstrip(',') + ";"
    conn = create_connection()
    execute_query(conn, sql)  # This will execute the query
    return 'Post worked'

推荐阅读