postgresql Flask-SQLAlchemy db.session.query(Model) vs Model.query

Flask-SQLAlchemy db.session.query(Model) vs Model.query

提问by Marcos Vives Del Sol

This is a weird bug I've stumbled upon, and I am not sure why is it happening, whether it's a bug in SQLAlchemy, in Flask-SQLAlchemy, or any feature of Python I'm not yet aware of.

这是我偶然发现的一个奇怪的错误，我不确定它为什么会发生，无论是 SQLAlchemy、Flask-SQLAlchemy 中的错误，还是我还不知道的 Python 的任何特性。

We are using Flask 0.11.1, with Flask-SQLAlchemy 2.1 using a PostgreSQL as DBMS.

我们使用 Flask 0.11.1，Flask-SQLAlchemy 2.1 使用 PostgreSQL 作为 DBMS。

Examples use the following code to update data from the database:

示例使用以下代码更新数据库中的数据：

entry = Entry.query.get(1)
entry.name = 'New name'
db.session.commit()

This works totally fine when executing from the Flask shell, so the database is correctly configured. Now, our controller for updating entries, slightly simplified (without validation and other boilerplate), looks like this:

当从 Flask shell 执行时，这完全正常，因此数据库配置正确。现在，我们用于更新条目的控制器稍微简化（没有验证和其他样板），如下所示：

def details(id):
    entry = Entry.query.get(id)

    if entry:
        if request.method == 'POST':
            form = request.form
            entry.name = form['name']
            db.session.commit()
            flash('Updated successfully.')
        return render_template('/entry/details.html', entry=entry)
    else:
        flash('Entry not found.')
        return redirect(url_for('entry_list'))

# In the application the URLs are built dynamically, hence why this instead of @app.route
app.add_url_rule('/entry/details/<int:id>', 'entry_details', details, methods=['GET', 'POST'])

When I submit the form in details.html, I can see perfectly fine the changes, meaning the form has been submitted properly, is valid and that the model object has been updated. However, when I reload the page, the changes are gone, as if it had been rolled back by the DBMS.

当我在 details.html 中提交表单时，我可以看到完美的更改，这意味着表单已正确提交、有效并且模型对象已更新。但是，当我重新加载页面时，更改消失了，就好像它已被 DBMS 回滚一样。

I have enabled app.config['SQLALCHEMY_ECHO'] = Trueand I can see a "ROLLBACK" before my own manual commit.

我已启用app.config['SQLALCHEMY_ECHO'] = True并且可以在我自己的手动提交之前看到“ROLLBACK”。

If I change the line:

如果我改变行：

entry = Entry.query.get(id)

To:

到：

entry = db.session.query(Entry).get(id)

As explained in https://stackoverflow.com/a/21806294/4454028, it does work as expected, so my guess what there was some kind of error in Flask-SQLAlchemy's Model.queryimplementation.

正如https://stackoverflow.com/a/21806294/4454028 中所解释的，它确实按预期工作，所以我猜测 Flask-SQLAlchemy 的Model.query实现中存在某种错误。

However, as I prefer the first construction, I did a quick modification to Flask-SQLAlchemy, and redefined the query@propertyfrom the original:

但是，由于我更喜欢​​第一个构造，因此我对 Flask-SQLAlchemy 进行了快速修改，并重新定义query@property了原始构造：

class _QueryProperty(object):

    def __init__(self, sa):
        self.sa = sa

    def __get__(self, obj, type):
        try:
            mapper = orm.class_mapper(type)
            if mapper:
                return type.query_class(mapper, session=self.sa.session())
        except UnmappedClassError:
            return None

To:

到：

class _QueryProperty(object):

    def __init__(self, sa):
        self.sa = sa

    def __get__(self, obj, type):
        return self.sa.session.query(type)

Where sais the Flask-SQLAlchemy object (ie dbin the controller).

saFlask-SQLAlchemy 对象在哪里（即db在控制器中）。

Now, this is where things got weird: it still doesn't save the changes. Code is exactly the same, yet the DBMS is still rolling back my changes.

现在，这就是事情变得奇怪的地方：它仍然没有保存更改。代码完全相同，但 DBMS 仍在回滚我的更改。

I read that Flask-SQLAlchemy can execute a commit on teardown, and tried adding this:

我读到 Flask-SQLAlchemy 可以在拆卸时执行提交，并尝试添加以下内容：

app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True

Suddenly, everything works. Question is: why?

突然间，一切正常。问题是：为什么？

Isn't teardown supposed to happen only when the view has finished rendering? Why is the modified Entry.querybehaving different to db.session.query(Entry), even if the code is the same?

不是只有当视图完成渲染时才应该进行拆卸吗？为什么修改后的Entry.query行为与 不同db.session.query(Entry)，即使代码相同？

采纳答案by Akshay Pratap Singh

Below is the correct way to make changes to a model instance and commit them to the database:

以下是对模型实例进行更改并将其提交到数据库的正确方法：

# get an instance of the 'Entry' model
entry = Entry.query.get(1)

# change the attribute of the instance; here the 'name' attribute is changed
entry.name = 'New name'

# now, commit your changes to the database; this will flush all changes 
# in the current session to the database
db.session.commit()

