SqlAlchemy: array of Postgresql custom types
Question: So in my postgres DB I have the following custom type:

    
    
    create type my_pg_type as (  
        sting_id varchar(32),
        time_diff interval,
        multiplier integer
    );
    

To further complicate things, this is being used as an array:

    
    
    alter table my_table add column my_keys my_pg_type [];
    

I'd like to map this with SQLAlchemy (0.6.4) !!

(apologies for elixir)

    
    
    from sqlalchemy.dialects.postgresql import ARRAY
    from sqlalchemy.types import Enum
    from elixir import Entity, Field    
    
    class MyTable(Entity):
        # -- snip --
        my_keys = Field(ARRAY(Enum))
    

I know 'Enum' is incorrect in the above.

For an example of a value coming back from the database for that array column,
I've shown below the value in `ARRAY.result_processor(self, dialect,
coltype)`:

    
    
    class ARRAY(sqltypes.MutableType, sqltypes.Concatenable, sqltypes.TypeEngine):
        # -- snip --  
        def result_processor(self, dialect, coltype):
            item_proc = self.item_type.result_processor(dialect, coltype)
            if item_proc:
                def convert_item(item):
                    if isinstance(item, list):
                        return [convert_item(child) for child in item]
                    else:
                        return item_proc(item)
            else:
                def convert_item(item):
                    if isinstance(item, list):
                        return [convert_item(child) for child in item]
                    else:
                        return item
            def process(value):
                if value is None:
                    return value
                """
                # sample value:
                 >>> value
                '{"(key_1,07:23:00,0)","(key_2,01:00:00,20)"}'
                """
                return [convert_item(item) for item in value]
            return process
    

So the above `process` function incorrectly splits the string, assuming it's
already a list.

So far, I've successfully subclassed ARRAY to properly split the string, and
instead of Enum, I've tried to write my own type (implementing Unicode) to
recreate the `(string, timedelta, integer)` tuple, but have run into a lot of
difficulties, specifically the proper conversion of the `interval` to the
Python `timedelta`.

I'm posting here in case I'm missing an obvious precedent way of doing this?


Answer: **UPDATE** See the recipe at the bottom for a workaround

I worked up some example code to see what psycopg2 is doing here, and this is
well within their realm - psycopg2 is not interpreting the value as an array
at all. psycopg2 needs to be able to parse out the ARRAY when it comes back as
SQLA's ARRAY type assumes at least that much has been done. You can of course
hack around SQLAlchemy's ARRAY, which here would mean basically not using it
at all in favor of something that parses out this particular string value
psycopg2 is giving us back.

But what's also happening here is that we aren't even getting at psycopg2's
mechanics for converting timedeltas either, something SQLAlchemy normally
doesn't have to worry about. In this case I feel like the facilities of the
DBAPI are being under-utilized and psycopg2 is a very capable DBAPI.

So I'd advise you work with psycopg2's custom type mechanics over at
<http://initd.org/psycopg/docs/extensions.html#database-types-casting-
functions>.

If you want to mail their [mailing
list](http://mail.postgresql.org/mj/mj_wwwusr/domain=postgresql.org?func=lists-
long-full&extra=psycopg), here's a test case:

    
    
    import psycopg2
    
    conn = psycopg2.connect(host="localhost", database="test", user="scott", password="tiger")
    cursor = conn.cursor()
    cursor.execute("""
    create type my_pg_type as (  
        string_id varchar(32),
        time_diff interval,
        multiplier integer
    )
    """)
    
    cursor.execute("""
        CREATE TABLE my_table (
            data my_pg_type[]
        )
    """)
    
    cursor.execute("insert into my_table (data) "
                "values (CAST(%(data)s AS my_pg_type[]))", 
                {'data':[("xyz", "'1 day 01:00:00'", 5), ("pqr", "'1 day 01:00:00'", 5)]})
    
    cursor.execute("SELECT * from my_table")
    row = cursor.fetchone()
    assert isinstance(row[0], (tuple, list)), repr(row[0])
    

PG's type registration supports global registration. You can also register the
types on a per-connection basis within SQLAlchemy using the [pool
listener](http://docs.sqlalchemy.org/en/rel_0_6/core/interfaces.html#connection-
pool-events) in 0.6 or [connect
event](http://docs.sqlalchemy.org/en/latest/core/events.html#sqlalchemy.events.PoolEvents.connect)
in 0.7 and further.

**UPDATE** \- due to
<https://bitbucket.org/zzzeek/sqlalchemy/issue/3467/array-of-enums-does-not-
allow-assigning> I'm probably going to recommend people use this workaround
type for now, until psycopg2 adds more built-in support for this:

    
    
    class ArrayOfEnum(ARRAY):
    
        def bind_expression(self, bindvalue):
            return sa.cast(bindvalue, self)
    
        def result_processor(self, dialect, coltype):
            super_rp = super(ArrayOfEnum, self).result_processor(dialect, coltype)
    
            def handle_raw_string(value):
                inner = re.match(r"^{(.*)}$", value).group(1)
                return inner.split(",")
    
            def process(value):
                return super_rp(handle_raw_string(value))
            return process
    

