"required" object in visit_insert() vs. bind_expression

[Mike Bayer]

need to work this case out:

import datetime
import uuid

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
from sqlalchemy.orm import *


metadata = MetaData()


class UUID_ARRAY(TypeDecorator):
    impl = ARRAY(UUID, dimensions=1)

    def bind_expression(self, bindvalue):
        if bindvalue.callable:
            val = bindvalue.callable()
        else:
            val = bindvalue.value
        if val is None:
            val = [       # nothing works here, the "required"
        # symbol screws it up
        elif not hasattr(val, '__iter__'):
            return literal(val)
        return array(
            cast(literal(str(uuid_val)), UUID())
            for uuid_val in val
        )


table = Table('example_2', metadata,
    Column('id', Integer, primary_key=True),
    Column('guids', UUID_ARRAY)
)

class Foo(object):
    pass

mapper(Foo, table)

if __name__ == '__main__':
    from sqlalchemy import create_engine
    engine = create_engine('postgresql://scott:tiger@localhost:5432/test', echo=True)
    metadata.bind = engine
    metadata.drop_all()
    metadata.create_all()

    f1 = Foo()
    f1.guids = [uuid.uuid4(), uuid.uuid4()](]
)
    s = Session(engine)
    s.add(f1)
    s.commit()

    print s.query(Foo).all()

[Mike Bayer]

Correction, this works----- does not work

class UUID_ARRAY(TypeDecorator):
    impl = ARRAY(UUID, dimensions=1)

    def bind_expression(self, bindvalue):
        if bindvalue.callable:
            val = bindvalue.callable()
        else:
            val = bindvalue.value
        if val is None:
            val = []
        elif not hasattr(val, '__iter__'):
            return bindvalue
        return array(
            cast(literal(str(uuid_val)), UUID())
            for uuid_val in val
        )

[Mike Bayer]

the idea would be to make "required" a fixed symbol in types, and get TypeEngine to bypass any methods when this symbol is used.

[Mike Bayer]

gack! sorry had the PG adapter in there..

[Mike Bayer]

from the list:

change again, that doesn't work.

Upon reflection, I think the case here is that there's no alternative but to make sure psycopg2 can properly format the contents of the ARRAY itself. This is because SQLAlchemy is producing a completed INSERT statement for preparation, without the parameters actually filled in:

INSERT INTO table (x) VALUES (%(x)s)

At that point, SQLAlchemy is done producing strings, and sends it off to psycopg2 along with an array value for "x". So there is no opportunity here for a user-defined bind expression generator to further modify the expression above. More fundamentally, the above statement can be called using DBAPI executemany(); each value for "x" can be an array of a different length. So it's totally up to psycopg2 here to handle this case, and it even suggests that prepared statements can't be used very effectively with Postgresql ARRAY types (psycopg2 doesn't use prepared statements AFAIK).

[Mike Bayer]

so not much we can really do here, the nature of a bind param is that you really can't assume anything about the value since the value might not be there yet. Made the "REQUIRED" symbol into a documented util.symbol so it's more identifiable in ac1ee45fcc749de4d75283f0b343be4b4c6c31ff .

[Mike Bayer]

Removing milestone: 0.8.0final (automated comment)