that adapter works both ways, so you can drop the custom type completely:
table = Table('example_2', metadata,
Column('timestamp', DateTime(timezone=False), primary_key=True),
Column('num', Integer),
Column('guids', ARRAY(UUID, dimensions=1))
)
On Jan 2, 2013, at 2:10 PM, Michael van Tellingen wrote:
> Again, thanks for the detailed response! I went with the
> psycopg2.extras.register_uuid() method and that works without problems
> for now.
> I did have to remove the as_uuid=True since psycopg2 already returned
> it as an uuid type.
>
> For future references the working test code is here
> https://gist.github.com/4433940
>
> Thanks again,
> Michael
>
>
> On Wed, Jan 2, 2013 at 7:21 PM, Michael Bayer <[email protected]>
> wrote:
>> psycopg2 does have some adapters for this:
>>
>> http://initd.org/psycopg/docs/extras.html?highlight=uuid#uuid-data-type
>>
>> ideally using those would work transparently with ARRAY types, but its not
>> clear if they do or not. They should, or if not you'd probably need to
>> build a more comprehensive type adapter. Or maybe that would be a psycopg2
>> bug. If you email their list, make sure the code you illustrate for them
>> uses only psycopg2 and nothing else.
>>
>> We can of course continue to work around this on the SQLAlchemy side, though
>> I'd rather not make a workaround of this degree a default behavior of the
>> SQLAlchemy dialect.
>>
>> In this case, the _PGArray type needs to be totally subverted to intercept
>> psycopg's non-handling of the data:
>>
>> from sqlalchemy.dialects.postgresql import psycopg2
>>
>> class UuidArrayFixer(psycopg2._PGArray):
>> def result_processor(self, dialect, coltype):
>> def fix(value):
>> return [
>> uuid.UUID(val)
>> for val in re.findall(r'[a-f0-9\-]+', value)
>> ]
>> return fix
>>
>> class UUID_ARRAY(TypeDecorator):
>> impl = UuidArrayFixer(UUID(as_uuid=True), dimensions=1)
>>
>> def bind_expression(self, bindvalue):
>> val = bindvalue.value
>> if val is None:
>> val = []
>> return array(
>> cast(literal(str(uuid_val)), UUID())
>> for uuid_val in val
>> )
>>
>>
>> On Jan 2, 2013, at 12:55 PM, Michael van Tellingen wrote:
>>
>>> Thanks for the detailed response! Inserting the values seems to work
>>> fine now. Only retrieving them again doesn't work.
>>> I've updated the gist at https://gist.github.com/4433940
>>>
>>> The problem seems that psycopg2 now returns the array as a string
>>> ({uuid1, uuid2}) and sqlalchemy iterates over the string instead of
>>> the containing elements.
>>> Bug in sqlalchemy or am i'm doing something stupid? :-)
>>>
>>> Thanks again!
>>>
>>>
>>> On Wed, Jan 2, 2013 at 5:23 PM, Michael Bayer <[email protected]>
>>> wrote:
>>>> OK well there's a lot of unfortunate turns on this one, at the core is that
>>>> psycopg2 by default doesn't know how to accept a list of UUIDs. The
>>>> postgresql.UUID() type in SQLAlchemy takes the easy approach and just
>>>> converts the value to a string, provided you pass the as_uuid=True flag to
>>>> it. That works for scalar UUID columns in Postgresql, however if we're
>>>> dealing with an array, then more casting is required as the data is passed
>>>> to the DB. Creating type processors on the psycopg2 side would be one
>>>> way to do this, though we can also do it on the SQLAlchemy side.
>>>>
>>>> Compounding issues here is that the array() construct in 0.8 has a tiny
>>>> bug,
>>>> that's the "self_group()" error you got, but if we make that work, we still
>>>> have to cast each individual uuid() value inside the array which is the
>>>> part
>>>> PG isn't doing here.
>>>>
>>>> If we fix the ARRAY.self_group() method, this approach works:
>>>>
>>>> def cast_uuid(value):
>>>> return cast(literal(uuid.uuid4(), UUID(as_uuid=True)), UUID)
>>>>
>>>> stmt = table.insert().values(
>>>> timestamp=datetime.datetime.utcnow(),
>>>> guids=array([cast_uuid(uuid.uuid4()), cast_uuid(uuid.uuid4())])
>>>> )
>>>>
>>>>
>>>> you get a statement like this:
>>>>
>>>> INSERT INTO example (timestamp, guids) VALUES (%(timestamp)s,
>>>> ARRAY[CAST(%(param_1)s AS UUID), CAST(%(param_2)s AS UUID)])
>>>> 2013-01-02 11:07:47,850 INFO sqlalchemy.engine.base.Engine {'timestamp':
>>>> datetime.datetime(2013, 1, 2, 16, 7, 47, 849197), 'param_1':
>>>> 'cdeec87a-4915-4bc7-a688-8557cc760fe4', 'param_2':
>>>> '7215808a-64ff-43fa-98af-1d3fc07e75d8'}
>>>>
>>>> But there's an even better approach here which is to use SQL bind
>>>> processing, which also doesn't trip the array() bug at the moment and is
>>>> more transparent:
>>>>
>>>> class UUID_ARRAY(TypeDecorator):
>>>> impl = ARRAY(UUID(as_uuid=True), dimensions=1)
>>>>
>>>> def bind_expression(self, bindvalue):
>>>> val = bindvalue.value
>>>> if val is None:
>>>> val = []
>>>> return array(
>>>> cast(literal(str(uuid_val)), UUID())
>>>> for uuid_val in val
>>>> )
>>>>
>>>> table = Table('example', metadata,
>>>> Column('timestamp', DateTime(timezone=False), primary_key=True),
>>>> Column('guids', UUID_ARRAY())
>>>> )
>>>>
>>>> stmt = table.insert().values(
>>>> timestamp=datetime.datetime.utcnow(),
>>>> guids=[uuid.uuid4(), uuid.uuid4()])
>>>> engine.execute(stmt)
>>>>
>>>>
>>>> the SQL output is the same.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> On Jan 2, 2013, at 8:02 AM, Michael van Tellingen wrote:
>>>>
>>>> Hi all,
>>>>
>>>> I'm experimenting a bit with postgresql arrays of uuid's.
>>>> Unfortunately I'm running into a bug or I'm not really understanding
>>>> it :-)
>>>>
>>>> My schema definition is as follow:
>>>>
>>>> table = Table('example', metadata,
>>>> Column('timestamp', DateTime(timezone=False), primary_key=True),
>>>> Column('guids', ARRAY(GUID, dimensions=1)
>>>> )
>>>>
>>>> Where GUID is taken from
>>>> http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#backend-agnostic-guid-type
>>>>
>>>> Then I try to insert a row with the following code:
>>>>
>>>> stmt = table.insert().values(
>>>> timestamp=datetime.datetime.utcnow(),
>>>> guids=[uuid.uuid4()]
>>>> )
>>>> engine.execute(stmt)
>>>>
>>>> This results in an ProgrammingError (column "guids" is of type uuid[]
>>>> but expression is of type text[] at character 97).
>>>> When using guids=array([uuid.uuid4()], type_=GUID) i receive the
>>>> following exception:
>>>> TypeError: self_group() takes exactly 2 arguments (1 given)
>>>>
>>>> The complete runnable code is located at https://gist.github.com/4433940
>>>>
>>>> Am I missing something or do I need to register a custom type via psycopg2?
>>>>
>>>> Thanks,
>>>> Michael
>>>>
>>>> --
>>>> You received this message because you are subscribed to the Google Groups
>>>> "sqlalchemy" group.
>>>> To view this discussion on the web visit
>>>> https://groups.google.com/d/msg/sqlalchemy/-/23G5PmB2sYcJ.
>>>> To post to this group, send email to [email protected].
>>>> To unsubscribe from this group, send email to
>>>> [email protected].
>>>> For more options, visit this group at
>>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>>
>>>>
>>>> --
>>>> You received this message because you are subscribed to the Google Groups
>>>> "sqlalchemy" group.
>>>> To post to this group, send email to [email protected].
>>>> To unsubscribe from this group, send email to
>>>> [email protected].
>>>> For more options, visit this group at
>>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To post to this group, send email to [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> For more options, visit this group at
>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To post to this group, send email to [email protected].
>> To unsubscribe from this group, send email to
>> [email protected].
>> For more options, visit this group at
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
