sqla-guid-array.py

import datetime
import uuid

import psycopg2.extras
from sqlalchemy import Column, MetaData, Table
from sqlalchemy.types import Integer, DateTime, TypeDecorator
from sqlalchemy.dialects.postgresql import ARRAY, array
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.sql.expression import cast, literal, select

psycopg2.extras.register_uuid()

metadata = MetaData()


class UUID_ARRAY(TypeDecorator):
    impl = ARRAY(UUID, 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_2', metadata,
    Column('timestamp', DateTime(timezone=False), primary_key=True),
    Column('num', Integer),
    Column('guids', UUID_ARRAY)
)


if __name__ == '__main__':
    from sqlalchemy import create_engine
    engine = create_engine('postgresql://localhost:5432/test')
    metadata.bind = engine
    metadata.create_all()

    stmt = table.insert().values(
        timestamp=datetime.datetime.utcnow(),
        num=2,
        guids=[uuid.uuid4(), uuid.uuid4()])
    engine.execute(stmt)

    stmt = select([table.c.guids])
    print engine.execute(stmt).fetchall()