Error while inserting data to MySQL from JSON using Flask Rest API

Question

I have one JSON Data and I want to insert it into my MySQL Database.

I have taken one JSON object and loaded it into dictionary and then created a class and inside the class defined one method to insert this data into my MySQL database

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

why can't you use JSON directly for getting data, why do you need json.loads ? — PurushothamC, Jun 06 '19 at 06:45
try use .format () method instead of %d as specified here, https://stackoverflow.com/questions/5082452/string-formatting-vs-format — PurushothamC, Jun 06 '19 at 06:48
thanx for your reply, but it worked, just I needed to replace %d with %s, thanx for your help — Aadhi Verma, Jun 06 '19 at 07:48

LinPy · Accepted Answer · 2019-06-06T06:38:04.153

0

I think you need to convert both your variables and in mysql statement set variable to strings

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

edited Jun 06 '19 at 06:38

answered Jun 06 '19 at 06:20

LinPy

16,987
4
43
57

Error while inserting data to MySQL from JSON using Flask Rest API

1 Answers1