python - 使用 Flask Rest API 将数据从 JSON 插入 MySQL 时出错

Question

这个问题已经有答案了:

How to use variables in SQL statement in Python? (5 个回答)

已关闭 4 年前。

我有一个 JSON 数据，我想将其插入到我的 MySQL 数据库中。

我获取了一个 JSON 对象并将其加载到字典中，然后创建了一个类，并在该类中定义了一种方法来将此数据插入到我的 MySQL 数据库中

from flask_mysqldb import MySQL
from flask_restful import Resource,Api
import json

app=Flask(__name__)

app.config['MYSQL_HOST']='localhost'
app.config['MYSQL_USER']='root'
app.config['MYSQL_PASSWORD']=''
app.config['MYSQL_DB']='test'

mysql=MySQL(app)
api=Api(app)
person = '{"product_id": 2, "user_id": 901, "rating":5}'
person_dict = json.loads(person)

class SetData(Resource):
    def post(self):
        product_id=person_dict['product_id']
        user_id=person_dict['user_id']
        rating=person_dict['rating']
        cur=mysql.connection.cursor()
        cur.execute("Insert INTO star_rating(product_id,user_id,range) values(%d,%d,%d)",(product_id,user_id,rating))
        mysql.connection.commit()
        return 'Record Inserted Successfully'

api.add_resource(SetData,'/')

if __name__=='__main__':
    app.run(debug=True)
            ```

It was expected that data should be inserted in my MySQL Database but its giving error: 
Error is browser is : 
    "message": "The method is not allowed for the requested URL."
When I checked the command prompt, there were few listed errors :
File "C:\Users\adeep\Desktop\restapi\lib\site-packages\MySQLdb\cursors.py", line 201, in execute
    query = query % args
TypeError: %d format: a number is required, not bytes

During handling of the above exception, another exception occurred:
MySQLdb._exceptions.ProgrammingError: %d format: a number is required, not bytes

Answer 1

我认为你需要将你的变量和mysql语句中的变量设置为字符串

 product_id=str(person_dict['product_id'])
 user_id=str(person_dict['user_id'])
 rating=str(person_dict['rating'])
 .....
 cur.execute("Insert INTO star_rating(product_id,user_id,range) values(%s,%s,%s)",(product_id,user_id,rating))