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.
