内存高效的SqlAlchemy内置迭代器/生成器？

109 投票

7 回答

79851 浏览

提问于 2025-04-17 02:06

我有一个大约1000万条记录的MySQL表，我是通过SqlAlchemy来操作这个表的。我发现，当我对这个表的大部分数据进行查询时，内存消耗会非常高，尽管我以为我在使用一个内置的生成器，它可以智能地分批获取数据。

for thing in session.query(Things):
    analyze(thing)

为了避免这个问题，我发现我需要自己构建一个迭代器，来分批处理数据。

lastThingID = None
while True:
    things = query.filter(Thing.id < lastThingID).limit(querySize).all()
    if not rows or len(rows) == 0: 
        break
    for thing in things:
        lastThingID = row.id
        analyze(thing)

这是正常现象吗？还是说我对SqlAlchemy的内置生成器有什么误解？

对于这个问题的答案似乎表明，内存消耗不应该这么高。

迭代器生成器内存管理 sqlalchemy 数据查询数据库优化批处理

7 个回答

我一直在研究如何用SQLAlchemy高效地进行数据遍历和分页，想更新一下这个回答。

我觉得你可以使用slice这个方法来合理地限制查询的范围，而且这样可以高效地重复使用它。

举个例子：

window_size = 10  # or whatever limit you like
window_idx = 0
while True:
    start,stop = window_size*window_idx, window_size*(window_idx+1)
    things = query.slice(start, stop).all()
    if things is None:
        break
    for thing in things:
        analyze(thing)
    if len(things) < window_size:
        break
    window_idx += 1

回答于 2025-04-17 由 Python大师

分享举报

我不是数据库专家，但在使用SQLAlchemy作为简单的Python抽象层（也就是说，不使用ORM查询对象）时，我找到了一种满意的方法，可以在不让内存使用量飙升的情况下查询一个有3亿行的表...

这里有个简单的例子：

from sqlalchemy import create_engine, select

conn = create_engine("DB URL...").connect()
q = select([huge_table])

proxy = conn.execution_options(stream_results=True).execute(q)

然后，我使用SQLAlchemy的fetchmany()方法，在一个无限的while循环中遍历结果：

while 'batch not empty':  # equivalent of 'while True', but clearer
    batch = proxy.fetchmany(100000)  # 100,000 rows at a time

    if not batch:
        break

    for row in batch:
        # Do your stuff here...

proxy.close()

这个方法让我可以进行各种数据汇总，而不会有危险的内存开销。

注意 这个stream_results方法适用于Postgres和pyscopg2适配器，但我猜它不适用于任何DBAPI，也不适用于任何数据库驱动...

在这篇博客文章中，有一个有趣的用例，启发了我上面的方法。

回答于 2025-04-17 由 Python大师

分享举报

132

大多数数据库API的实现会在获取数据时一次性把所有行都加载到内存中。因此，通常在SQLAlchemy的ORM拿到结果之前，整个结果集就已经在内存里了。

而Query的工作方式是默认会完全加载给定的结果集，然后再把对象返回给你。这是因为有些查询不仅仅是简单的SELECT语句。例如，当你连接其他表时，可能会在一个结果集中多次返回相同的对象（这在急切加载时很常见），所以需要把所有行都加载到内存中，这样才能返回正确的结果，否则集合可能只会部分填充。

因此，Query提供了一个选项，可以通过yield_per()来改变这种行为。这个调用会让Query分批返回行，你可以指定每批的大小。正如文档所说，这种方法只适合在你不进行急切加载的情况下使用，所以基本上是你得非常清楚自己在做什么。此外，如果底层的数据库API已经预先加载了行，那么仍然会有内存开销，所以这种方法的效果也只是比不使用稍微好一点。

我几乎不使用yield_per(); 相反，我使用你上面提到的LIMIT方法的更好版本，利用窗口函数。LIMIT和OFFSET有一个很大的问题，就是当OFFSET值非常大时，查询会变得越来越慢，因为OFFSET为N时，它会逐行读取N行数据——这就像是把同一个查询执行五十次，每次读取的行数越来越多。通过窗口函数的方法，我会预先获取一组“窗口”值，这些值指向我想选择的表的块。然后我会发出单独的SELECT语句，每次从一个窗口中提取数据。

窗口函数的方法可以在维基上找到，我用得非常成功。

另外要注意：并不是所有的数据库都支持窗口函数；你需要使用Postgresql、Oracle或SQL Server。在我看来，使用至少Postgresql绝对是值得的——如果你在使用关系数据库，最好还是用最好的。

回答于 2025-04-17 由 Python大师

分享举报

内存高效的SqlAlchemy内置迭代器/生成器？

7 个回答

撰写回答