On Aug 13, 2012, at 3:25 PM, Vlad K. wrote:
> On 08/13/2012 08:53 PM, Michael Bayer wrote:
>> we don't currently have native support for ARRAY operations and there is
>> some infrastructure work that should make it easier in 0.8 to add.
>>
>> for now, when you need custom operators use op():
>>
>> Model.array_column.op("@>")("somevalue")
>
> I tried that, but that doesn't work because the second operand has to be
> wrapped in ARRAY[], afaik that's the only way to lookup a value in an array
> column (which is also gist indexable).
>
>
> If I do
>
> Model.array_column.op("@>")('ARRAY[123]')
>
> I get
>
> SELECT * FROM model_table WHERE array_column @> 'ARRAY[123]'
>
>
> and I need ARRAY[123] without quotes.
we can keep turning the crank here, here's a full series of examples to make
this happen:
# step 1. build a custom element to do the "ARRAY[xyz]" part of this.
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import ColumnElement
from sqlalchemy.sql.expression import _literal_as_binds
from sqlalchemy.dialects.postgresql import ARRAY
class MyArray(ColumnElement):
type = ARRAY
def __init__(self, expr):
self.expr = _literal_as_binds(expr)
@compiles(MyArray)
def compile(element, compiler, **kw):
return "ARRAY[%s]" % compiler.process(element.expr)
# step 2. illustrate raw usage of MyArray with the ORM.
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
Base = declarative_base()
class MyClass(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
arrvalue = Column(ARRAY(String))
print MyClass.arrvalue.op("@>")(MyArray("123"))
# step 3. use ColumnProperty to define a "comparator" at the
# ORM level.
from sqlalchemy.orm.properties import ColumnProperty
class MyComparator(ColumnProperty.Comparator):
def array_in(self, other):
return self.__clause_element__().op("@>")(MyArray(other))
class MyOtherClass(Base):
__tablename__ = 'bar'
id = Column(Integer, primary_key=True)
arrvalue = ColumnProperty(Column(ARRAY(String)),
comparator_factory=MyComparator)
print MyOtherClass.arrvalue.array_in("123")
# step 4. 0.8 will have a "core" method for the "comparison" part which is
easier.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
