游标如何在 Python 的 DB-API 中工作? [英] How do cursors work in Python's DB-API?

问题描述

我一直在使用 Python 和 RDBMS(MySQL 和 PostgreSQL)，我注意到我真的不明白如何使用游标.

I have been using python with RDBMS' (MySQL and PostgreSQL), and I have noticed that I really do not understand how to use a cursor.

通常，他的脚本通过客户端 DB-API(如 psycopg2 或 MySQLdb)连接到数据库:

Usually, one have his script connect to the DB via a client DB-API (like psycopg2 or MySQLdb):

connection = psycopg2.connect(host='otherhost', etc)

然后创建一个游标:

cursor = connection.cursor()

然后可以发出查询和命令:

And then one can issue queries and commands:

cursor.execute("SELECT * FROM etc")

现在查询的结果在哪里，我想知道?是在服务器上吗?或者在我的客户端上有一点，在我的服务器上有一点?然后，如果我们需要访问一些结果，我们就获取它们:

Now where is the result of the query, I wonder? is it on the server? or a little on my client and a little on my server? And then, if we need to access some results, we fetch 'em:

rows = cursor.fetchone() 

或

rows = cursor.fetchmany()

现在让我们说，我没有检索所有行，并决定执行另一个查询，之前的结果会发生什么?是他们的开销.

Now lets say, I do not retrieve all the rows, and decide to execute another query, what will happen to the previous results? Is their an overhead.

另外，我是否应该为每种形式的命令创建一个游标，并以某种方式不断地将它用于相同的命令?我负责 psycopg2 可以以某种方式优化多次执行但具有不同值的命令，如何以及是否值得?

Also, should I create a cursor for every form of command and continuously reuse it for those same commands somehow; I head psycopg2 can somehow optimize commands that are executed many times but with different values, how and is it worth it?

谢谢

推荐答案

是的，我知道它已经几个月了 :P

ya, i know it's months old :P

DB-API 的游标似乎模仿 SQL 游标.就 AFA 资源(行)管理而言，DB-API 没有指定是客户端必须检索所有行还是声明一个实际的 SQL 游标.只要 fetchXXX 接口做他们应该做的事情，DB-API 就会很高兴.

DB-API's cursor appears to be closely modeled after SQL cursors. AFA resource(rows) management is concerned, DB-API does not specify whether the client must retrieve all the rows or DECLARE an actual SQL cursor. As long as the fetchXXX interfaces do what they're supposed to, DB-API is happy.

关注 AFA psycopg2 游标(您可能很清楚)，未命名的 DB-API 游标"将获取整个结果集——由 libpq 在内存中缓冲的 AFAIK.命名 DB-API 游标"(一个可能不可移植的 psycopg2 概念)，将按需请求行(fetchXXX 方法).

AFA psycopg2 cursors are concerned(as you may well know), "unnamed DB-API cursors" will fetch the entire result set--AFAIK buffered in memory by libpq. "named DB-API cursors"(a psycopg2 concept that may not be portable), will request the rows on demand(fetchXXX methods).

正如unbeknown"所引用的，executemany 可用于优化同一命令的多次运行.但是，它不适应准备好的语句的需要；当具有不同参数集的语句的重复执行不是直接按顺序执行时，executemany() 的执行效果与 execute() 一样.DB-API 确实为"驱动程序作者提供了缓存已执行语句的能力，但它的实现(语句的范围/生命周期是什么?)是未定义的，因此不可能在 DB-API 实现中设置预期.

As cited by "unbeknown", executemany can be used to optimize multiple runs of the same command. However, it doesn't accommodate for the need of prepared statements; when repeat executions of a statement with different parameter sets is not directly sequential, executemany() will perform just as well as execute(). DB-API does "provide" driver authors with the ability to cache executed statements, but its implementation(what's the scope/lifetime of the statement?) is undefined, so it's impossible to set expectations across DB-API implementations.

如果您要将大量数据加载到 PostgreSQL，我强烈建议您尝试找到一种使用 COPY 的方法.

If you are loading lots of data into PostgreSQL, I would strongly recommend trying to find a way to use COPY.

这篇关于游标如何在 Python 的 DB-API 中工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！