Python 关于在 sqlalchemy 会话中刷新对象

About refreshing objects in sqlalchemy session

提问by Cristián Pérez

Well, I am dealing with a doubt about sqlalchemy and objects refreshing!

好吧，我正在处理有关 sqlalchemy 和对象刷新的疑问！

I am in the situation in what I have 2 sessions, and the same object has been queried in both sessions!... For some particular thing I cannot to close one of the sessions. I have modified the object and commited the changes in session A, but in session B, the attributes are the initial ones! without modifications!..

我处于我有 2 个会话的情况，并且在两个会话中都查询了同一个对象！...对于某些特定的事情，我无法关闭其中一个会话。我已经修改了对象并在会话 A 中提交了更改，但是在会话 B 中，属性是初始的！无需修改！...

So... shall I implement some kind of notification system to communicate changes or there is a built-in way to do this in sqlalchemy??

那么......我应该实现某种通知系统来传达更改还是在 sqlalchemy 中有一种内置的方法可以做到这一点？

采纳答案by Mark Hildreth

Sessions are designed to work like this. The attributes of the object in Session B WILL keep what it had when first queried in Session B. Additionally, SQLAlchemy will not attempt to automatically refresh objects in other sessions when they change, nor do I think it would be wise to try to create something like this.

会话被设计成这样工作。会话 B 中对象的属性将保持它在会话 B 中第一次查询时的属性。此外，当其他会话中的对象发生变化时，SQLAlchemy 不会尝试自动刷新它们，我也不认为尝试创建某些东西是明智的像这样。

You should be actively thinking of the lifespan of each session as a single transaction in the database. How and when sessions need to deal with the fact that their objects might be stale is not a technical problem that can be solved by an algorithm built into SQLAlchemy (or any extension for SQLAlchemy): it is a "business" problem whose solution you must determine and code yourself. The "correct" response might be to say that this isn't a problem: the logic that occurs with Session B could be valid if it used the data at the time that Session B started. Your "problem" might not actually be a problem. The docs actually have an entire section on when to use sessions, but it gives a pretty grim response if you are hoping for a one-size-fits-all solution...

您应该积极地将每个会话的生命周期视为数据库中的单个事务。会话需要如何以及何时处理它们的对象可能陈旧的事实并不是一个技术问题，可以通过内置于 SQLAlchemy（或 SQLAlchemy 的任何扩展）中的算法解决：这是一个“业务”问题，您必须使用它的解决方案自己确定和编码。“正确”的回应可能是说这不是问题：如果会话 B 使用了会话 B 启动时的数据，那么发生在会话 B 上的逻辑可能是有效的。您的“问题”实际上可能不是问题。文档实际上有一整节关于何时使用 session，但如果您希望获得一种通用的解决方案，它会给出非常严峻的回应......

A Session is typically constructed at the beginning of a logical operation where database access is potentially anticipated.

The Session, whenever it is used to talk to the database, begins a database transaction as soon as it starts communicating. Assuming the autocommit flag is left at its recommended default of False, this transaction remains in progress until the Session is rolled back, committed, or closed. The Session will begin a new transaction if it is used again, subsequent to the previous transaction ending; from this it follows that the Session is capable of having a lifespan across many transactions, though only one at a time. We refer to these two concepts as transaction scope and session scope.

The implication here is that the SQLAlchemy ORM is encouraging the developer to establish these two scopes in his or her application, including not only when the scopes begin and end, but also the expanse of those scopes, for example should a single Session instance be local to the execution flow within a function or method, should it be a global object used by the entire application, or somewhere in between these two.

The burden placed on the developer to determine this scope is one area where the SQLAlchemy ORM necessarily has a strong opinion about how the database should be used. The unit of work pattern is specifically one of accumulating changes over time and flushing them periodically, keeping in-memory state in sync with what's known to be present in a local transaction. This pattern is only effective when meaningful transaction scopes are in place.

会话通常是在逻辑操作开始时构建的，在该操作中可能预期会进行数据库访问。

每当使用 Session 与数据库通信时，只要它开始通信，它就会开始数据库事务。假设自动提交标志保留其建议的默认值 False，则此事务将保持进行中，直到 Session 回滚、提交或关闭。如果 Session 再次使用，它将开始一个新的事务，在前一个事务结束之后；由此可知，Session 能够在许多事务中具有生命周期，尽管一次只有一个。我们将这两个概念称为事务范围和会话范围。

这里的含义是 SQLAlchemy ORM 鼓励开发人员在他或她的应用程序中建立这两个范围，不仅包括范围何时开始和结束，还包括这些范围的扩展，例如单个 Session 实例应该是本地的对于函数或方法中的执行流程，它应该是整个应用程序使用的全局对象，还是介于这两者之间的某个位置。

确定此范围的开发人员的负担是 SQLAlchemy ORM 必须对如何使用数据库有强烈意见的一个领域。工作单元模式特别是随着时间的推移累积更改并定期刷新它们，使内存中的状态与本地事务中已知的状态保持同步。此模式仅在有意义的事务范围到位时才有效。

That said, there are a few things you can do to change how the situation works:

