查询添加到 SQLAlchemy 中未提交会话的对象 [英] Querying objects added to a non committed session in SQLAlchemy

问题描述

所以我在没有太多上下文的情况下提出了这个问题，结果被否决了，让我们再试一次......

So I placed this question without too much context, and got downvoted, let's try again...

一方面，我不遵循 SQLAlchemy 的 session.add 背后的逻辑.我知道它将对象排队以进行插入，并且我知道 session.query 在连接的数据库中而不是在会话中查找，但是在 SQLAlchemy 中是否有可能在不使用的情况下查询会话首先做 session.flush?我对读取 session.query 的东西的期望是它查询会话......

For one, I don't follow the logic behind SQLAlchemy's session.add. I understand that it queues the object for insertion, and I understand that session.query looks in the connected database rather than in the session, but is it at all possible, within SQLAlchemy, to query the session without first doing session.flush? My expectation from something which reads session.query is that it queries the session...

在 session.query().first() 出现 None 后，我现在手动查看 session.new.

I am now manually looking in session.new after a None comes out of session.query().first().

我不想在 session.query 之前做 session.flush 有两个原因，

There's two reasons why I don't want to do session.flush before my session.query,

• 基于效率恐惧的一个(为什么我要写入数据库，如果我仍在用户可能想要回滚的会话中查询数据库?)；
• 二是因为我采用了一个相当大的程序，并且它设法定义它自己的 Session 其实例导致刷新也提交.

• one based on efficiency fears (why should I write to the database, and query the database if I am still within a session which the user may want to rollback?);
• two is because I've adopted a fairly large program, and it manages to define its own Session whose instances causes flush to also commit.

所以这个问题的真正核心是谁帮我在 github 上的 GPL 程序中找到错误！

So really the core of this question is who's helping me find an error in a GPL program on github!

这是一个在 bauble/ghini 中具有惊人行为的代码片段:

This is a code snippet with a surprising behaviour in bauble/ghini:

# setting up things in ghini
# <replace-later>
import bauble
import bauble.db as db
db.open('sqlite:///:memory:', verify=False)
from bauble.prefs import prefs
import bauble.pluginmgr as pluginmgr
prefs.init()
prefs.testing = True
pluginmgr.load()
db.create(True)
Session = bauble.db.Session
from bauble.plugins.garden import Location
# </replace-later>

# now just plain straightforward usage
session = Session()

session.query(Location).delete()
session.commit()
u0 = session.query(Location).filter_by(code=u'mario').first()
print u0

u1 = Location(code=u'mario')
session.add(u1)
session.flush()

u2 = session.query(Location).filter_by(code=u'mario').one()
print u1, u2, u1==u2

session.rollback()
u3 = session.query(Location).filter_by(code=u'mario').first()
print u3

这里的输出是:

None
mario mario True
mario

这里有我认为只是用于设置数据库的标准简单代码:

here you have what I think is just standard simple code to set up a database:

from sqlalchemy import Column, Unicode

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Location(Base):
    __tablename__ = 'location'
    code = Column(Unicode(64), index=True, primary_key=True)
    def __init__(self, code=None):
        self.code = code
    def __repr__(self):
        return self.code

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

Base.metadata.create_all(engine)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine, autoflush=False)

有了这个，上面相同的代码片段的输出就不那么令人惊讶了:

with this, the output of the same above code snippet is less surprising:

None
mario mario True
None

推荐答案

小玩意中的冲洗最终发出 COMMIT 的原因是 db.py 中的第 133 行，他们处理历史表:

The reason why flushes in bauble end up emitting a COMMIT is the line 133 in db.py where they handle their history table:

table.insert(dict(table_name=mapper.local_table.name,
                  table_id=instance.id, values=str(row),
                  operation=operation, user=user,
                  timestamp=datetime.datetime.today())).execute()

不是使用传入的事务连接在事件处理程序中发出额外的 SQL，他们应该，而是按原样执行语句本身，这意味着它最终使用引擎作为绑定(通过表的元数据找到).使用引擎执行具有 autocommit 行为.由于 bauble 总是使用 ="http://docs.sqlalchemy.org/en/latest/core/pooling.html#sqlalchemy.pool.SingletonThreadPool" rel="nofollow noreferrer">SingletonThreadPool，只有每个线程一个连接，因此该语句最终也会提交刷新的更改.我想知道这个错误是否是 bauble 禁用 自动刷新...

