How to update records when many fields in Flask. Python

Question

I am trying to update the record in the sqlite database, the values that come in the query from the frontend:

data: [{"cnt_doc":"17","code":"111","contragent":"Name1","contragent_id":2,"created_date":"Mon, 17 Jun 2019 18:54:37 GMT","date_operation":null,"id":2,"rezerv":"23","status":"Status1","status_id":1,"stellag":"67","storage":"NameStorage","storage_id":2,"sum_operation":"100","timestamp":"Mon, 17 Jun 2019 18:54:37 GMT","type":2,"type_id":2,"id_type":0}]

In my Flask application I do this:

from flask import render_template, flash, redirect, url_for, jsonify, request
from app import app, db
from sqlalchemy.exc import SQLAlchemyError
from app.models import Operation

...

@app.route('/operation', methods=['GET', 'POST'])
def operation():   
        method = request.args.get('method')       
        if method == 'Update':
            data = request.form.get("data")
            try:
                objFormat = json.loads(data)
                for key in objFormat:
                    dict_upd = key

                operat = Operation.query.filter_by(id=int(dict_upd['id'])).first()
                for key in dict_upd.keys():
                    setattr(operat, key, dict_upd[key])

                Operation.query.update(operat)#
                db.session.commit()
                jsonFormat = jsonify(dict_upd)
                dictOut = {"data": dict_upd, "meta": {"success": "true"}}

                return (dictOut)
            except SQLAlchemyError as e:
                return jsonify('Error update record in db', exc_info=e)

In my case, I get an error when executing Operation.query.update (operat) and I strongly doubt that the operat object has the necessary structure to execute the update. Give an example or correct mine to most concisely and correctly update the records when there are many fields requiring updating.

Performing update records in this form is successfully performed, but has a very cumbersome structure:

operat = Operation.query.filter_by(id = int(dict_upd['id'])).update(
                                        {'date_operation': dict_upd.date_operation},
                                        {'code': dict_upd.code},
                                        {'status_id': dict_upd.status_id},
                                        {'status': dict_upd.status},
                                        {'type_id': dict_upd.type_id},
                                        {'type': dict_upd.type},
                                        {'storage_id': dict_upd.storage_id},
                                        {'storage': dict_upd.storage},
                                        {'contragent_id': dict_upd.contragent_id},
                                        {'contragent': dict_upd.contragent},
                                        {'sum_operation': dict_upd.sum_operation},
                                        {'rezerv': dict_upd.rezerv},
                                        {'cnt_doc': dict_upd.cnt_doc},
                                        {'stellag': dict_upd.stellag},
                                        )

Therefore, I am trying to shorten my design of updating records. Thank.

score 2 · Answer 1 · answered Jun 21 '19 at 08:11

2

You can just pass the dictionary to update function (docs):

operat = Operation.query.filter_by(id = int(dict_upd['id'])).update(dict_upd)

You can also use merge to create or update object in database:

operat = db.session.merge(Operation( **dict_upd))

answered Jun 21 '19 at 08:11

Adrian Krupa

1,877
1
15
24

score 2 · Accepted Answer · answered Jun 21 '19 at 12:28

If you extend the dictionary with the operation id, you can use SQLAlchemy's bulk_update_mappings to just update them in the database with very little overhead from the ORM. I don't have many numbers, but I can tell from experience that it's going to be a lot faster and less cumbersome. Have a look at this question and the documentation

def operation():
  method = request.args.get('method')
  if method == 'Update':
    data = request.form.get("data")
    try:
      objFormat = json.loads(data)
      db.session.bulk_insert_mappings(Operation, objFormat)
      db.session.commit()
      jsonFormat = jsonify(dict_upd)
      dictOut = {"data": dict_upd, "meta": {"success": "true"}}

      return (dictOut)
    except SQLAlchemyError as e:
      return jsonify('Error update record in db', exc_info=e)

How to update records when many fields in Flask. Python

2 Answers2