Insert json string into table

Question

I want to insert data into mysql table using json string array in postman. There are two tables in one database. one is for question and another one is for answer. I want to insert question and answer into tables using post request.

Here I have tried a code.

app = Flask(__name__)

api = Api(app)

class train(Resource):

    def post(self):
        account_id = request.json['account_id']
        question = request.json['question']
        answer = request.json['answer']
        question_id = request.json['question_id']
        conn = db_connect.connect()
        query =conn.execute ("INSERT INTO ai_question (account_id,question) 
                VALUES (%s, %s)", (account_id, question))
        query1 =conn.execute ("INSERT INTO ai_answer (question_id, answer) 
                VALUES (%s,%s)", (question_id, answer))
        result1 = {'data': [dict(zip(tuple (query.keys()) ,i)) for i in 
                query.cursor]}
        result2 = {'data': [dict(zip(tuple (query1.keys()) ,i)) for i in 
                query1.cursor]}
        return jsonify(result1, result2)    

api.add_resource(trainings, '/trainings') 
api.add_resource(ask, '/ask')
api.add_resource(train, '/train') 

if __name__ == '__main__':
     app.run('0.0.0.0',5000)

which inserts data into table by writting below json string in postman.

{
    "question":"abc",
    "answer":"xyz",
    "question_id":"1",
    "account_id":"1"
}

but I want to insert data in this way using postman:

{
    "account_id":"11",
    "data": [
        {
            "question":"how are you?", 
            "answer":"I am good how about you?"
        },
        {
            "question":"thank you", 
            "answer":"welcome"
        }
    ]
}

'question' and 'answer' are the columns in different tables.

Have you tried accessing the data array first? I’m sure you would need to do something like `data[0].question` to get the first value. — Danny Dainton, Dec 22 '17 at 07:22
Dear Danny, I am so sorry to inform you that as I am new to this things. I don't know how and where to use `data[0].question` . Can you please guide me? Thank you in advance. — Pooa, Dec 22 '17 at 10:46
I don't know Flask or Python in a huge way but from what I can see and read [here](http://flask.pocoo.org/docs/0.12/api/#response-objects) - `question = request.json['question']` wouldn't be able to pick up any values in the `data` array in your example. I'm probably wrong but `question = request.json['data'][0]['question']` might pick up the first value. This is a basic [tutorial](https://www.w3schools.com/js/js_json_arrays.asp) but it will help to understand how to access the data in the JSON. — Danny Dainton, Dec 22 '17 at 11:27
Awesome - You may need to close out this question if you have a solution that works for you. — Danny Dainton, Dec 22 '17 at 13:12
It is inserting only "how are you", "welcome". How to insert all strings? — Pooa, Dec 23 '17 at 07:01

score 0 · Accepted Answer · answered Dec 23 '17 at 05:35

0

question = request.json['data'][0]['question']

will work.

answered Dec 23 '17 at 05:35

Pooa

9
1
11

It is inserting only "how are you", "welcome". How to insert all strings? – Pooa Dec 23 '17 at 07:01
You will need to loop over the data, as my suggestion would only get the first question in the array. Research information in that area - you need to at least do some level of work on your side to find a solution that works. – Danny Dainton Dec 23 '17 at 07:21
Have a look at different questions on here like this [one](https://stackoverflow.com/a/42445265/6028443) and experiment different methods of getting the data you require. Extract that part away from your code and try to figure out that in isolation before bring it back in. – Danny Dainton Dec 23 '17 at 08:41

Insert json string into table

1 Answers1