SQLAlchemy：如何使用ORM扫描大型表？

Question

我目前正在尝试使用SQLAlchemy，它非常方便。

为了测试，我创建了一个包含我的照片档案的巨大表格，并用SHA1哈希进行索引（以消除重复项 :-))。这非常快速......

出于好玩，我对结果SQLite数据库执行了相当于select *的操作：

session = Session()
for p in session.query(Picture):
    print(p)

我本来期望会看到哈希值在屏幕上打印出来，但实际上它只是一直在扫描磁盘。同时，内存使用量急剧增加，几秒钟后就达到了1GB。这似乎是由SQLAlchemy的身份映射功能引起的，我以为它只是保留弱引用。

有人能向我解释一下吗？我原本认为每个图片 p 在哈希写出后都会被回收！？

- Bluehorn

---

我自己也注意到了这个问题。如果我执行 len(Session.query(Model).limit(100).all())，我得到的结果是 1。如果我去掉 limit，内存使用量会飙升。 - Sarah Vessels

Answer 1

好的，我刚刚找到了一种自己实现的方法。将代码更改为

session = Session()
for p in session.query(Picture).yield_per(5):
    print(p)

每次只加载5张图片。默认情况下，查询似乎会一次性加载所有行。然而，我还不理解该方法的免责声明。引用自SQLAlchemy文档

警告：使用此方法需谨慎；如果同一个实例在多个行批次中存在，则最终用户对属性的更改将被覆盖。 特别是通常无法与急切加载的集合（即任何lazy=False）一起使用此设置，因为这些集合在遇到后续结果批次时将被清除以进行新的加载。

因此，如果使用yield_per确实是ORM扫描大量SQL数据的正确方式，那么什么时候使用它才是安全的呢？

- Bluehorn

---

如果每个查询实例都只有一个结果 SQL 行，那么使用 yield_per 是安全的。如果你使用 eagerload 或者 join 一个一对多的关系，每个实例会得到额外的行数。如果您需要更多有关 yield_per 的详细信息，请单独创建一个问题进行询问。 - Ants Aasma

需要注意的是：如果您在执行此操作时尝试提交，它会引发异常。请参见https://dev59.com/-mct5IYBdhLWcg3wPbIm。 - Theron Luhn

在与 MySQL 的内存泄漏进行长时间的战斗后，我在 yield_per 文档中看到了这句话：“还要注意，yield_per() 将设置 stream_results 执行选项为 True，但目前只有 psycopg2 方言能够理解，它将使用服务器端游标流式传输结果，而不是预缓冲此查询的所有行。其他 DBAPI 在使它们可用之前会预缓冲所有行。” 解决方法在这里：https://dev59.com/UnA65IYBdhLWcg3wqQc8#3699677 - bcoughlan

Answer 2

以下是我通常处理这种情况的方法：

def page_query(q):
    offset = 0
    while True:
        r = False
        for elem in q.limit(1000).offset(offset):
           r = True
           yield elem
        offset += 1000
        if not r:
            break

for item in page_query(Session.query(Picture)):
    print item

这样可以避免DBAPI（如psycopg2和MySQLdb）进行的各种缓冲。如果查询中包含显式的JOIN，则仍需适当使用，但早加载的集合保证可以完全加载，因为它们被应用于具有实际LIMIT / OFFSET的子查询。

我注意到Postgresql返回大结果集的最后100行几乎需要和返回整个结果一样长的时间（除了实际的行获取开销），因为OFFSET只是简单地扫描整个结果集。

- zzzeek

---

我认为你可以在循环中消除一个赋值，例如： while True: elem = None; for elem .... offset += 1000; if elem==None: break; offset += 1000 - Dave

不，Dave，你需要在循环结束时设置elem = None，否则它永远不会终止。 - George V. Reilly

请注意，对查询进行切片将产生相同的效果：q[offset:offset+1000]。 - Martijn Pieters

请参考@zzzeek在类似主题上的最新答案……链接到维基上更多信息和代码。 - floer32

Answer 3

您可以推迟图片的获取，只在访问时检索它。您可以按查询逐个执行此操作。 例如：

session = Session()
for p in session.query(Picture).options(sqlalchemy.orm.defer("picture")):
    print(p)

或者你可以在映射器中完成它。

mapper(Picture, pictures, properties={
   'picture': deferred(pictures.c.picture)
})

如何实现在访问属性时才加载图片的方法可以在此处的文档中找到。

无论采用哪种方式，都可以确保只有在访问属性时才会加载图片。

- David Raznick

---

谢谢David，这很有趣。事实上，我刚刚改变了我的代码，使用from sqlalchemy.orm import deferred ... class Picture(object): ... imagedata = deferred(Column(Binary))这是一个很大的改进。然而，仍需要几秒钟才能输出第一个结果，因为查询会创建几千个Picture对象。我希望在SQLAlchemy遍历SQLite结果行时，逐个创建这些对象。 - Bluehorn

我认为大部分时间将花费在从数据库中检索数据而不是创建对象上。因此，查询本身需要有限制。因此，对于session.query（Picture）[0：5]中的p：print p。 - David Raznick