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.

Ruben Helsloot · Accepted Answer · 2019-06-21 12:28:55Z

2

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)

answered Jun 21, 2019 at 12:28

Ruben Helsloot

13.2k6 gold badges33 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adrian Krupa · Accepted Answer · 2019-06-21 08:11:09Z

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, 2019 at 8:11

Adrian Krupa

1,9471 gold badge15 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to update records when many fields in Flask. Python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related