SQL从BEGIN中获取数据； ...;结尾; python 中的块

发布于 2024-09-05 08:53:53 字数 363 浏览 2 评论 0原文

我想通过将它们放在 BEGIN; END; 之间来一次运行许多选择查询。我尝试了以下操作：

cur = connection.cursor()
cur.execute("""
BEGIN;
SELECT ...;
END;""")
res = cur.fetchall()

但是，我收到错误：

psycopg2.ProgrammingError: no results to fetch

如何以这种方式实际获取数据？

同样，如果我连续有很多选择，我只会从最新的选择中获取数据。有没有办法从所有这些中获取数据？

原文

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()

However, I get the error:

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蓝礼 2024-09-12 08:53:53

Postgresql 实际上并不支持从单个命令返回多个结果集。如果将此输入传递给 psql：

BEGIN;
SELECT ...;
END;

它将在客户端拆分此输入并实际执行三个语句，只有第二个语句返回结果集。

“BEGIN”和“END”是用于启动/结束事务的 SQL 级命令。（可能有一个较低级别的协议可以执行此操作，但我不记得了）。您可能不想直接发出它们，而是让您的驱动程序（psycopg2）处理这个问题。例如，使用 Perl 的 DBI，我在连接时指定 AutoCommit=>0，并且它在我的第一个命令之前隐式发出“BEGIN”；然后当我显式调用 $dbh->commit; 时“END”（或“COMMIT”等）我猜 Python 的 DB-API 的工作原理与此类似，因为其他系统（例如 JDBC）也这样做......

Postgresql doesn't actually support returning multiple result sets from a single command. If you pass this input to psql:

BEGIN;
SELECT ...;
END;

it will split this up client-side and actually execute three statements, only the second of which returns a result set.

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

回复收藏 0 原文