python - Flask 中字段较多时如何更新记录。 Python

Question

我正在尝试更新sqlite数据库中的记录，即来自前端查询的值:

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}]

在我的 Flask 应用程序中，我这样做:

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)

就我而言，我在执行 Operation.query.update (operat) 时遇到错误，并且我强烈怀疑 operat 对象是否具有执行该操作所需的结构更新。当有很多字段需要更新时，请给出一个例子或纠正我的最简洁、正确的更新记录。

以这种形式执行更新记录执行成功，但是结构非常繁琐:

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

因此，我正在尝试缩短更新记录的设计。谢谢。

Answer 1

如果您使用操作 id 扩展字典，则可以使用 SQLAlchemy 的 bulk_update_mappings 在数据库中更新它们，而 ORM 的开销非常小。我没有太多的数字，但我可以从经验中看出，它会快得多，也不会那么麻烦。看看this question和 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)