Note:Don't use SQLALCHEMY_COMMIT_ON_TEARDOWN, as it's considered harmful and also removed from docs. See the changelog for version 2.0.

注意：不要使用SQLALCHEMY_COMMIT_ON_TEARDOWN，因为它被认为是有害的并且也会从文档中删除。请参阅2.0 版的变更日志。

Edit:If you have two objects of normal session(created using sessionmaker()) instead of scoped session, then on calling db.session.add(entry)above code will raise error sqlalchemy.exc.InvalidRequestError: Object '' is already attached to session '2' (this is '3'). For more understanding about sqlalchemy session, read below section

编辑：如果您有两个普通会话对象（使用创建sessionmaker()）而不是作用域会话，那么在调用db.session.add(entry)上面的代码时会引发错误sqlalchemy.exc.InvalidRequestError: Object '' is already attached to session '2' (this is '3')。有关 sqlalchemy 会话的更多了解，请阅读以下部分

Major Difference between Scoped Session vs. Normal Session

Scoped Session 与 Normal Session 的主要区别

The session object we mostly constructed from the sessionmaker()call and used to communicate with our database is a normal session. If you call sessionmaker()a second time, you will get a new session object whose states are independent of the previous session. For example, suppose we have two session objects constructed in the following way:

我们主要从sessionmaker()调用中构建并用于与我们的数据库通信的会话对象是一个普通会话。如果您sessionmaker()再次调用，您将获得一个新的会话对象，其状态独立于前一个会话。例如，假设我们有两个以下列方式构造的会话对象：

from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)


from sqlalchemy import create_engine
engine = create_engine('sqlite:///')

from sqlalchemy.orm import sessionmaker
session = sessionmaker()
session.configure(bind=engine)
Base.metadata.create_all(engine)

# Construct the first session object
s1 = session()
# Construct the second session object
s2 = session()

Then, we won't be able to add the same User object to both s1and s2at the same time. In other words, an object can only be attached at most one unique session object.

然后，我们将无法给同一个用户对象添加到都s1和s2在同一时间。换句话说，一个对象最多只能附加一个唯一的会话对象。

>>> jessica = User(name='Jessica')
>>> s1.add(jessica)
>>> s2.add(jessica)
Traceback (most recent call last):
......
sqlalchemy.exc.InvalidRequestError: Object '' is already attached to session '2' (this is '3')

If the session objects are retrieved from a scoped_sessionobject, however, then we don't have such an issue since the scoped_sessionobject maintains a registry for the same session object.

scoped_session然而，如果会话对象是从一个对象中检索的，那么我们就不会有这样的问题，因为该scoped_session对象为同一个会话对象维护了一个注册表。

>>> session_factory = sessionmaker(bind=engine)
>>> session = scoped_session(session_factory)
>>> s1 = session()
>>> s2 = session()
>>> jessica = User(name='Jessica')
>>> s1.add(jessica)
>>> s2.add(jessica)
>>> s1 is s2
True
>>> s1.commit()
>>> s2.query(User).filter(User.name == 'Jessica').one()

Notice thats1and s2are the same session object since they are both retrieved from a scoped_sessionobject who maintains a reference to the same session object.

请注意，s1和s2是相同的会话对象，因为它们都是从scoped_session维护对同一会话对象的引用的对象中检索的。

Tips

尖端

So, try to avoid creating more than one normal sessionobject. Create one object of the session and use it everywhere from declaring models to querying.

因此，尽量避免创建多个普通会话对象。创建会话的一个对象并在从声明模型到查询的任何地方使用它。

回答by Marcos Vives Del Sol

Our project is separated in several files to ease mantenaince. One is routes.pywith the controllers, and another one is models.py, which contains the SQLAlchemy instance and models.

我们的项目被分成几个文件以方便维护。一个是routes.py控制器，另一个是models.py，它包含 SQLAlchemy 实例和模型。

So, while I was removing boilerplate to get a minimal working Flask project to upload it to a git repository to link it here, I found the cause.

因此，当我删除样板以获得最小的工作 Flask 项目以将其上传到 git 存储库以将其链接到此处时，我找到了原因。

Apparently, the reason is that my workmate, while attempting to insert data using queries instead of the model objects (no, I have no idea why on earth he wanted to do that, but he spent a whole day coding it), had defined another SQLAlchemy instance in the routes.py.

显然，原因是我的同事在尝试使用查询而不是模型对象插入数据时（不，我不知道他到底为什么要这样做，但他花了一整天的时间编写代码），定义了另一个中的 SQLAlchemy 实例routes.py。

So, when I was trying to insert data from the Flask shell using:

因此，当我尝试使用以下方法从 Flask shell 插入数据时：

from .models import *
entry = Entry.query.get(1)
entry.name = 'modified'
db.session.commit()

I was using the correct dbobject, as defined in models.py, and it was working completely fine.

我使用了正确的db对象，如 中定义的那样models.py，并且它完全正常工作。

However, as in routes.pythere was another dbdefined after the model import, this one was overwriting the referenceto the correct SQLAlchemy instance, so I was commiting with a different session.

然而，因为在模型导入之后定义了routes.py另一个db，这个覆盖了对正确 SQLAlchemy 实例的引用，所以我提交了一个不同的 session。