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.
