title: Bulk update of rows in Postgres DB using psycopg2
body: <p>We need to do bulk updates of many rows in our Postgres DB, and want to use the SQL syntax below. How do we do that using psycopg2?</p>
  
  <pre><code>UPDATE table_to_be_updated
  SET msg = update_payload.msg
  FROM (VALUES %(update_payload)s) AS update_payload(id, msg)
  WHERE table_to_be_updated.id = update_payload.id
  RETURNING *
  </code></pre>
  
  <p><strong>Attempt 1 - Passing values</strong></p>
  
  <p>We need to pass a nested iterable format to the psycopg2 query. For the <code>update_payload</code>, I've tried passing a list of lists, list of tuples, and tuples of tuples. It all fails with various errors.</p>
  
  <p><strong>Attempt 2 - Writing custom class with __conform__</strong></p>
  
  <p>I've tried to write a custom class that we can use for these operations, which would return </p>
  
  <pre><code>(VALUES (row1_col1, row1_col2), (row2_col1, row2_col2), (...))
  </code></pre>
  
  <p>I've coded up like this following instructions <a href="http://initd.org/psycopg/docs/advanced.html#adapting-new-types" rel="noreferrer">here</a>, but it's clear that I'm doing something wrong. For instance, in this approach I'll have to handle quoting of all values inside the table, which would be cumbersome and prone to errors.</p>
  
  <pre><code>class ValuesTable(list):
      def __init__(self, *args, **kwargs):
          super(ValuesTable, self).__init__(*args, **kwargs)
  
      def __repr__(self):
          data_in_sql = ""
          for row in self:
              str_values = ", ".join([str(value) for value in row])
              data_in_sql += "({})".format(str_values)
          return "(VALUES {})".format(data_in_sql)
  
      def __conform__(self, proto):
          return self.__repr__()
  
      def getquoted(self):
          return self.__repr__()
  
      def __str__(self):
          return self.__repr__()
  </code></pre>
  
  <p><strong>EDIT:</strong> If doing a bulk update can be done in a faster/cleaner way using another syntax than the one in my original question, then I'm all ears!</p>