python：sqlalchemy - 如何确保连接不陈旧使用新的事件系统 [英] python: sqlalchemy - how do I ensure connection not stale using new event system

问题描述

我在python中使用sqlalchemy包。我有一个操作，在我对现有表执行自动加载后需要一些时间来执行。当尝试使用连接时，会导致以下错误：

I am using the sqlalchemy package in python. I have an operation that takes some time to execute after I perform an autoload on an existing table. This causes the following error when I attempt to use the connection:

sqlalchemy.exc.OperationalError: (OperationalError) (2006, 'MySQL server has gone away')

我有一个简单的效用函数， p>

I have a simple utility function that performs an insert many:

def insert_data(data_2_insert, table_name):
    engine = create_engine('mysql://blah:blah123@localhost/dbname')
    # Metadata is a Table catalog. 
    metadata = MetaData()
    table = Table(table_name, metadata, autoload=True, autoload_with=engine)
    for c in mytable.c:
        print c
    column_names = tuple(c.name for c in mytable.c)
    final_data = [dict(zip(column_names, x)) for x in data_2_insert]
    ins = mytable.insert()
    conn = engine.connect()
    conn.execute(ins, final_data)
    conn.close()

由于'data_2_insert'有677,161行，因此下面这行代码需要花费很长时间。

It is the following line that times long time to execute since 'data_2_insert' has 677,161 rows.

final_data = [dict(zip(column_names, x)) for x in data_2_insert]

我遇到了这个问题指的是一个类似的问题。但我不知道如何实施接受答案建议的连接管理，因为robots.jpg在评论中指出了这一点：

I came across this question which refers to a similar problem. However I am not sure how to implement the connection management suggested by the accepted answer because robots.jpg pointed this out in a comment:

注意：SQLAlchemy 0.7 - PoolListener已被弃用，但同样的解决方案可以使用新的事件系统。

如果有人可以请给我看几个指针，我如何可以将建议集成到我使用sqlalchemy的方式，我会非常感激。谢谢。

If someone can please show me a couple of pointers on how I could go about integrating the suggestions into the way I use sqlalchemy I would be very appreciative. Thank you.

推荐答案

我认为你正在寻找这样的东西：

I think you are looking for something like this:

from sqlalchemy import exc, event
from sqlalchemy.pool import Pool

@event.listens_for(Pool, "checkout")
def check_connection(dbapi_con, con_record, con_proxy):
    '''Listener for Pool checkout events that pings every connection before using.
    Implements pessimistic disconnect handling strategy. See also:
    http://docs.sqlalchemy.org/en/rel_0_8/core/pooling.html#disconnect-handling-pessimistic'''

    cursor = dbapi_con.cursor()
    try:
        cursor.execute("SELECT 1")  # could also be dbapi_con.ping(),
                                    # not sure what is better
    except exc.OperationalError, ex:
        if ex.args[0] in (2006,   # MySQL server has gone away
                          2013,   # Lost connection to MySQL server during query
                          2055):  # Lost connection to MySQL server at '%s', system error: %d
            # caught by pool, which will retry with a new connection
            raise exc.DisconnectionError()
        else:
            raise

如果你想有条件地触发这个策略，你应该避免在这里使用decorator，而是使用 listen（） function：

If you wish to trigger this strategy conditionally, you should avoid use of decorator here and instead register listener using listen() function:

# somewhere during app initialization
if config.check_connection_on_checkout:
    event.listen(Pool, "checkout", check_connection)

• 连接池活动


• 活动API
• 
  

• Connection Pool Events
• Events API

这篇关于python：sqlalchemy - 如何确保连接不陈旧使用新的事件系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！