问题描述
我想通过将它们置于 BEGIN; END; 之间来一次运行许多选择查询。我尝试了以下操作：
I want to run many select queries at once by putting them between BEGIN; END;. I tried the following:
cur = connection.cursor()
cur.execute("""
BEGIN;
SELECT ...;
END;""")
res = cur.fetchall()
但是，出现错误：
psycopg2.ProgrammingError: no results to fetch
我实际上如何以这种方式获取数据？
How can I actually get data this way?
同样，如果我连续只有多个选择，则只能从最新的选择中获取数据。有办法从所有这些数据中获取数据吗？
Likewise, if I just have many selects in a row, I only get data back from the latest one. Is there a way to get data out of all of them?
推荐答案
Postgresql实际上并不支持从一个数据库中返回多个结果集。单个命令。如果将此输入传递给psql：
Postgresql doesn't actually support returning multiple result sets from a single command. If you pass this input to psql:
BEGIN;
SELECT ...;
END;
它将在客户端进行拆分并实际执行三个语句，其中仅第二个返回a结果集。
it will split this up client-side and actually execute three statements, only the second of which returns a result set.
BEGIN和 END是SQL级的命令，用于启动/完成事务。 （这样做可能有一个较低级别的协议，但我不记得了）。您可能不想直接发布它们，而是让驱动程序（psycopg2）处理。例如，使用Perl的DBI，我在连接时指定AutoCommit => 0，并且在我的第一个命令之前隐式地发出 BEGIN；然后在我显式调用$ dbh-> commit时单击 END（或 COMMIT等）；我猜想Python的DB-API的工作原理是这样的，因为其他系统（例如JDBC）也是如此...
"BEGIN" and "END" are SQL-level commands to start/finish a transaction. (There may be a lower-level protocol for doing this but I can't remember). You probably don't want to issue them directly, but rather have your driver (psycopg2) handle this. For example, with Perl's DBI I specify AutoCommit=>0 when connecting and it implicitly issues a "BEGIN" before my first command; and then "END" (or "COMMIT" etc) when I explicitly call $dbh->commit; I guess Python's DB-API works rather like this, since other systems such as JDBC do as well...
这篇关于SQL从BEGIN中获取数据； ...;结束;在python中阻止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！