在SQLAlchemy Core中执行多个独立的语句？

Question

我正在使用SQLAlchemy Core运行几个独立的语句。这些语句是针对不同的表格且互不相关的。因此，我不能使用标准的table.insert()方法同时传入多个参数字典。目前，我的做法是：

sql_conn.execute(query1)
sql_conn.execute(query2)

我是否可以在不需要两次来回到数据库的情况下一次运行它们？ 我使用的是MySQL 5.7和Python 2.7.11。

- Eli

---

为什么将两个语句放在一起运行是个好主意？或者说，这样做有什么好处？ - Mauro Baraldi

@MauroBaraldi 一次往返数据库而不是两次。 - univerio

@MauroBaraldi 这是你必须做出的权衡，以提取更好的性能。例如，SQLAlchemy ORM 批处理查询。 - univerio

也许这不是一个好的方法。SQLAlchemy在性能方面有一个很好的文档。 - Mauro Baraldi

批量查询是降低延迟的有效方法。性能不仅限于优化单个查询的运行时间。 - univerio

Answer 1

听起来你想要一个交易：

with engine.connect() as sql_conn:
    with sql_conn.begin():
        sql_conn.execute(query1)
        sql_conn.execute(query2)

上面的代码使用了上下文管理器，其中隐含了一个 sql_conn.commit()，它将更改一次性提交到数据库中。如果你想要显式地提交更改，可以这样做：

from sqlalchemy import create_engine
engine = create_engine("postgresql://scott:tiger@localhost/test")
connection = engine.connect()
trans = connection.begin()
connection.execute(text("insert into x (a, b) values (1, 2)"))
trans.commit()

https://docs.sqlalchemy.org/en/14/core/connections.html#basic-usage

这句话的意思是：“虽然这主要是针对创建真实数据库事务的，但对于您的用例，它具有有用的副作用，即在SQLAlchemy中维护一个“虚拟”事务。请参阅此链接以获取更多信息。”

https://docs.sqlalchemy.org/en/14/orm/session_transaction.html#session-level-vs-engine-level-transaction-control

会话(Session)跟踪单个“虚拟”事务的状态，使用称为SessionTransaction的对象。然后，这个对象利用Session对象绑定的底层引擎或引擎，根据需要使用连接(Connection)对象启动真正的连接级别事务。

当需要时，自动创建此“虚拟”事务，或者可以使用Session.begin()方法启动它。尽可能地支持Python上下文管理器的使用，无论是在创建Session对象的级别还是在维护SessionTransaction的范围方面。

以上描述了ORM功能，但链接显示它与核心功能具有相同的平等性。

- Alex W

---

通过sqla async难道不可能吗？ - undefined

Answer 2

MySQL提供了几种技术，你可以决定哪种适合你：

情况1 - 与服务器进行2次往返：

query1;
query2;

案例2 -- 执行1次往返调用存储过程，如下所示：CALL

-- one-time declaration (overhead not included):
CREATE PROCEDURE both
BEGIN;
    query1;
    query2;
END;

-- per invocation (1 round trip)
CALL both();

注意事项：如果您需要对调用进行参数化，特别是使用可变数量的操作数，那就会变得混乱。

情况3：向单个表中进行多个插入操作--批量插入只需1次往返。

INSERT INTO t (a,b) VALUES (1,2), (11,22), ...;

（对于多个DELETEs，UPDATEs等，还有其他各种技术可以使用，涉及到单个表。）

案例4：需要“原子性”（由于可能与其他进程发生冲突）--多次往返。

START TRANSACTION;
query1;
query2;
COMMIT;

- Rick James

Answer 3

同时运行两个查询既不明智也不实际。我指的是在单个服务器调用中连续使用两个SQL语句：“SELECT…;SELECT…;”

这样做会为黑客提供另一种通过“SQL注入”进行恶意操作的途径，因此并不明智。

另一方面，虽然可能，但并不一定实用。您可以创建一个包含任意数量相关或不相关查询的存储过程，然后CALL该过程。有些事情可能会使它不切实际：

• 唯一的将数据输入的方法是通过有限数量的标量参数。
• 输出作为多个结果集返回；您需要以不同的方式编写代码来查看发生了什么。

如果您和MySQL服务器在同一台机器上，则来回延迟时间不重要，即使两个服务器位于同一数据中心，也通常可以忽略延迟。当客户端和服务器相隔很远时，延迟变得很重要。对于跨大西洋延迟，我们说的是超过100毫秒。从巴西到中国约为250毫秒。（庆幸我们没有生活在木星上。）

- Rick James

---

如果这些查询是由我定义的，并且我们进行了适当的参数化，那么就不会增加SQL注入的风险。即使对我们来说多花50毫秒的时间也是很重要的，这就是我问这个问题的原因。肯定不想为此使用存储过程，也不应该在我可以非常容易地只使用原始SQL而不是SQLAlchemy Core发送两个查询的情况下使用存储过程。正因为如此，这个问题特别涉及通过SQLAlchemy Core完成这个任务。 - Eli

@Eli - 保护措施来自禁止多查询。黑客喜欢将一个查询变成多个，这会使他们受到限制。例如-关闭引号并执行一些恶意操作："; DROP ...作为本应只是字符串的一部分。 - Rick James

这是一个参数化问题，这就是我上面所提到的。如果人们正确地进行参数化，那么可以防止黑客进行堆叠查询。这与我们是否应该能够从SQLAlchemy一次运行多个查询的问题是分开的。 - Eli

我添加了一个新的第一段 - 对我的回答有两种解释方式。 - Rick James

-1，这个回答对于“黑客”和“微不足道的延迟”有很多不必要的FUD。OP想要的是一笔交易。 - undefined