SQLAlchemy会话和连接关系 [英] SQLAlchemy session and connection relationship

问题描述

使用相同的SQLAlchemy session对象执行的查询是否使用相同的基础连接?如果没有，有没有办法确保这一点?

Do queries executed with the same SQLAlchemy session object use the same underlying connection? If not, is there a way to ensure this?

某些背景:我需要使用MySQL的命名锁功能，即GET_LOCK()和RELEASE_LOCK()函数.就MySQL服务器而言，只有获得锁的连接才能释放它-因此，我必须确保在同一连接内执行这两个命令，或者确保连接被释放以确保释放锁.

Some background: I have a need to use MySQL's named lock feature, i.e. GET_LOCK() and RELEASE_LOCK() functions. As far as the MySQL server is concerned, only the connection that obtained the lock can release it - so I have to make sure that I either execute these two commands within the same connection or the connection dies to ensure the lock is released.

为了使事情变得更好，我创建了一个锁定"上下文，如下所示:

To make things nicer, I have created a "locked" context like so:

@contextmanager
def mysql_named_lock(session, name, timeout):
    """Get a named mysql lock on a session
    """
    lock = session.execute("SELECT GET_LOCK(:name, :timeout)",
                           name=name, timeout=timeout).scalar()
    if lock:
        try:
            yield session
        finally:
            session.execute("SELECT RELEASE_LOCK(:name)", name=name)
    else:
        e = "Count not obtain named lock {} within {} sections".format(
            name, timeout)
        raise RuntimeError(e)

def my_critical_section(session):
    with mysql_named_lock(session, __name__, 10) as lockedsession:
        thing = lockedsession.query(MyStuff).one()
    return thing

我想确保mysql_named_lock中的两个execute调用发生在同一基础连接上，或者连接已关闭.

I want to make sure that the two execute calls in mysql_named_lock happen on the same underlying connection or the connection is closed.

我可以假设这会正常工作"吗?或者我在这里需要注意什么吗?

Can I assume this would "just work" or is there anything I need to be aware of here?

推荐答案

如果(a)您的会话是scoped_session，并且(b)您以非并行方式使用它(相同的pid/线).如果您太偏执，请通过

it will "just work" if (a) your session is a scoped_session and (b) you are using it in a non-concurrent fashion (same pid / thread). If you're too paranoid, make sure (assert) you're using the same connection ID via

session.connection().connection.thread_id()

同样，没有必要将会话作为参数传递.在应用程序的全局范围内的某个位置初始化一次，然后在代码中的任何位置调用，您将获得相同的连接ID.

also, there is no point to pass session as an argument. Init it once, somewhere in your application’s global scope, then call anywhere in a code, you will get the same connection ID.

这篇关于SQLAlchemy会话和连接关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！