关闭SQLAlchemy连接

Question

我在Python里有一个这样的函数：

def add_odm_object(obj, table_name, primary_key, unique_column):
    db = create_engine('mysql+pymysql://root:@127.0.0.1/mydb')
    metadata = MetaData(db)
    t = Table(table_name, metadata, autoload=True)
    s = t.select(t.c[unique_column] == obj[unique_column])    
    rs = s.execute()
    r = rs.fetchone()
    if not r:
        i = t.insert()
        i_res = i.execute(obj)
        v_id = i_res.inserted_primary_key[0]
        return v_id
    else:
        return r[primary_key]   

这个函数会检查对象obj是否在数据库里，如果没有找到，就把它保存到数据库中。现在，我遇到了一个问题。我在一个循环里多次调用这个函数，结果在调用几百次后，我收到了一个错误提示：user root has exceeded the max_user_connections resource (current value: 30)。我试着找解决办法，比如有个问题提到：如何在MySQL中关闭sqlalchemy连接，建议创建一个conn = db.connect()对象，其中db是数据库引擎，并在查询完成后调用conn.close()来关闭连接。

但我该在代码的哪里打开和关闭连接呢？我并不是直接操作连接，而是使用Table()和MetaData这些函数。

Answer 1

引擎是一个创建数据库连接的工厂，这个工厂的建立成本很高。你的应用程序应该每个数据库服务器只调用一次 create_engine()。

同样，MetaData 和 Table 对象描述了一个在已知数据库中固定的结构。这些也是配置性的构件，通常情况下就像类一样，在一个模块中只创建一次。

在这种情况下，你的函数似乎想要动态加载表，这没问题；MetaData 对象就像一个注册表，它的一个方便之处在于，如果表已经存在，它会把现有的表返回给你。

在 Python 函数中，特别是在循环中，为了获得最佳性能，通常你只想引用一个单一的 数据库连接。

考虑到这些，你的模块可能看起来像这样：

# module level variable.  can be initialized later, 
# but generally just want to create this once.
db = create_engine('mysql+pymysql://root:@127.0.0.1/mydb')

# module level MetaData collection.
metadata = MetaData()

def add_odm_object(obj, table_name, primary_key, unique_column):
    with db.begin() as connection:

        # will load table_name exactly once, then store it persistently
        # within the above MetaData
        t = Table(table_name, metadata, autoload=True, autoload_with=conn)

        s = t.select(t.c[unique_column] == obj[unique_column])    
        rs = connection.execute(s)
        r = rs.fetchone()
        if not r:
            i_res = connection.execute(t.insert(), some_col=obj)
            v_id = i_res.inserted_primary_key[0]
            return v_id
        else:
            return r[primary_key]