Error from psycopy2 with UUID-array column type

Question

I have trouble inserting UUID values into a UUID-array column in Postgres.

Since psycopg2 doesn't automatically convert Python UUID values to Postgres UUID, I use something like str(my_id)

However this is the first time I am using any array-type column. The error I get is:

psycopg2.ProgrammingError: column "items" is of type uuid[] but expression is of type text[]
LINE 3: ...0', NULL, '01adae1e-e2cf-11e4-b773-ac8112c8e1fb', ARRAY['01a...
                                                             ^
HINT:  You will need to rewrite or cast the expression.

I've googled this error and found some (eg bug 2279, bug 2277) Postgres bug reports that hint at incomplete/missing implementation of array features but I don't know whether these relate to my problem. These however seem to have been resolved. FWIW I'm using Postgres 9.3.6

To debug this I created a small test table, eg:

testdb=# \d test_table
    Table "public.test_table"
  Column   |  Type   | Modifiers 
-----------+---------+-----------
 key       | integer | 
 item_list | uuid[]  |

And the output from my talkative test:

DEBUGGING:
Statement:  INSERT INTO test_table
    ("item_list", "key")
    VALUES (%s, %s);
Arguments:  [['2f286cea-e34b-11e4-9b5e-2c768ada7efb', '2f28dd7e-e34b-11e4-9b5e-2c768ada7efb'], 1]
Active Role: test1
Mogrified SQL Expression:
INSERT INTO test_table
    ("item_list", "key")
    VALUES (ARRAY['2f286cea-e34b-11e4-9b5e-2c768ada7efb', '2f28dd7e-e34b-11e4-9b5e-2c768ada7efb'], 1);
Exception ProgrammingError occured. Arguments:
    ('column "item_list" is of type uuid[] but expression is of type text[]\nLINE 3:     VALUES (ARRAY[\'2f286cea-e34b-11e4-9b5e-2c768ada7efb\', \'2...\n                    ^\nHINT:  You will need to rewrite or cast the expression.\n',)
   * * * *   WARNING: Command Failure on Running Statement 
 INSERT INTO test_table
    ("item_list", "key")
    VALUES (%s, %s);!

ERROR:  column "item_list" is of type uuid[] but expression is of type text[]
LINE 3:     VALUES (ARRAY['2f286cea-e34b-11e4-9b5e-2c768ada7efb', '2...
                    ^
HINT:  You will need to rewrite or cast the expression.

None
Traceback (most recent call last):
  File "db-test.py", line 214, in <module>
    db_exec(cursor, stmt, args, debug=True, re_raise=True)
  File "db-test.py", line 62, in db_exec
    cur.execute(stmt, args)
psycopg2.ProgrammingError: column "item_list" is of type uuid[] but expression is of type text[]
LINE 3:     VALUES (ARRAY['2f286cea-e34b-11e4-9b5e-2c768ada7efb', '2...
                    ^
HINT:  You will need to rewrite or cast the expression.

The only other thing I've tried is to use uuid raw but I get the expected "Can't adapt" error.

The exception DOES occur one line earlier in my test program, eg

DEBUGGING:
Statement:  INSERT INTO test_table
    ("item_list", "key")
    VALUES (%s, %s);
Arguments:  [[UUID('2bfaf276-e34c-11e4-8af5-2c768ada7efb'), UUID('2bfb6d82-e34c-11e4-8af5-2c768ada7efb')], 1]
Active Role: test1
Mogrified SQL Expression:
Exception ProgrammingError occured. Arguments:
    ("can't adapt type 'UUID'",)
   * * * *   WARNING: Command Failure on Running Statement 
 INSERT INTO test_table
    ("item_list", "key")
    VALUES (%s, %s);!

None
None
Traceback (most recent call last):
  File "db-test.py", line 214, in <module>
    db_exec(cursor, stmt, args, debug=True, re_raise=True)
  File "db-test.py", line 61, in db_exec
    print cur.mogrify(stmt, args)
psycopg2.ProgrammingError: can't adapt type 'UUID'

The relevant part of the test program looks like this:

46  def db_exec(cur, stmt, args=None, re_raise=True, debug=False, assume_role=None):
47      if (cur is None) or (cur is False):
48          print "ERROR : DB Connection/Cursor is not open for commands"
49          return cur
50      if (debug is True):
51          print "DEBUGGING:"
52          print "Statement: ", stmt
53          if args is not None:
54              print "Arguments: ", args
55      try:
56          if assume_role is not None:
57              cur.execute('SET ROLE "%s";' % assume_role)
58          if debug is True:
59              print "Active Role:", GetCurrentRole(cur)
60              print "Mogrified SQL Expression:"
61              print cur.mogrify(stmt, args)
62          cur.execute(stmt, args)
[...snip...]

I have seen that there exists a way to add custom "autmatic" type conversions for Python <--> Postgres and wonder whether the solution may lie in there... but I don't know how to do that (yet)

Answer 1

I decided to investigate the psycopg2 extras and found a solution that involves just one extra line of code.

The hint came from here, found near the bottom of that page in the section under the heading "UUID data type"

The line is:

psycopg2.extras.register_uuid()

Despite my pesimism about this work for both simple AND array UUID types, it does so just fine. In addition this means I no longer need to manually convert every UUID to a str. (The reverse is not an issue in my application but presumably it would also work for retrieving UUIDs and having them registered as UUID type in python)