sql-server - Pandas 和 SQLAlchemy : df. to_sql() with SQLAlchemy 2.0 future=True 在使用来自 engine.begin() 的连接时抛出错误

Question

我试图通过 pandas DataFrame 在 MS SQL Server 中更新插入一个表。

据我了解，这是一个两步过程:

• 在临时表中执行 pandas df.to_sql()
• 执行魔术sql合并现有最终表中的临时表

这有效，但前提是我在 create_engine 调用中设置了 future=False。

   self.engine = create_engine(self.url, echo=True, future=False)
  with self.engine.begin() as conn:
            df.to_sql("#update_table", conn, if_exists="append", index=False)
            # merge...
            sql = f"""\
            -- magic sql
            """
            result = conn.execute(text(sql)).fetchone()

当使用 future=True 时我得到这个错误:

Traceback (most recent call last):
  File "/usr/local/Caskroom/miniconda/base/envs/dc_uk/lib/python3.9/site-packages/pandas/io/sql.py", line 1340, in insert_records
    table.insert(chunksize=chunksize, method=method)
  File "/usr/local/Caskroom/miniconda/base/envs/dc_uk/lib/python3.9/site-packages/pandas/io/sql.py", line 959, in insert
    with self.pd_sql.run_transaction() as conn:
  File "/usr/local/Caskroom/miniconda/base/envs/dc_uk/lib/python3.9/contextlib.py", line 119, in __enter__
    return next(self.gen)
  File "/usr/local/Caskroom/miniconda/base/envs/dc_uk/lib/python3.9/site-packages/pandas/io/sql.py", line 1416, in run_transaction
    with self.connectable.begin() as tx:
  File "/usr/local/Caskroom/miniconda/base/envs/dc_uk/lib/python3.9/site-packages/sqlalchemy/future/engine.py", line 144, in begin
    return super(Connection, self).begin()
  File "/usr/local/Caskroom/miniconda/base/envs/dc_uk/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 766, in begin
    raise exc.InvalidRequestError(
sqlalchemy.exc.InvalidRequestError: This connection has already initialized a SQLAlchemy Transaction() object via begin() or autobegin; can't call begin() here unless rollback() or commit() is called first.

似乎 pandas 中的某些东西无法(还)处理 SA2 中的变化。 :(

今年早些时候有一些关于 SQLAlchemy 2.0 的问题，但所有问题都已关闭。

我的问题:我做错了什么吗？或者这仍然是一个悬而未决的问题...？

附言:这是在 mac、python 3.9.7、pandas 1.3.4 和 SQLAlchemy 1.4.27 上。

Answer 1

根据文档，“future”会导致事务自动启动，因此您无需调用 .begin()。

例如

with engine.connect() as conn:
    conn.execute(...)
    conn.execute(...)
    conn.commit()

    conn.execute(...)
    conn.execute(...)
    conn.commit()

method sqlalchemy.future.Connection.begin()