current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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, 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 in 0.6 or connect event in 0.7 and further.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.