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 <mike...@zzzcomputing.com> 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 <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. > -- 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.
