在SQLAlchemy中使用bulk_update_mappings更新具有不同值的多行 [英] Using bulk_update_mappings in SQLAlchemy to update multiple rows with different values

问题描述

我有两个桌子Foo和Bar.我刚刚在Bar表中添加了新列x，必须使用Foo中的值填充该列

I have two tables Foo and Bar. I just added a new column x to the Bar table which has to be populated using values in Foo

class Foo(Base):
    __table__ = 'foo'
    id = Column(Integer, primary_key=True)
    x = Column(Integer, nullable=False)

class Bar(Base):
    __table__ = 'bar'
    id = Column(Integer, primary_key=True)
    x = Column(Integer, nullable=False)
    foo_id = Column(Integer, ForeignKey('foo.id'), nullable=False)

一种简单的实现方法是遍历Bar中的所有行，然后一次一个地更新它们，但这要花很长时间(Foo和Bar中有超过10万行)

One straightforward way to do it would be iterating over all the rows in Bar and then updating them one-by-one, but it takes a long time (there are more than 100k rows in Foo and Bar)

for b, foo_x in session.query(Bar, Foo.x).join(Foo, Foo.id==Bar.foo_id):
    b.x = foo_x
session.flush()

现在我想知道这样做是否正确-

Now I was wondering if this would be right way to do it -

mappings = []
for b, foo_x in session.query(Bar, Foo.x).join(Foo, Foo.id==Bar.foo_id):
    info = {'id':b.id, 'x': foo_x}
    mappings.append(info)
session.bulk_update_mappings(Bar, mappings)

bulk_update_mappings上没有太多示例.文档建议

There are not much examples on bulk_update_mappings out there. The docs suggest

所有存在但不属于主键的那些键 应用于UPDATE语句的SET子句；主键 必需的值将应用于WHERE子句.

All those keys which are present and are not part of the primary key are applied to the SET clause of the UPDATE statement; the primary key values, which are required, are applied to the WHERE clause.

因此，在这种情况下，将在WHERE子句中使用id，然后使用字典右侧的x值进行更新?

So, in this case id will be used in the WHERE clause and then that would be updates using the x value in the dictionary right ?

推荐答案

该方法在用法上是正确的.我唯一要更改的是类似以下的内容

The approach is correct in terms of usage. The only thing I would change is something like below

mappings = []
i = 0

for b, foo_x in session.query(Bar, Foo.x).join(Foo, Foo.id==Bar.foo_id):
    info = {'id':b.id, 'x': foo_x}
    mappings.append(info)
    i = i + 1
    if i % 10000 == 0:
        session.bulk_update_mappings(Bar, mappings)
        session.flush()
        session.commit()
        mappings[:] = []

session.bulk_update_mappings(Bar, mappings)

这将确保您没有太多的数据挂在内存中，并且一次也不会对数据库进行太大的插入

This will make sure you don't have too much data hanging in memory and you don't do a too big insert to the DB at a single time

这篇关于在SQLAlchemy中使用bulk_update_mappings更新具有不同值的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！