<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">My goal is to update many rows at once on a Postgres database using sqlalchemy. My setting is :</p>
<ul dir="auto">
<li>I have a table created with <code class="notranslate">sqlalchemy.Table(...)</code></li>
<li>The primary key is, say, <code class="notranslate">a</code>, <code class="notranslate">b</code></li>
<li>There are some other fields <code class="notranslate">c</code> and <code class="notranslate">d</code></li>
</ul>
<p dir="auto">I have created all the rows with the primary key setup, and need to add each column (respectively c and d) separately. Thus, I'm looking to give sqlalchemy a dict like <code class="notranslate">{ 'a': ..., 'b': ..., 'c': f(a, b) }</code> for many values of a and b. However, I cannot figure out a way to do this without doing it row by row (which is very expensive and time consuming).</p>
<p dir="auto">I have tried the <code class="notranslate">Session.bulk_update_mappings</code> approach, but since my Table is not from the ORM system but rather from the Core, I don't have a mapper, and sqlalchemy complains, utlimately failing to update my rows (<a href="https://stackoverflow.com/a/36760560/5016244" rel="nofollow">SO reference</a>).</p>
<p dir="auto">I also tried the Core approach, without success (<a href="https://docs.sqlalchemy.org/en/13/core/tutorial.html#executing-multiple-statements" rel="nofollow">documentation reference</a>). Indeed, I fail to see how to do it with an Update statement. I have tried like so,</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="    stmt = self._table.update()
    self._engine.execute(stmt, [{'a': 0, 'b': 0, 'c': 10}, ...])"><pre class="notranslate">    <span class="pl-s1">stmt</span> <span class="pl-c1">=</span> <span class="pl-s1">self</span>.<span class="pl-s1">_table</span>.<span class="pl-en">update</span>()
    <span class="pl-s1">self</span>.<span class="pl-s1">_engine</span>.<span class="pl-en">execute</span>(<span class="pl-s1">stmt</span>, [{<span class="pl-s">'a'</span>: <span class="pl-c1">0</span>, <span class="pl-s">'b'</span>: <span class="pl-c1">0</span>, <span class="pl-s">'c'</span>: <span class="pl-c1">10</span>}, ...])</pre></div>
<p dir="auto">But this complains that there are duplicate keys that violate the unique constraint <code class="notranslate">table_name_pkey</code>, and I believe that it is due to the fact that it doesn't try to find rows matching the pkey and update remaining fields...</p>
<p dir="auto">Anyways, I can't figure out a way to do this properly and with sufficiently good performances.</p>
<p dir="auto">I thank you in advance for your kind replies and your help.</p>