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 <mike...@zzzcomputing.com>
> 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 sqlalchemy@googlegroups.com.
>> To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> 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 sqlalchemy@googlegroups.com.
>> To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> 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 sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> 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 sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
