Postgres和SqlAlchemy无法正确更新行

Question

每当我使用带有SqlAlchemy的session执行更新语句，然后调用commit()时，它很少会更新数据库。

这是我的环境： 我有两个运行的服务器。一个用于我的数据库，另一个用于Python服务器。

数据库服务器：

Postgres v9.6 - 在Amazon的RDS上

具有Python的服务器

Linux 3.13.0-65-generic x86_64 - 在Amazon EC2实例上 SqlAlchemy v1.1.5 Python v3.4.3 Flask 0.11.1

此外，我使用pgAdmin 4查询我的表。

重要文件：

server/models/category.py

from sqlalchemy.orm import backref
from .. import db
from flask import jsonify


class Category(db.Model):
    __tablename__ = "categories"

    id = db.Column(db.Integer, primary_key=True)
    cat_name = db.Column(db.String(80))
    includes = db.Column(db.ARRAY(db.String), default=[])
    excludes = db.Column(db.ARRAY(db.String), default=[])

    parent_id = db.Column(db.ForeignKey('categories.id', ondelete='SET NULL'), nullable=True, default=None)
    subcategories = db.relationship('Category', backref=backref(
        'categories',
        remote_side=[id],
        single_parent=True,
        cascade="all, delete-orphan"
    ))

    assigned_user = db.Column(db.String(80), nullable=True, default=None)


    def to_dict(self):
        return dict(
            id=self.id,
            cat_name=self.cat_name,
            parent_id=self.parent_id,
            includes=self.includes,
            excludes=self.excludes,
            assigned_user=self.assigned_user,
        )

    def json(self):
        return jsonify(self.to_dict())

    def __repr__(self):
        return "<%s %r>" % (self.__class__, self.to_dict())

class CategoryOperations:
    ...
    @staticmethod
    def update_category(category):
        return """
            UPDATE categories
            SET cat_name='{0}',
              parent_id={1},
              includes='{2}',
              excludes='{3}',
              assigned_user={4}
            WHERE id={5}
            RETURNING cat_name, parent_id, includes, excludes, assigned_user
        """.format(
            category.cat_name,
            category.parent_id if category.parent_id is not None else 'null',
            "{" + ",".join(category.includes) + "}",
            "{" + ",".join(category.excludes) + "}",
            "'" + category.assigned_user + "'" if category.assigned_user is not None else 'null',
            category.id
        )

    @staticmethod
    def update(category, session):
        print("Updating category with id: " + str(category.id))
        stmt = CategoryOperations.update_category(category)
        print(stmt)
        row_updated = session.execute(stmt).fetchone()
        return Category(
            id=category.id,
            cat_name=row_updated[0],
            parent_id=row_updated[1],
            includes=row_updated[2],
            excludes=row_updated[3],
            assigned_user=row_updated[4]
        )
    ...

server/api/category.py

from flask import jsonify, request
import json
from .api_utils.utils import valid_request as is_valid_request
from . import api
from ..models.category import Category, CategoryOperations
from ..models.users_categories import UsersCategoriesOperations, UsersCategories
from ..models.listener_item import ListenerItemOperations, ListenerItem
from ..models.user import UserOperations
from ..schemas.category import category_schema
from .. import get_session

...
@api.route('/categories/<int:id>', methods=['PUT'])
def update_category(id):
    category_json = request.json
    if category_json is None:
        return "Bad Request: Request not sent as json", 400
    valid_json, json_err = is_valid_request(category_json, ['cat_name', 'parent_id', 'includes', 'excludes', 'assigned_user'], "and")
    if not valid_json:
        return json_err, 400

    category = Category(
        id=id,
        cat_name=category_json['cat_name'],
        parent_id=category_json['parent_id'],
        includes=category_json['includes'],
        excludes=category_json['excludes'],
        assigned_user=category_json['assigned_user'],
    )
    session = get_session()
    try:
        updated_category = CategoryOperations.update(category, session)
        session.commit()
        print(updated_category.to_dict())
        return jsonify(updated_category.to_dict()), 200
    except Exception as e:
        print("ROLLBACK")
        print(e)
        session.rollback()
        return str(e), 500
...

在这种情况下，还有一个文件可能会很有用： server/__init__.py

import sqlalchemy as sa
from flask import Flask
from flask_marshmallow import Marshmallow
from flask_sqlalchemy import SQLAlchemy
from config import config
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from flask_cors import CORS, cross_origin
from .db_config import CONFIG

db = SQLAlchemy()
ma = Marshmallow()

Engine = sa.create_engine(
    CONFIG.POSTGRES_URL,
    client_encoding='utf8',
    pool_size=20,
    max_overflow=0
)
Session = sessionmaker(bind=Engine)
conn = Engine.connect()


def get_session():
    return Session(bind=conn)


