Python Postgres：我能使用fetchall()获取100万行数据吗？

Question

我在Python中正在使用psycopg2模块从Postgres数据库中读取数据。我需要对一列中的所有行执行某些操作，该列有超过100万行。

我想知道cur.fetchall()会失败或导致服务器崩溃吗？（因为我的RAM可能无法容纳那么多数据）

q="SELECT names from myTable;"
cur.execute(q)
rows=cur.fetchall()
for row in rows:
    doSomething(row)

有更聪明的方法来做这件事吗？

- Medya Gh

---

我认为更明智的做法是在数据库服务器端找到一种方法来处理你计划用所有这些行执行的操作。如果你发现自己从数据库中检索了一百万行数据，那么你的方法可能有问题。 - mustaccio

@mustaccio，这不是一个好的观点。您不知道OP问题背后的业务逻辑和数据集。 - Adrian Chrostowski

Answer 1

Burhan提出的解决方案通过仅获取单行数据来减少大型数据集的内存使用：

row = cursor.fetchone()

然而，我注意到逐行获取数据会显著减慢速度。 我通过互联网连接访问外部数据库，这可能是其中一个原因。

具有服务器端游标并获取一堆行被证明是最有效的解决方案。 您可以更改 SQL 语句（如 Alecxe 的答案中所述），但也可以使用 psycopg2 提供的功能进行纯 Python 方法：

cursor = conn.cursor('name_of_the_new_server_side_cursor')
cursor.execute(""" SELECT * FROM table LIMIT 1000000 """)

while True:
    rows = cursor.fetchmany(5000)
    if not rows:
        break

    for row in rows:
        # do something with row
        pass

您可以在psycopg2 wiki中了解更多关于服务器端游标的内容。

- linqu

---

你能定义“获取”吗？因为查询仍然完全执行，所以数据被存储在某个地方。在数据库中，它仍然是单个查询，所以我不明白fetchone或fetchmany如何节省内存。 - swade

好问题@StevenWade，用这种方式做法不会减少数据库实例的内存消耗，没错。然而我理解这个问题是针对避免连接到数据库并获取数据的实例的内存消耗。 - linqu

那么它如何减少应用程序中的内存使用？它会在数据包级别上进行分割吗？它必须以某种方式限制返回的数据量。它不会将查询分解，所有结果也不能一次性返回，否则它就无法减少内存使用。 - swade

Answer 2

考虑使用服务器端游标：

当执行数据库查询时，Psycopg游标通常会提取由后端返回的所有记录，并将它们传输到客户端进程。如果查询返回了大量数据，则客户端将分配相应数量的内存。

如果数据集过大以至于在客户端无法实际处理，那么可以创建一个服务器端游标。使用这种类型的游标，可以仅向客户端传输受控制的数据量，使得可以检查大型数据集而不必将其全部保存在内存中。

这是一个例子：

cursor.execute("DECLARE super_cursor BINARY CURSOR FOR SELECT names FROM myTable")
while True:
    cursor.execute("FETCH 1000 FROM super_cursor")
    rows = cursor.fetchall()

    if not rows:
        break

    for row in rows:
        doSomething(row)

- alecxe

---

谢谢，使用这种方法和Burhan Khalid给出的答案有什么优势？https://dev59.com/s2Mm5IYBdhLWcg3wB7Z5#17933440 - Medya Gh

Burhan的回答看起来更简单，也可以工作。但是，在每次迭代循环的步骤中让服务器给你控制的数据量是一种方法。 - alecxe

没有使用BINARY关键字对我来说是有效的。 - Jan Sila

psycopg2不支持二进制游标，正如以下链接所述：https://www.postgresql.org/message-id/20170721184920.75hboryinmgdekyw％40macberry.localdomain。如果有人知道如何通过psycopg2实际使用二进制游标而不仅仅是命名游标，那将不胜感激。 - ihatecache

当我重新启动代码时，光标从它已处理的最后一条记录开始，而不是回到数据库的开头。我该如何将光标指向表的开头？ - Alex