Instead of issuing the additional SQL in the event handler using the passed in transactional connection, as they should, they execute the statement itself as is, which means it ends up using the engine as the bind (found through the table's metadata). Executing using the engine has autocommit behaviour. Since bauble always uses a SingletonThreadPool, there's just one connection per thread, and so that statement ends up committing the flushed changes as well. I wonder if this bug is the reason why bauble disables autoflush...

修复方法是更改​​事件处理以使用事务连接:

The fix is to change the event handling to use the transactional connection:

class HistoryExtension(orm.MapperExtension):
    """
    HistoryExtension is a
    :class:`~sqlalchemy.orm.interfaces.MapperExtension` that is added
    to all clases that inherit from bauble.db.Base so that all
    inserts, updates, and deletes made to the mapped objects are
    recorded in the `history` table.
    """
    def _add(self, operation, mapper, connection, instance):
        """
        Add a new entry to the history table.
        """
        ...  # a ton of code here
        table = History.__table__
        stmt = table.insert(dict(table_name=mapper.local_table.name,
                                 table_id=instance.id, values=str(row),
                                 operation=operation, user=user,
                                 timestamp=datetime.datetime.today()))
        connection.execute(stmt)

    def after_update(self, mapper, connection, instance):
        self._add('update', mapper, connection, instance)

    def after_insert(self, mapper, connection, instance):
        self._add('insert', mapper, connection, instance)

    def after_delete(self, mapper, connection, instance):
        self._add('delete', mapper, connection, instance)

值得注意的是MapperExtension 自 0.7 版起已弃用.

It's worth a note that MapperExtension has been deprecated since version 0.7.

关于您对会议的看法，我引用了 "Session Basics"，你真的应该通读一下:

Regarding your views about the session I quote "Session Basics", which you really should read through:

在最一般的意义上，Session 建立与数据库的所有对话，并代表您在其生命周期内加载或与之关联的所有对象的保存区".它提供了获取Query 对象的入口点，该对象使用Session 对象的当前数据库连接将查询发送到数据库，...

In the most general sense, the Session establishes all conversations with the database and represents a "holding zone" for all the objects which you’ve loaded or associated with it during its lifespan. It provides the entrypoint to acquire a Query object, which sends queries to the database using the Session object’s current database connection, ...

和 "是会话缓存?":

嗯……不.它在某种程度上用作缓存，因为它实现了身份映射模式，并存储以主键为键的对象.但是，它不执行任何类型的查询缓存.这意味着，如果你说 session.query(Foo).filter_by(name='bar')，即使 Foo(name='bar') 就在那里，在身份映射中，会话对此一无所知.它必须向数据库发出 SQL ，取回行，然后当它看到行中的主键时，它可以在本地身份映射中查看对象已经存在.只有当您说 query.get({some primary key}) 时，Session 才不必发出查询.

Yeee…no. It’s somewhat used as a cache, in that it implements the identity map pattern, and stores objects keyed to their primary key. However, it doesn’t do any kind of query caching. This means, if you say session.query(Foo).filter_by(name='bar'), even if Foo(name='bar') is right there, in the identity map, the session has no idea about that. It has to issue SQL to the database, get the rows back, and then when it sees the primary key in the row, then it can look in the local identity map and see that the object is already there. It’s only when you say query.get({some primary key}) that the Session doesn’t have to issue a query.

所以:

我对读取 session.query 的东西的期望是它查询会话......

My expectation from something which reads session.query is that it queries the session...

你的期望是错误的.Session 处理与数据库的对话——包括其他事情.

Your expectations are wrong. The Session handles talking to the DB – among other things.

我不想在 session.query 之前执行 session.flush 有两个原因，

There's two reasons why I don't want to do session.flush before my session.query,

• 基于效率恐惧的一个(为什么我要写入数据库，如果我仍在用户可能想要回滚的会话中查询数据库?)；

因为您的数据库可能会进行验证、具有触发器并为某些列生成值——主键、时间戳等.您认为要插入的数据最终可能会在数据库中出现其他内容，而 Session 绝对无法知道这一点.

Because your DB may do validation, have triggers, and generate values for some columns – primary keys, timestamps, and the like. The data you thought you're inserting may end up something else in the DB and the Session has absolutely no way to know about that.

另外，为什么 SQLAlchemy 应该自己实现一种内存数据库，有自己的查询引擎，以及同步 2 个数据库带来的所有问题?SQLAlchemy 如何支持您查询的不同数据库的所有不同操作和功能?您的简单相等谓词示例只是触及了皮毛.

Also, why should SQLAlchemy implement a sort of an in-memory DB in itself, with its own query engine, and all the problems that come with synchronizing 2 databases? How would SQLAlchemy support all the different operations and functions of different DBs you query against? Your simple equality predicate example just scratches the surface.

回滚时，会回滚数据库的事务(以及会话未刷新的更改).

When you rollback, you roll back the DB's transaction (along with the session's unflushed changes).

• 二是因为我采用了一个相当大的程序，它设法定义了自己的会话，其实例导致刷新也提交.

由事件处理错误引起.

这篇关于查询添加到 SQLAlchemy 中未提交会话的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！