从游标创建临时表
在使用 SQLObject 从 Python 访问的 PostgreSQL 中，有什么方法可以根据游标的结果创建临时表吗？
之前，我有一个查询，我直接从查询创建临时表。然后我有许多其他查询与该临时表交互。
现在我有更多的数据，所以我只想一次只处理 1000 行左右。但是，据我所知，我无法从光标执行 CREATE TEMP TABLE ... AS ... 。唯一要做的事情是：
rows = cur.fetchmany(1000);
cur2 = conn.cursor()
cur2.execute("""CREATE TEMP TABLE foobar (id INTEGER)""")
for row in rows:
cur2.execute("""INSERT INTO foobar (%d)""" % row)
还是有更好的方法？这看起来效率极低。
Is there any way, in PostgreSQL accessed from Python using SQLObject, to create a temporary table from the results of a cursor?
Previously, I had a query, and I created the temporary table directly from the query. I then had many other queries interacting w/ that temporary table.
Now I have much more data, so I want to only process 1000 rows at a time or so. However, I can't do CREATE TEMP TABLE ... AS ... from a cursor, not as far as I can see. Is the only thing to do something like:
rows = cur.fetchmany(1000);
cur2 = conn.cursor()
cur2.execute("""CREATE TEMP TABLE foobar (id INTEGER)""")
for row in rows:
cur2.execute("""INSERT INTO foobar (%d)""" % row)
or is there a better way? This seems awfully inefficient.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
Postgres 正在逐条记录地读取游标，您只需通过 fetchmany 调用获取 1000 个游标并将它们加载到内存中。我不确定您真正期望您所要求的工作如何进行。
性能更好的版本将确保所有这些插入都包装在单个 BEGIN 和 END 中，以便它是一个事务。
与仅通过 row_number() 将列添加到临时表中以使其有序相比，游标是否有原因？
Well Postgres is reading the cursor record by record and you're just getting 1000 of those with the fetchmany call and loading them into memory. I'm not sure how you would really expect what you're asking to work.
A better performing version of that would make sure that all those INSERTS were wrapped in a single BEGIN and END so that its one transaction.
Is there a reason for the cursor as opposed to just adding a column via row_number() into a temporary table to start off with - so that its ordered?
我没有使用过 PostgreSQL，但我知道要插入存储过程的结果，您可以这样做：
取自 此处。
那么你可以做类似的事情吗？也许将光标结果作为一个整体发送给它，如下所示：
I haven't used PostgreSQL but I know that to insert the results of a stored procedure you would do:
Taken from here.
So could you maybe do something similar. Maybe send it the cursor results as a whole so something like:
我最终这样做了：
而不是 1000 个单独的插入。仍然不知道更好的方法，但这已经足够好了。
I ended up doing this:
instead of 1000 separate inserts. Still don't know a better way, but this works well enough.
您可以尝试
查看快速执行助手文档以获取完整的详细信息
You might try
see the Fast execution helpers docs for full dets