psycopg2 execute_values with list of class objects

Question

I have a class Person with id, fname, and lname. My program will have a large list of Person objects, which I would like to insert into my PostgreSQL database.

I would like to do this through psycopg2.execute_values method, which requires __getitem__ to be defined for my class.

__getitem__ is currently returning tuple(id, fname, lname), which results in pscopg2.execute_values trying to execute: insert into persons(id, fname, lname) values ((1, 'test1', 'test1'))...

import psycopg2.extras


class Person:
    def __init__(self, id, fname, lname):
        self.id = id
        self.fname = fname
        self.lname = lname

    def __len__(self):
        return 1

    def __getitem__(self, item):
        return self.id, self.fname, self.lname


dsn = "user=my_user host=localhost port=5432 dbname=my_db"
query = "insert into persons(id, fname, lname) values %s"

p1 = Person(1, 'test1', 'test1')
p2 = Person(2, 'test2', 'test2')

records = list()
records.append(p1)
records.append(p2)
with psycopg2.connect(dsn=dsn) as conn:
    with conn.cursor() as cursor:
        psycopg2.extras.execute_values(cursor, query, records, template=None, page_size=1000)

psycopg2.ProgrammingError: INSERT has more target columns than expressions
LINE 1: insert into persons(id, fname, lname) values ((1, 'test1', 'test1'))...
                                ^
HINT:  The insertion source is a row expression containing the same number of columns expected by the INSERT. Did you accidentally use extra parentheses?

Using a namedtuple works:

from collections import namedtuple
import psycopg2.extras


Person = namedtuple('Person', ['id', 'fname', 'lname'])

dsn = "user=my_user host=localhost port=5432 dbname=my_db"
query = "insert into persons(id, fname, lname) values %s"

p1 = Person(1, 'test1', 'test1')
p2 = Person(2, 'test2', 'test2')

records = list()
records.append(p1)
records.append(p2)

with psycopg2.connect(dsn=dsn) as conn:
    with conn.cursor() as cursor:
        psycopg2.extras.execute_values(cursor, query, records, template=None, page_size=1000)

However, I want to implement a different __eq__ and __hash__ implementation. If there is a way to override these methods for a namedtuple then I'm willing to go with that approach.

Otherwise how can I modify __getitem__ to return id, fname, and lname separately so that the query becomes:

insert into persons(id, fname, lname) values (1, 'test1', 'test1')...

Answer 1

Defining __conform__ seems to do the trick: https://www.python.org/dev/peps/pep-0246/

import psycopg2.extras
from psycopg2.extensions import AsIs, ISQLQuote


class Person:
    def __init__(self, id, fname, lname):
        self.id = id
        self.fname = fname
        self.lname = lname

    def __len__(self):
        return 1

    def __getitem__(self, key):
        return self

    def __conform__(self, protocol):
        if protocol is ISQLQuote:
            return AsIs("{id}, '{fname}', '{lname}'".format(id=self.id, fname=self.fname, lname=self.lname))

        return None


dsn = "user=my_user host=localhost port=5432 dbname=my_db"
query = "insert into persons(id, fname, lname) values %s"

p1 = Person(1, 'test1', 'test1')
p2 = Person(2, 'test2', 'test2')

records = list()
records.append(p1)
records.append(p2)

with psycopg2.connect(dsn=dsn) as conn:
    with conn.cursor() as cursor:
        psycopg2.extras.execute_values(cursor, query, records, template=None, page_size=1000)

Unsure if this is the recommended method, so I will keep this open in hopes that someone chimes in.