Bulk update Postgres column from python dataframe
<p>I am using the below python code to update postgres DB column <code>value</code>based on <code>Id</code>. This loop has to run for thousands of records and it is taking longer time. </p>

<p>Is there a way where I can pass array of dataframe values instead of looping each row?</p>

<pre><code> for i in range(0,len(df)):
        QUERY=""" UPDATE "Table" SET "value"='%s' WHERE "Table"."id"='%s'
            """ % (df['value'][i], df['id'][i])
        cur.execute(QUERY)
        conn.commit()
</code></pre>

<p>Depends on a library you use to communicate with PostgreSQL, but usually bulk inserts are much faster via <a href="https://www.postgresql.org/docs/9.5/static/sql-copy.html" rel="nofollow">COPY FROM</a> command.</p>

<p>If you use psycopg2 it is as simple as following:</p>

<pre><code>cursor.copy_from(io.StringIO(string_variable), "destination_table", columns=('id', 'value'))
</code></pre>

<p>Where <strong>string_variable</strong> is tab and new line delimited dataset like <code>1\tvalue1\n2\tvalue2\n</code>.</p>

<p>To achieve a performant bulk update I would do: </p>

<ol>
<li><p>Create a temporary table: <code>CREATE TEMPORARY TABLE tmp_table;</code>; </p></li>
<li><p>Insert records with <strong>copy_from</strong>;</p></li>
<li><p>Just update destination table with query <code>UPDATE destination_table SET value = t.value FROM tmp_table t WHERE id = t.id</code> or any <a href="https://www.postgresql.org/docs/9.5/static/sql-update.html" rel="nofollow">other preferred syntax</a></p></li>
</ol>

python sql postgresql bulkupdate