python - 通过 SQLAlchemy 使用 Postgresql 执行多个语句不会保留更改

Question

这不起作用——更新无效:

command = "select content from blog where slug = 'meow'; update account_balance set balance=200 where id=1; select 1 from blog;"
content = db.engine.scalar(command)

切换语句执行更新和选择成功:

command = "update account_balance set balance=200 where id=1; select content from blog where slug = 'meow';"
content = db.engine.scalar(command)

为什么第一个不起作用？它适用于 Pgadmin。我使用 Flask-Sqlalchemy 启用了自动提交。

我正在举办一个关于 SQL 注入(inject)的研讨会，所以请不要重写解决方案!

Answer 1

SQLAlchemy 的自动提交工作方式是它检查发出的语句，试图 detect whether or not data is modified :

..., SQLAlchemy implements its own “autocommit” feature which works completely consistently across all backends. This is achieved by detecting statements which represent data-changing operations, i.e. INSERT, UPDATE, DELETE, as well as data definition language (DDL) statements such as CREATE TABLE, ALTER TABLE, and then issuing a COMMIT automatically if no transaction is in progress. The detection is based on the presence of the autocommit=True execution option on the statement. If the statement is a text-only statement and the flag is not set, a regular expression is used to detect INSERT, UPDATE, DELETE, as well as a variety of other commands for a particular backend

自 multiple result sets are not supported在 SQLAlchemy 级别，在您的第一个示例中，检测只是忽略了发出 COMMIT，因为 first 语句是一个 SELECT，而在您的第二个示例中它是一个 UPDATE。不会尝试从多个语句中检测数据修改语句。

如果你看PGExecutionContext.should_autocommit_text() ，你会看到它做了一个 regex match反对AUTOCOMMIT_REGEXP .换句话说，它只匹配文本的开头。