使用python将MySQL输出为JSON

Question

我试图从Flask API中获取像这样的JSON输出，该API从MySQL数据库获取数据。

[{ 
    'date': '2016-01-01', 
    'total': 17164, 
    'details': [{ 
    'name': 'Project 1', 
    'date': '2016-01-01 12:30:45', 
    'value': 9192 
    }, { 
    'name': 'Project 2', 
    'date': '2016-01-01 13:37:00', 
    'value': 6753 
    }, 
    { 
    'name': 'Project N', 
    'date': '2016-01-01 17:52:41', 
    'value': 1219 
    }] 
}] 

的API定义是这样的：

@app.route('/api/getSalesHeatData', methods=['GET']) 
@cross_origin() # allow all origins all methods. 
def get_Sales_Heat_Data(): 
db = getMysqlConnection() 
print(db) 
try: 
    sqlstr1 = "SELECT InvoiceDateOnly, sum(Quantity) from testing group by InvoiceDateOnly" 
    print(sqlstr1) 
    sqlstr2 = "SELECT InvoiceDateOnly, Description, Quantity from testing" 
    print(sqlstr2) 
    cur1 = db.cursor() 
    cur1.execute(sqlstr1) 
    output_json1 = cur1.fetchall() 
    cur2 = db.cursor() 
    cur2.execute(sqlstr2) 
    output_json2 = cur2.fetchall() 
    user_list = [] 
    for row1 in output_json1: 
     d = collections.OrderedDict() 
     d['date'] = str(row1[0]) 
     d['total'] = row1[1] 
     for row2 in output_json2: 
      if row1[0] == row2[0]: 
       f = collections.OrderedDict() 
       f['desc'] = row2[1] 
       f['date'] = str(row2[0]) 
       f['qty'] = row2[2] 
       d.append(f) 
     user_list.append(d) 
except Exception as e: 
    print("Error in SQL:\n", e) 
finally: 
    db.close() 
return json.dumps(user_list)` 

输出在终端返回一个错误，但显示的状态200而没有数据。请在这方面给我建议。如果这是一个愚蠢的错误，我是新来的python和对不起。

Answer 1

我找到了问题的答案。我将d.append(f)更改为d['details'] = f。