Psycopg2: I have bug with array of composite types

[psycoteam]

Originally submitted by: Psycopg website

Submitted by: Nikita Kuznetsov

import psycopg2
import psycopg2.extras


conn = psycopg2.connect("dbname=test user=y")
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS test_comp_table;")
cur.execute("DROP TYPE IF EXISTS test_comp_type;")
cur.execute("CREATE TYPE test_comp_type AS (first text, second text);")
psycopg2.extras.register_composite("test_comp_type", conn, globally=True)
cur.execute("CREATE TABLE test_comp_table (data test_comp_type[]);")
try:
    cur.execute("INSERT INTO test_comp_table VALUES (%(data)s)",
                dict(data=[("first", "second")]))
except:
    print(cur.query)
    raise

"""
b"INSERT INTO test_comp_table VALUES (ARRAY[('first', 'second')])"
Traceback (most recent call last):
  File "../common/test.py", line 14, in <module>
    dict(data=[("first", "second")]))
psycopg2.ProgrammingError: column "data" is of type test_comp_type[] but expression is of type record[]
LINE 1: INSERT INTO test_comp_table VALUES (ARRAY[('first', 'second'...
                                            ^
HINT:  You will need to rewrite or cast the expression.
"""

[dvarrazzo]

Try adding an explicit cast after your placeholder specifying that the type you are passing is an array of test_comp_type. This should work:

cur.execute("INSERT INTO test_comp_table VALUES (%(data)s::test_comp_type[])",
            dict(data=[("first", "second")]))

[psycoteam]

Originally submitted by: Nikita Kuznetsov

No, I want autocast, because I use sqlalchemy and I work througth sqlalchemy interface.

[dvarrazzo]

In this case you should create your data as a specific python type, it cannot be a generic tuple, and register a typecaster for it. For instance:

Comp = namedtuple('Comp', 'first second')

class CompAdapter:
    def __init__(self, x):
        self.adapted = psycopg2.extensions.SQL_IN(x)
    def prepare(self, conn):
        self.adapted.prepare(conn)
    def getquoted(self):
        return self.adapted.getquoted() + '::test_comp_type'

psycopg2.extensions.register_adapter(Comp, CompAdapter)

# this now works
cur.execute("INSERT INTO test_comp_table VALUES (%(data)s)",
    {'data': [ Comp('a', 'b'), Comp('c', 'd') ]})

[tomaszalusky]

Just found https://github.com/psycopg/psycopg2/issues/231#issuecomment-53741200 .
Very helpful, I just had to update it for Python 3:

    def getquoted(self):
        return (self.adapted.getquoted().decode('utf-8') + '::test_comp_type').encode('utf-8')

[dvarrazzo]

@tomaszalusky why decode and encode instead of just adding bytes?