通过 SQLAlchemy 使用 Postgresql 执行多条语句不会持久化更改 [英] Executing multiple statements with Postgresql via SQLAlchemy does not persist changes

问题描述

这不起作用 - 更新无效:

This does not work – the update has no effect:

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)

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

Switching the statements performs the update and select successfully:

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 启用了自动提交.

Why does the first not work? It works in Pgadmin. I enabled autocommit with Flask-Sqlalchemy.

我正在做一个关于 SQL 注入的研讨会，所以请不要重写解决方案！

I am doing a workshop on SQL injection, so please dont rewrite the solution!

推荐答案

SQLAlchemy 的自动提交的工作方式是检查发出的语句，尝试 检测数据是否被修改:

The way SQLAlchemy's autocommit works is that it inspects the issued statements, trying to detect whether or not data is modified:

...，SQLAlchemy 实现了自己的自动提交"功能，该功能在所有后端都完全一致.这是通过检测表示数据更改操作的语句(即 INSERT、UPDATE、DELETE 以及数据定义语言 (DDL) 语句(例如 CREATE TABLE、ALTER TABLE)，然后在没有正在进行的事务时自动发出 COMMIT 来实现的).检测基于语句中是否存在 autocommit=True 执行选项.如果语句是纯文本语句并且未设置标志，则使用正则表达式来检测针对特定后端的 INSERT、UPDATE、DELETE 以及各种其他命令

..., 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

由于不支持多个结果集 在 SQLAlchemy 级别，在您的第一个示例中，检测只是省略了发出 COMMIT，因为 first 语句是一个 SELECT，而在您的第二个示例中，它是一个 UPDATE.不会尝试从多个语句中检测数据修改语句.

Since multiple result sets are not supported at SQLAlchemy level, in your first example the detection simply omits issuing a COMMIT because the first statement is a SELECT, where as in your second example it is an UPDATE. No attempt to detect data modifying statements from multiple statements takes place.

如果你看看PGExecutionContext.should_autocommit_text()，你会看到它做了一个正则表达式匹配针对AUTOCOMMIT_REGEXP.换句话说，它只匹配文本的开头.

If you look at PGExecutionContext.should_autocommit_text(), you'll see that it does a regex match against AUTOCOMMIT_REGEXP. In other words it matches only at the beginning of the text.

这篇关于通过 SQLAlchemy 使用 Postgresql 执行多条语句不会持久化更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！