Using bulk_update_mappings in SQLAlchemy to update multiple rows with different values
<p>I have two tables Foo and Bar. I just added a new column <code>x</code> to the Bar table which has to be populated using values in Foo</p>

<pre><code>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)
</code></pre>

<p>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)</p>

<pre><code>for b, foo_x in session.query(Bar, Foo.x).join(Foo, Foo.id==Bar.foo_id):
    b.x = foo_x
session.flush()
</code></pre>

<p>Now I was wondering if this would be right way to do it - </p>

<pre><code>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)
</code></pre>

<p>There are not much examples on <code>bulk_update_mappings</code> out there. The docs suggest </p>

<blockquote>
  <p>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.</p>
</blockquote>

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