def create_app(config_name):
    app = Flask(__name__, static_url_path="/app", static_folder="static")
    app_config = config[config_name]()
    print(app_config)
    app.config.from_object(app_config)

    from .api import api as api_blueprint
    app.register_blueprint(api_blueprint, url_prefix='/api')

    from .api.routes import routes
    routes(app)

    from .auth import authentication
    authentication(app)

    db.init_app(app)
    ma.init_app(app)
    CORS(app)
    ...
    return app

为了更好地解释我提供的环境和文件，假设我的分类表中有一行数据如下：

{
  "assigned_user": null,
  "cat_name": "Category Name Before",
  "excludes": [
    "exclude1",
    "excludeBefore"
  ],
  "id": 2,
  "includes": [
    "include1",
    "include2"
  ],
  "parent_id": null
}

当我向 /api/categories/2 发送PUT请求并发送以下内容时：

{
  "assigned_user": null,
  "cat_name": "Category Name 1",
  "excludes": [
    "exclude1",
    "exclude2"
  ],
  "id": 2,
  "includes": [
    "include1",
    "include2"
  ],
  "parent_id": null
}

在请求期间，我打印出了我的PUT请求创建的SQL语句（用于测试），我得到了以下内容：

UPDATE categories
SET cat_name='Category Name 1',
    parent_id=null,
    includes='{include1,include2}',
    excludes='{exclude1,exclude2}',
    assigned_user=null
WHERE id=2
RETURNING cat_name, parent_id, includes, excludes, assigned_user

提交 UPDATE 语句后，它会返回响应。我可以像这样获取更新后的对象：

{
  "assigned_user": null,
  "cat_name": "Category Name 1",
  "excludes": [
    "exclude1",
    "exclude2"
  ],
  "id": 2,
  "includes": [
    "include1",
    "include2"
  ],
  "parent_id": null
}

当我使用这个URL进行GET请求：/api/categories/2，我也会得到同样的对象，如下所示：

{
  "assigned_user": null,
  "cat_name": "Category Name 1",
  "excludes": [
    "exclude1",
    "exclude2"
  ],
  "id": 2,
  "includes": [
    "include1",
    "include2"
  ],
  "parent_id": null
}

然而，当我在 pgAdmin 中运行下面的 SQL 命令时，我得到了旧版本（它没有更新数据库中的行）：

SELECT * FROM categories WHERE id=2

这是我得到的对象：

{
  "assigned_user": null,
  "cat_name": "Category Name Before",
  "excludes": [
    "exclude1",
    "excludeBefore"
  ],
  "id": 2,
  "includes": [
    "include1",
    "include2"
  ],
  "parent_id": null
}

在执行PUT请求之前，这是我拥有的对象。如果我重新启动Python服务器并执行GET请求，则会获取旧对象。似乎在会话中存储数据，但由于某种原因未传播到数据库。

值得注意的是，如果我在pgAdmin中运行更新命令，则会成功更新行。

更新：我还使用了这些方法（如此处所述）进行更新，但问题仍然存在：

# using the session to update
session.query(Category).filter_by(id=category.id).update({
    "cat_name": category.id,
    "assigned_user": category.assigned_user,
    "includes": category.includes,
    "excludes": category.excludes,
    "parent_id": category.parent_id
})

# using the category object to edit, then commit
category_from_db = session.query(Category).filter_by(id=category.id).first()
category_from_db.cat_name = category_json['cat_name']
category_from_db.assigned_user = category_json['assigned_user']
category_from_db.excludes = category_json['excludes']
category_from_db.includes = category_json['includes']
category_from_db.parent_id = category_json['parent_id']
session.commit()

任何想法？

- Daniel Hair

Answer 1

事实证明，每次调用get_session时，我都会创建一个新的会话。而且我没有在每个HTTP请求之后关闭会话。

这是server/api/category.py的PUT请求的样子：

@api.route('/categories/<int:id>', methods=['PUT'])
def update_category(id):
    category_json = request.json
    if category_json is None:
        return "Bad Request: Request not sent as json", 400
    valid_json, json_err = is_valid_request(category_json, ['cat_name', 'parent_id', 'includes', 'excludes', 'assigned_user'], "and")
    if not valid_json:
        return json_err, 400

    category = Category(
        id=id,
        cat_name=category_json['cat_name'],
        parent_id=category_json['parent_id'],
        includes=category_json['includes'],
        excludes=category_json['excludes'],
        assigned_user=category_json['assigned_user'],
    )
    session = get_session()
    try:
        updated_category = CategoryOperations.update(category, session)
        session.commit()
        print(updated_category.to_dict())
        return jsonify(updated_category.to_dict()), 200
    except Exception as e:
        print("ROLLBACK")
        print(e)
        session.rollback()
        return str(e), 500
    finally:                              #
        session.close()                   # <== The fix

一旦我完成了一个会话并关闭它，问题就解决了。希望这可以帮助别人。

- Daniel Hair