使用session.query读取SQLAlchemy中未提交的数据 [英] Using session.query to read uncommitted data in SQLAlchemy

问题描述

我正在尝试针对一系列数据库操作编写集成测试，并且希望能够将SQLAlchemy会话用作登台环境，验证并回滚事务。

I'm trying write integration tests against a series of database operations, and I want to be able to use a SQLAlchemy session as a staging environment in which to validate and rollback a transaction.

是否可以使用 session.query（Foo）来检索未提交的数据 session.execute（text（'select * from foo'））的内容？

Is it possible to retrieve uncommitted data using session.query(Foo) instead of session.execute(text('select * from foo'))?

这些结果是使用SQLAlchemy 1.2.10，Python 2.7.13和Postgres 9.6.11观察到的。

These results were observed using SQLAlchemy 1.2.10, Python 2.7.13, and Postgres 9.6.11.

I' ve看过相关的StackOverflow帖子，但没有找到以下两个操作为何表现不同的解释。

I've looked at related StackOverflow posts but haven't found an explanation as to why the two operations below should behave differently.

• < a href = https://stackoverflow.com/questions/20364243/sqlalchemy-changes-not-committing-to-db> SQLalchemy：更改未提交给数据库

• 在每次 session.query session.flush（） c> 。没有成功。

• Tried with and without session.flush() before every session.query. No success.

sqlalchemy update not commiting changes to database. Using single connection in an app

• 检查以确保我在整个过程中都使用相同的会话对象

Sqlalchemy返回SELECT命令（query.all）的不同结果

• 不适用：我的目标工作流程是评估单个会话的临时表中的一系列CRUD操作。

查询添加到SQLAlchemy中未提交会话的对象

• 似乎是最相关的问题，但我避免使用 session.commit（）是不同的，我没有找到我想要的解释。



• Seems to be the most related issue, but my motivation for avoiding session.commit() is different, and I didn't quite find the explanation I'm looking for.

1）建立与数据库的连接并定义模型对象；到目前为止没有问题：

1) I establish a connection to the database and define a model object; no issues so far:

from sqlalchemy import text
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey

#####
# Prior DB setup:
# CREATE TABLE foo (id int PRIMARY KEY, label text);
#####

# from https://docs.sqlalchemy.org/en/13/orm/mapping_styles.html#declarative-mapping
Base = declarative_base()

class Foo(Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
    label = Column(String)

# from https://docs.sqlalchemy.org/en/13/orm/session_basics.html#getting-a-session
some_engine = create_engine('postgresql://username:password@endpoint/database')
Session = sessionmaker(bind=some_engine)

2）我执行在不提交结果的情况下进行了一些更新，我可以通过在会话中执行select语句来查看暂存的数据：

2) I perform some updates without committing the result, and I can see the staged data by executing a select statement within the session:

session = Session()
sql_insert = text("INSERT INTO foo (id, label) VALUES (1, 'original')")
session.execute(sql_insert);
sql_read = text("SELECT * FROM foo WHERE id = 1");
res = session.execute(sql_read).first()
print res.label

sql_update = text("UPDATE foo SET label = 'updated' WHERE id = 1")
session.execute(sql_update)
res2 = session.execute(sql_read).first()
print res2.label

sql_update2 = text("""
INSERT INTO foo (id, label) VALUES (1, 'second_update')
ON CONFLICT (id) DO UPDATE
    SET (label) = (EXCLUDED.label)
""")
session.execute(sql_update2)
res3 = session.execute(sql_read).first()
print res3.label
session.rollback()

# prints expected values: 'original', 'updated', 'second_update'

3 ）我尝试用session.query替换select语句，但看不到新数据：

3) I attempt to replace select statements with session.query, but I can't see the new data:

session = Session()
sql_insert = text("INSERT INTO foo (id, label) VALUES (1, 'original')")
session.execute(sql_insert);
res = session.query(Foo).filter_by(id=1).first()
print res.label

sql_update = text("UPDATE foo SET label = 'updated' WHERE id = 1")
session.execute(sql_update)
res2 = session.query(Foo).filter_by(id=1).first()
print res2.label

sql_update2 = text("""
INSERT INTO foo (id, label) VALUES (1, 'second_update')
ON CONFLICT (id) DO UPDATE
    SET (label) = (EXCLUDED.label)
""")
session.execute(sql_update2)
res3 = session.query(Foo).filter_by(id=1).first()
print res3.label
session.rollback()
# prints: 'original', 'original', 'original'

我希望第3步的打印输出为原始，更新， second_update。

I expect the printed output of Step 3 to be 'original', 'updated', 'second_update'.

推荐答案

根本原因是在这种情况下原始SQL查询和ORM不会自动混合。而 会话不是缓存，这意味着它不会缓存查询，它会根据对象的主键将对象存储在身份地图。当 查询 返回映射对象的行，并返回现有对象。这就是为什么您不观察在第三步中所做的更改的原因。这看起来似乎是处理情况的一种较差的方法，但是SQLAlchemy的操作基于关于事务隔离，如何时到期或刷新 ：

The root cause is that the raw SQL queries and the ORM do not mix automatically in this case. While the Session is not a cache, meaning it does not cache queries, it does store objects based on their primary key in the identity map. When a Query returns a row for a mapped object, the existing object is returned. This is why you do not observe the changes you made in the 3rd step. This might seem like a rather poor way to handle the situation, but SQLAlchemy is operating based on some assumptions about transaction isolation, as described in "When to Expire or Refresh":

交易隔离

... [So]作为最佳猜测，它假设在事务范围内，除非已知已经发出SQL表达式来修改特定行，否则除非明确指示，否则无需刷新行。

...[So] as a best guess, it assumes that within the scope of a transaction, unless it is known that a SQL expression has been emitted to modify a particular row, there’s no need to refresh a row unless explicitly told to do so.

关于事务隔离的整个注释值得一读。使SQLAlchemy知道这种更改的方法是，如果可能的话，使用Query API执行更新，并手动到期已更改的对象，如果其他所有方法均失败。考虑到这一点，您的第三步应如下所示：

The whole note about transaction isolation is a worthwhile read. The way to make such changes known to SQLAlchemy is to perform updates using the Query API, if possible, and to manually expire changed objects, if all else fails. With this in mind, your 3rd step could look like:

session = Session()
sql_insert = text("INSERT INTO foo (id, label) VALUES (1, 'original')")
session.execute(sql_insert);
res = session.query(Foo).filter_by(id=1).first()
print(res.label)

session.query(Foo).filter_by(id=1).update({Foo.label: 'updated'},
                                          synchronize_session='fetch')
# This query is actually redundant, `res` and `res2` are the same object
res2 = session.query(Foo).filter_by(id=1).first()
print(res2.label)

sql_update2 = text("""
INSERT INTO foo (id, label) VALUES (1, 'second_update')
ON CONFLICT (id) DO UPDATE
    SET label = EXCLUDED.label
""")
session.execute(sql_update2)
session.expire(res)
# Again, this query is redundant and fetches the same object that needs
# refreshing anyway
res3 = session.query(Foo).filter_by(id=1).first()
print(res3.label)
session.rollback()

这篇关于使用session.query读取SQLAlchemy中未提交的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！