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 <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. > -- 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.