An adapter for psycopg2 to pass a sequence of objects to a VALUES list.

values.py

import psycopg2.extensions as ext

class Values(object):
    """Wrapper to pass a sequence of objects as VALUES argument"""
    def __init__(self, seq):
        self.seq = map(ext.adapt, seq)

    def __conform__(self, proto):
        if proto is ext.ISQLQuote:
            return self

    def prepare(self, conn):
        try:
            for a in self.seq:
                a.prepare(conn)
        except AttributeError:
            # assume all the adapters are the same: bail out at the first error
            pass

    def getquoted(self):
        q = [ a.getquoted() for a in self.seq ]
        q[0] = '(%s' % q[0]
        q[-1] = '%s)' % q[-1]
        return '),('.join(q)


if __name__ == '__main__':
    # create a test table with:
    #
    #   create table testvalues as select i as id, i::text || i::text as data
    #   from generate_series(1,1000) s(i);
    #
    import psycopg2
    cnn = psycopg2.connect('')
    cur = cnn.cursor()
    cur.execute("select * from testvalues where id = any(values %s)",
                [Values(range(100))])
    print cur.fetchall()

References:

• http://www.datadoghq.com/2013/08/100x-faster-postgres-performance-by-changing-1-line/
• http://www.postgresql.org/message-id/[email protected]