python - SqlAlchemy映射的批量更新-更安全，更快捷？

Question

我用的是Postgres 9.2和SqlAlchemy。目前，这是我更新数据库中我的Thing排名的代码：

lock_things = session.query(Thing).\
                filter(Thing.group_id == 4).\
                with_for_update().all()

tups = RankThings(lock_things) # return sorted tuple (<numeric>, <primary key Thing id>)
rank = 1
for prediction, id in tups:
    thing = session.query(Thing).\
        filter(Thing.group_id == 4).\
        filter(Thing.id == id).one()
    thing.rank = rank
    rank += 1

session.commit()

然而，这似乎是缓慢的。这也是我想要成为原子的东西，这也是我为什么使用 with_for_update()语法的原因。
我觉得必须有一种方法来“压缩”查询，并以这种方式更新。
我怎样才能更快地完成所有的查询？
编辑：我想我需要创建一个临时表来连接并快速更新，请参见：
https://stackoverflow.com/a/20224370/712997
http://tapoueh.org/blog/2013/03/15-batch-update
在SqlAlchemy中如何做到这一点？

Answer 1

一般来说，这种手术的目的有两个：
不要在循环内执行查询
减少在SQL端执行计算所需的查询数
此外，如果可能的话，您可能希望合并一些查询。
让我们从2开始），因为这是非常具体的，而且通常不容易实现。通常，这里最快的操作是编写一个返回秩的查询。有两种选择：
查询运行起来很快，所以只要需要排名就可以执行它。这种情况很简单：

SELECT 
    thing.*, 
    (POINTS_QUERY) as score
FROM thing
ORDER BY score DESC

在这种情况下，这将通过一些人为的分数给你一个有序的列表（例如，如果你建立了某种竞争）。 POINTS_QUERY将是在子查询中使用特定的 thing来确定其分数的东西，例如，聚合它已解决的所有任务的分数。
在SQLAlchemy中，如下所示：

score = session.query(func.sum(task.points)).filter(task.thing_id == Thing.id).correlate(Thing).label("score")
thing_ranking = session.query(thing, score).order_by(desc("score"))

这是SQLAlchemy更高级的用法：我们构造一个子查询，返回我们标记的标量值 score。使用 correlate我们告诉它 thing将来自外部查询（这很重要）。
因此，在这种情况下，您只需运行一个查询，就可以获得排名（排名a是根据列表中的索引确定的，取决于您的 ranking strategy）。如果你能做到这一点，这是最好的情况
要缓存这些值，查询本身就很昂贵。这意味着您可以使用上面的解决方案并将值缓存在数据库之外（例如，在dict中或使用缓存库）。或者像上面那样计算它们，但是更新一个数据库字段（比如 Thing.rank）。同样，上面的查询给出了排名。另外，我假设最简单的排序：索引表示排序：

for rank, (thing, score) in enumerate(thing_ranking):
     thing.rank = rank

请注意，我是如何使用 enumerate根据索引建立排名的。此外，我还利用了这样一个事实：由于我刚刚查询了 thing，所以我已经在会话中拥有它，因此不需要额外的查询。所以这可能是你的解决方案，但请继续阅读以获取更多信息。
使用上面的最后一个想法，我们现在可以处理1）：在循环之外获取查询。一般来说，我注意到您将一个事物列表传递给一个排序函数，该函数似乎只返回id。为什么？如果你能改变它，使它返回作为一个整体的东西。
但是，您可能无法更改此函数，因此让我们考虑一下如果无法更改它，我们将如何操作。我们已经有一份所有相关事情的清单。我们得到了他们的身份证的分类列表。那么为什么不构建一个 dict作为ID>的查找呢？

things_dict = dict(thing.id, thing for thing in lock_things)

我们可以使用这个dict而不是在循环中查询：

for prediction, id in tups:
    thing = things_dict[id]

但是，可能（由于某些原因，我在您的示例中遗漏了）并不是所有的id都是以前返回的。在这种情况下（或通常情况下），您可以利用SQLAlchemy保留的类似映射：您可以要求它提供主键，如果它已经拥有主键，它将不会查询数据库：

for prediction, id in tups:
    thing = session.query(Thing).get(id)

这样我们就减少了问题，只对我们还没有的对象执行查询。
最后一件事：如果我们没有大部分的东西呢？然后我没有解决你的问题，我只是替换了查询。在这种情况下，您将不得不创建一个新的查询来获取所需的所有元素。一般来说，这取决于id的来源和它们是如何确定的，但是您总是可以采用效率最低的方法（这仍然比内部循环查询快得多）：使用SQL的 IN：

all_things = session.query(Thing).filter(Thing.group_id == 4).filter(Thing.id.in_([id for _, id in tups]).all()

这将构造一个用 IN关键字过滤的查询。然而，由于有大量的事情，这是非常低效的，因此如果您是在这种情况下，它很可能是最好的您构建一些更有效的方式，在SQL中，确定这是否是一个您想要的ID。
摘要
所以这是一段很长的文字。总结一下：
如果可以在那里高效地编写查询，请尽可能多地在SQL中执行查询
利用SQLAlchemy的优点，例如创建子查询
尝试从不在循环中执行查询
为您自己创建一些映射（或者使用SQLAlchemy的映射来发挥您的优势）
用蟒蛇式的方法：保持简单，保持明确。
最后一个想法是：如果你的查询变得非常复杂，并且你担心你对ORM执行的查询失去控制，那么放弃它，转而使用Core。它几乎和ORM一样棒，并且在您自己构建查询时，为您提供了对查询的大量控制。有了它，您几乎可以构造任何您能想到的SQL查询，我确信您提到的批处理更新在这里也是可能的（如果您看到我上面的查询导致许多 UPDATE语句，您可能希望使用Core）。