也就是说，您可以做一些事情来改变这种情况的运作方式：

First, you can reduce how long your session stays open. Session B is querying the object, then later you are doing something with that object (in the same session) that you want to have the attributes be up to date. One solution is to have this second operation done in a separate session.

首先，您可以缩短会话保持打开状态的时间。会话 B 正在查询该对象，然后您正在对该对象（在同一会话中）执行某些操作，以便使属性保持最新。一种解决方案是在单独的会话中完成第二个操作。

Another is to use the expire/refresh methods, as the docs show...

另一种是使用过期/刷新方法，如文档所示......

# immediately re-load attributes on obj1, obj2
session.refresh(obj1)
session.refresh(obj2)

# expire objects obj1, obj2, attributes will be reloaded
# on the next access:
session.expire(obj1)
session.expire(obj2)

You can use session.refresh()to immediately get an up-to-date version of the object, even if the session already queried the object earlier.

您可以使用session.refresh()立即获取对象的最新版本，即使会话之前已经查询过该对象。

回答by Tuanitim

Run this, to force session to update latest value from your database of choice:

运行此命令，强制会话更新您选择的数据库中的最新值：

session.expire_all()

Excellent DOC about default behavior and lifespan of session

关于默认行为和会话寿命的优秀文档

回答by Nathan Wailes

I just had this issue and the existing solutions didn't work for me for some reason. What did work was to call session.commit(). After calling that, the object had the updated values from the database.

我刚刚遇到了这个问题，现有的解决方案由于某种原因对我不起作用。起作用的是调用session.commit(). 调用之后，该对象具有来自数据库的更新值。

回答by SVUser

TL;DR Rather than working on Session synchronization, see if your task can be reasonably easily coded with SQLAlchemy Core syntax, directly on the Engine, without the use of (multiple) Sessions

TL;DR与其处理会话同步，不如直接在引擎上使用 SQLAlchemy Core 语法轻松地对您的任务进行编码，而无需使用（多个）会话

For someone coming from SQL and JDBC experience, one critical thing to learn about SQLAlchemy, which, unfortunately, I didn't clearly come across reading through the multiple documents for months is that SQLAlchemy consists of two fundamentally different parts: the Core and the ORM. As the ORM documentation is listed first on the website and most examples use the ORM-like syntax, one gets thrown into working with it and sets them-self up for errors and confusion - if thinking about ORM in terms of SQL/JDBC. ORM uses its own abstraction layer that takes a complete control over how and when actual SQL statements are executed. The rule of thumb is that a Session is cheap to create and kill, and it should never be re-used for anything in the program's flow and logic that may cause re-querying, synchronization or multi-threading. On the other hand, the Core is the direct no-thrills SQL, very much like a JDBC Driver. There is one place in the docsI found that "suggests" using Core over ORM:

对于拥有 SQL 和 JDBC 经验的人来说，了解 SQLAlchemy 的一件重要事情，不幸的是，我几个月来没有清楚地阅读过多个文档，因为 SQLAlchemy 由两个根本不同的部分组成：核心和 ORM . 由于网站上首先列出了 ORM 文档，并且大多数示例都使用类似 ORM 的语法，因此人们会投入使用它并自行设置错误和混淆 - 如果从 SQL/JDBC 的角度考虑 ORM。ORM 使用自己的抽象层来完全控制实际 SQL 语句的执行方式和时间。经验法则是创建和终止会话的成本很低，并且永远不应该将它重新用于程序流和逻辑中可能导致重新查询、同步或多线程的任何事情。另一方面，Core 是直接没有刺激的 SQL，非常像 JDBC 驱动程序。有一个地方我发现“建议”使用 Core over ORM 的文档：

it is encouraged that simple SQL operations take place here, directly on the Connection, such as incrementing counters or inserting extra rows within log tables. When dealing with the Connection, it is expected that Core-level SQL operations will be used; e.g. those described in SQL Expression Language Tutorial.

鼓励在这里直接在连接上进行简单的 SQL 操作，例如递增计数器或在日志表中插入额外的行。处理Connection时，预计会用到Core级别的SQL操作；例如，SQL 表达式语言教程中描述的那些。

Although, it appears that using a Connection causes the same side effect as using a Session: re-query of a specific record returns the same result as the first query, even if the record's content in the DB was changed. So, apparently Connections are as "unreliable" as Sessions to read DB content in "real time", but a direct Engine execution seems to be working fine as it picks a Connection object from the pool (assuming that the retrieved Connection would never be in the same "reuse" state relatively to the query as the specific open Connection). The Result object should be closed explicitly, as per SA docs

虽然，使用 Connection 会导致与使用 Session 相同的副作用：重新查询特定记录会返回与第一次查询相同的结果，即使数据库中的记录内容已更改。因此，显然 Connections 与 Sessions 一样“不可靠”，以“实时”读取数据库内容，但直接引擎执行似乎工作正常，因为它从池中选择一个 Connection 对象（假设检索到的 Connection 永远不会在相对于查询的相同“重用”状态作为特定的打开连接）。根据 SA 文档，应明确关闭 Result 对象