On Dec 10, 2013, at 3:29 AM, Matthew Pounsett <matt.pouns...@gmail.com> wrote:
>
>
> On Tuesday, 10 December 2013 01:00:51 UTC-5, Michael Bayer wrote:
> that’s a little strange but you can get around it using CAST:
>
> match = session.query(MyTable).\
> filter(MyTable.myset == cast(z, ARRAY(String))).\
> all()
>
> Unfortunately, that doesn't work.
>
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) cannot cast type record
> to character varying[]
> LINE 3: WHERE mytable.myset = CAST((E'a', E'b', E'c') AS VARCHAR[])
the test case I’m using is below. You might want to make sure you’re on the
latest psycopg2, this is also SQLAlchemy 0.8.4 but the SQL output seems the
same. Overall, if maybe you’re on an older postgresql version, you need to
log in with psql, figure out what SQL query works directly, then just render
that with whatever casts are needed.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import ARRAY, array
Base = declarative_base()
class MyTable(Base):
__tablename__ = 'mytable'
id = Column(Integer, primary_key=True)
myset = Column(ARRAY(String))
engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
session = Session(engine)
z = ['a', 'b', 'c']
match = session.query(MyTable).\
filter(MyTable.myset == cast(z, ARRAY(String))).\
all()
> ^
> 'SELECT mytable.id AS mytable_id, mytable.myset AS mytable_myset \nFROM
> mytable \nWHERE mytable.myset = CAST(%(param_1)s AS VARCHAR[])' {'param_1':
> ('a', 'b', 'c')}
>
> of course if you declare you column like this:
>
> myset = Column(ARRAY(Text))
>
> That doesn't change the original error.. it still complains:
>
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator does not exist:
> character varying[] = text[]
> LINE 3: WHERE mytable.myset = ARRAY[E'a', E'b', E'c']
> ^
> HINT: No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
> 'SELECT mytable.id AS mytable_id, mytable.myset AS mytable_myset \nFROM
> mytable \nWHERE mytable.myset = %(myset_1)s' {'myset_1': ['a', 'b', 'c']}
>
> So, I'm still looking for a solution..
> Thanks for the suggestions.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
