"required" object in visit_insert() vs. bind_expression
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()
Comments (7)
-
reporter -
reporter the idea would be to make "required" a fixed symbol in types, and get TypeEngine to bypass any methods when this symbol is used.
-
reporter gack! sorry had the PG adapter in there..
-
reporter 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).
-
reporter - changed status to resolved
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 .
-
reporter - changed component to sql
-
reporter - removed milestone
Removing milestone: 0.8.0final (automated comment)
- Log in to comment
Correction, this works----- does not work