python - 在查询 [INFORMATION_SCHEMA].[TABLES] 时，SQLAlchemy 在插入期间挂起

Question

我有一个 Python 进程，它使用 SQLAlchemy 将一些数据插入到 MS SQL Server 数据库中。当 Python 进程运行时，它会在插入期间挂起。我打开了 SQLAlchemy 日志记录以获取更多信息。我发现它卡在 SQLAlchemy 似乎正在请求有关整个数据库的表架构信息的这一点上:

2020-10-30 08:12:07 [11444:6368] sqlalchemy.engine.base.Engine._execute_context(base.py:1235) INFO: SELECT [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] 
FROM [INFORMATION_SCHEMA].[TABLES] 
WHERE [INFORMATION_SCHEMA].[TABLES].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(? AS NVARCHAR(max)) ORDER BY [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME]
2020-10-30 08:12:07 [11444:6368] sqlalchemy.engine.base.Engine._execute_context(base.py:1240) INFO: ('dbo', 'BASE TABLE')

此时我在数据库中进行了其他“操作”，包括一些打开的事务，我的猜测是无论出于何种原因查询 [INFORMATION_SCHEMA].[TABLES] 都会产生一些死锁或 block 不知何故。

我还读到 ( here ) [INFORMATION_SCHEMA].[TABLES] 是一个不会导致死锁的 View ，这与我对导致此问题的原因的猜测相矛盾。

我的问题是:我能否更改 SQLAlchemy 的配置/设置，使其一开始就不会进行此查询？

更新 1:插入的Python代码是这样的:

with sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params).connect() as connection:
    # df is a Pandas DataFrame
    df.to_sql(name=my_table, con=connection, if_exists='append', index=False)

请注意，当我在一天中没有其他数据库事务进行的其他时间运行 Python 脚本时，代码可以正常工作。在这些情况下，日志会像这样立即继续，列出数据库中的所有表:

2020-10-30 08:13:03 [11444:6368] sqlalchemy.engine.base.Engine._execute_context(base.py:1235) INFO: SELECT [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] 
FROM [INFORMATION_SCHEMA].[TABLES] 
WHERE [INFORMATION_SCHEMA].[TABLES].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(? AS NVARCHAR(max)) ORDER BY [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME]
2020-10-30 08:13:03 [11444:6368] sqlalchemy.engine.base.Engine._execute_context(base.py:1240) INFO: ('dbo', 'BASE TABLE')
2020-10-30 08:13:03 [11444:6368] sqlalchemy.engine.base.Engine._init_metadata(result.py:810) DEBUG: Col ('TABLE_NAME',)
2020-10-30 08:13:03 [11444:6368] sqlalchemy.engine.base.Engine.process_rows(result.py:1260) DEBUG: Row ('t_table1',)
2020-10-30 08:13:03 [11444:6368] sqlalchemy.engine.base.Engine.process_rows(result.py:1260) DEBUG: Row ('t_table2',)
...

更新 2:显然，当在打开的事务中创建表或其他对象但尚未提交时，查询 [INFORMATION_SCHEMA].[TABLES] 将被阻止 (source)。有没有熟悉 SQLAlchemy 内部结构的人建议如何首先阻止它进行此查询？

更新 3:在 SQLAlchemy github 上发布此问题后(issue link)，SQLAlchemy 开发人员确认 [INFORMATION_SCHEMA].[TABLES] 的查询实际上是由 Pandas function 引起的to_sql().

所以，我的新问题是有人知道如何在 Pandas to_sql() 函数中禁用此行为吗？我查看了文档，但找不到任何有用的信息。

Answer 1

我不是很熟悉 SQLAlchemy，但我可以告诉你这个问题的 Pandas 方面。

如果表不存在，Pandas 会自动创建一个新表。它判断表是否存在的方法是调用 SQL Alchemy 中的 has_table()。 has_table() 的工作方式是查询信息模式。 (至少，它在 MySQL 和 MSSQL 中是这样工作的。)

实现细节

这是我在 Pandas 和 SQLAlchemy 中找到的跟踪逻辑的内容。我们从 pandas/io/sql.py 开始，在 to_sql() 内。

        table = SQLTable(
            name,
            self,
            frame=frame,
            index=index,
            if_exists=if_exists,
            index_label=index_label,
            schema=schema,
            dtype=dtype,
        )
        table.create()

SQLTable.create() 定义在这里:

class SQLTable(PandasObject):
    [...]
    def create(self):
        if self.exists():
            if self.if_exists == "fail":
                raise ValueError(f"Table '{self.name}' already exists.")
            elif self.if_exists == "replace":
                self.pd_sql.drop_table(self.name, self.schema)
                self._execute_create()
            elif self.if_exists == "append":
                pass
            else:
                raise ValueError(f"'{self.if_exists}' is not valid for if_exists")
        else:
            self._execute_create()

请注意，它无条件地调用了 exists()。在 SQLTable.exists() 中，您会发现:

    def exists(self):
        return self.pd_sql.has_table(self.name, self.schema)

这最终会在 SQLAlchemy 中调用 has_table():https://docs.sqlalchemy.org/en/13/core/internals.html#sqlalchemy.engine.default.DefaultDialect.has_table

对于 MSSQL，这是在 SQLAlchemy 中的 sqlalchemy/dialects/mssql/base.py 中实现的:

    @_db_plus_owner
    def has_table(self, connection, tablename, dbname, owner, schema):
        if tablename.startswith("#"):  # temporary table
            [...]
        else:
            tables = ischema.tables

            s = sql.select(tables.c.table_name).where(
                sql.and_(
                    tables.c.table_type == "BASE TABLE",
                    tables.c.table_name == tablename,
                )
            )

            if owner:
                s = s.where(tables.c.table_schema == owner)

            c = connection.execute(s)

            return c.first() is not None

(ischema 是 information_schema 的缩写，这段代码是在那个表上运行一个 select。)

如何解决这个问题

我没有找到解决此问题的简单好方法。 Pandas 假定 has_table() 是一种廉价操作。 MSSQL 不遵循该假设。无论 if_exists 设置为什么，Pandas 都会在 to_sql() 期间调用 has_table()。

不过，我可以想出一个 hacky 的方法来做到这一点。如果你要 monkey-patch pandas.io.sql.SQLTable.create() 使其成为空操作，然后您可以欺骗 Pandas 认为该表已经存在。这样做的缺点是 Pandas 不会自动创建表。