SQLAlchemy upsert with ON CONFLICT DO UPDATE

<p>I need to insert records but update them if they already exist (upsert). This is common for incrementing counters, updating reputation scores, or syncing external data. I need the PostgreSQL-specific ON CONFLICT DO UPDATE pattern.</p>

<p>Use PostgreSQL dialect's insert with on_conflict_do_update:</p> <div class="highlight"><pre><span></span><code><span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy.dialects.postgresql</span><span class="w"> </span><span class="kn">import</span> <span class="n">insert</span> <span class="k">as</span> <span class="n">pg_insert</span> <span class="kn">from</span><span class="w"> </span><span class="nn">sqlalchemy</span><span class="w"> </span><span class="kn">import</span> <span class="n">select</span> <span class="k">async</span> <span class="k">def</span><span class="w"> </span><span class="nf">upsert_domain_reputation</span><span class="p">(</span> <span class="n">session</span><span class="p">:</span> <span class="n">AsyncSession</span><span class="p">,</span> <span class="n">user_id</span><span class="p">:</span> <span class="n">uuid</span><span class="o">.</span><span class="n">UUID</span><span class="p">,</span> <span class="n">domain</span><span class="p">:</span> <span class="nb">str</span><span class="p">,</span> <span class="n">delta</span><span class="p">:</span> <span class="nb">float</span><span class="p">,</span> <span class="p">)</span> <span class="o">-&gt;</span> <span class="kc">None</span><span class="p">:</span> <span class="n">stmt</span> <span class="o">=</span> <span class="n">pg_insert</span><span class="p">(</span><span class="n">ContributorDomainReputation</span><span class="p">)</span><span class="o">.</span><span class="n">values</span><span class="p">(</span> <span class="n">contributor_id</span><span class="o">=</span><span class="n">user_id</span><span class="p">,</span> <span class="n">domain_tag</span><span class="o">=</span><span class="n">domain</span><span class="p">,</span> <span class="n">reputation_score</span><span class="o">=</span><span class="n">delta</span><span class="p">,</span> <span class="n">vote_count</span><span class="o">=</span><span class="mi">1</span><span class="p">,</span> <span class="p">)</span> <span class="n">stmt</span> <span class="o">=</span> <span class="n">stmt</span><span class="o">.</span><span class="n">on_conflict_do_update</span><span class="p">(</span> <span class="n">index_elements</span><span class="o">=</span><span class="p">[</span><span class="s1">'contributor_id'</span><span class="p">,</span> <span class="s1">'domain_tag'</span><span class="p">],</span> <span class="n">set_</span><span class="o">=</span><span class="p">{</span> <span class="s1">'reputation_score'</span><span class="p">:</span> <span class="n">ContributorDomainReputation</span><span class="o">.</span><span class="n">reputation_score</span> <span class="o">+</span> <span class="n">delta</span><span class="p">,</span> <span class="s1">'vote_count'</span><span class="p">:</span> <span class="n">ContributorDomainReputation</span><span class="o">.</span><span class="n">vote_count</span> <span class="o">+</span> <span class="mi">1</span><span class="p">,</span> <span class="s1">'updated_at'</span><span class="p">:</span> <span class="n">func</span><span class="o">.</span><span class="n">now</span><span class="p">(),</span> <span class="p">}</span> <span class="p">)</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span> <span class="c1"># ON CONFLICT DO NOTHING (idempotent insert):</span> <span class="n">stmt</span> <span class="o">=</span> <span class="n">pg_insert</span><span class="p">(</span><span class="n">Tag</span><span class="p">)</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">'python'</span><span class="p">)</span> <span class="n">stmt</span> <span class="o">=</span> <span class="n">stmt</span><span class="o">.</span><span class="n">on_conflict_do_nothing</span><span class="p">(</span><span class="n">index_elements</span><span class="o">=</span><span class="p">[</span><span class="s1">'name'</span><span class="p">])</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span> <span class="c1"># Get the value after upsert (RETURNING):</span> <span class="n">stmt</span> <span class="o">=</span> <span class="n">pg_insert</span><span class="p">(</span><span class="n">Tag</span><span class="p">)</span><span class="o">.</span><span class="n">values</span><span class="p">(</span><span class="n">name</span><span class="o">=</span><span class="s1">'python'</span><span class="p">)</span><span class="o">.</span><span class="n">on_conflict_do_update</span><span class="p">(</span> <span class="n">index_elements</span><span class="o">=</span><span class="p">[</span><span class="s1">'name'</span><span class="p">],</span> <span class="n">set_</span><span class="o">=</span><span class="p">{</span><span class="s1">'name'</span><span class="p">:</span> <span class="s1">'python'</span><span class="p">},</span> <span class="c1"># no-op update to trigger RETURNING</span> <span class="p">)</span><span class="o">.</span><span class="n">returning</span><span class="p">(</span><span class="n">Tag</span><span class="o">.</span><span class="n">id</span><span class="p">)</span> <span class="n">result</span> <span class="o">=</span> <span class="k">await</span> <span class="n">session</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span><span class="n">stmt</span><span class="p">)</span> <span class="n">tag_id</span> <span class="o">=</span> <span class="n">result</span><span class="o">.</span><span class="n">scalar_one</span><span class="p">()</span> </code></pre></div> <p>Key points: - <code>index_elements</code> must match a unique constraint or index - <code>set_</code> uses the model class for column references (not string column names) - <code>excluded</code> pseudo-table contains the values that would have been inserted - Atomically increment counters: <code>col = col + excluded.col</code></p>