Answer 3

fetchall() 方法最多获取 arraysize 条记录，为了避免对数据库造成巨大的负担，你可以分批获取记录，或者逐行遍历游标直到全部获取完毕：

row = cur.fetchone()
while row:
   # do something with row
   row = cur.fetchone()

- Burhan Khalid

Answer 4

这里是使用简单的服务器端游标代码，可以实现fetchmany管理速度的方法。

原理是在Psycopg2中使用命名游标，并为其提供一个良好的itersize，以一次加载多行数据，就像fetchmany一样，但只需使用单个循环for rec in cursor即可隐式执行fetchnone()。

使用此代码，我能够在1小时内从数十亿行表中查询150万行，并使用200兆字节的内存。

- Le Droid

Answer 5

编辑: 使用fetchmany（与fetchone（）和fetchall（）一起，即使有行限制（arraysize），仍将发送整个结果集，使其保留在客户端（存储在底层c库中，我认为是libpq）以供任何其他fetchmany（）调用等。如果不使用命名游标（这将需要打开事务），则必须使用sql中的limit，并使用order-by分析结果，并使用where（ordered_val =％（last_seen_val）s and primary_key>％（last_seen_pk）s OR ordered_val>％（last_seen_val）s）增强下一个查询。

这至少对于库来说是误导性的，文档应该有关于此的描述。我不知道为什么它没有出现。

---

如果没有需要交互式向前/向后滚动的情况，不确定命名游标是否适合？我可能在这里错了。

fetchmany循环很繁琐，但我认为这是最好的解决方案。为了让生活更轻松，您可以使用以下内容：

from functools import partial
from itertools import chain

# from_iterable added >= python 2.7
from_iterable = chain.from_iterable

# util function
def run_and_iterate(curs, sql, parms=None, chunksize=1000):
    if parms is None:
        curs.execute(sql)
    else:
        curs.execute(sql, parms)
    chunks_until_empty = iter(partial(fetchmany, chunksize), [])
    return from_iterable(chunks_until_empty)

# example scenario
for row in run_and_iterate(cur, 'select * from waffles_table where num_waffles > %s', (10,)):
    print 'lots of waffles: %s' % (row,)

- parity3

Answer 6

阅读评论和答案时，我想澄清一些关于fetchone和服务器端光标的问题，以供未来读者参考。

在使用普通游标（客户端）时，Psycopg 获取被后端返回的所有记录，并将它们传输到客户端进程。整个记录都缓存在客户端的内存中，当你执行像curs.execute('SELECT * FROM ...'这样的查询时。

这个问题也证实了这一点。

所有的fetch*方法都是用来访问存储的数据的。

问：fetchone如何帮助我们更好地利用内存？
答：它仅从存储的数据中获取一个记录，并创建一个单独的Python对象，并将其交给你的Python代码，而fetchall将从该数据获取并创建n个Python对象，并一次性将它们全部交给你。

如果你的表有1,000,000条记录，那么在内存中会发生以下情况：

curs.execute --> whole 1,000,000 result set  +  fetchone --> 1 Python object
curs.execute --> whole 1,000,000 result set  +  fetchall --> 1,000,000 Python objects

当然，fetchone有所帮助，但我们仍然需要将整个记录存储在内存中。这就是服务器端游标发挥作用的地方：

PostgreSQL也有自己的游标概念（有时也称为portal）。创建数据库游标时，查询不一定完全处理：服务器可能只能按需生成结果。仅传输客户端请求的结果：如果查询结果非常大，但客户端只需要前几条记录，则可以仅传输它们。 ... 它们的接口相同，但在幕后，它们发送命令以控制服务器上游标的状态（例如在获取新记录或使用scroll()移动时）。

因此，您不会在一个块中获得整个结果集。

缺点是：

缺点是服务器需要跟踪部分处理的结果，因此它在服务器上使用更多的内存和资源。

- S.B