python - 如何使用 sqlalchemy 截断和插入同一事务？

Question

我有一个production_table 和stage_table。我有一个运行几个小时并在 stage_table 中生成数据的 python 脚本。我想在脚本的末尾将 stage_table 中的数据COPY 到 production_table。

基本上这就是我想要的:

1. TRUNCATE production_table
2. COPY production_table from stage_table

这是我的代码:

from sqlalchemy import create_engine
from sqlalchemy.sql import text as sa_text
engine = create_engine("mysql+pymysql://  AMAZON AWS")
engine.execute(sa_text('''TRUNCATE TABLE {1}; COPY TABLE {1} from {0}'''.format(stage_table, production_table)).execution_options(autocommit=True))

这应该生成:

TRUNCATE TABLE production_table; COPY TABLE production_table from stage_table

但是这不起作用。

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, u"You have an error in your SQL syntax;

我怎样才能让它发挥作用？以及如何确保 TRUNCATE 和 COPY 在一起。如果 COPY 中止，我不希望发生 TRUNCATE。

Answer 1

在 SQLAlchemy 中处理单个事务中的多个语句的通常方法是开始一个显式事务并执行其中的每个语句:

with engine.begin() as conn:
    conn.execute(statement_1)
    conn.execute(statement_2)
    ...

至于你原来的尝试，MySQL中没有COPY语句。其他一些 DBMS 确实有类似的功能。此外，并非所有 DB-API 驱动程序都支持 single query or command 中的多个语句，至少开箱即用，这里似乎也是如此。参见 this issue和 related note in the PyMySQL ChangeLog .

最大的问题是not all statements in MySQL can be rolled back ，其中最常见的是 DDL 语句。换句话说，您根本无法在与以下 INSERT INTO ... 相同的事务中执行 TRUNCATE [TABLE] ...，并且必须围绕该限制设计您的应用程序。正如 Christian W. 在评论中所建议的那样，您也许可以从暂存表创建一个全新的表并重命名，或者只是交换生产表和暂存表。 RENAME TABLE ... 也不能回滚，但至少你会减少错误窗口，并且可以撤消更改，因为原始生产表仍然存在，只是在一个新名称下.完成所有其他操作后，您就可以删除原始生产表。这里有一些东西可以证明这个想法，但如果出现问题，则需要人工干预:

# No point in faking transactions here, since MySQL in use.
engine.execute("CREATE TABLE new_production AS SELECT * FROM stage_table")
engine.execute("RENAME TABLE production_table TO old_production")
engine.execute("RENAME TABLE new_production TO production_table")
# Point of no return:
engine.execute("DROP TABLE old_production")