OK well this stage to create an Index is just not deferred enough, and text()
is not supported. Declarative has to make a "name" column that is part of
MyModel by copying it because it's coming from a mixin and that just hasn't
happened yet, the Column is not the right object yet. The Table isn't there.
Index wasn't designed with this case in mind, it doesn't support string names
the way UniqueConstraint does because it was intended to be constructed given
Table bound columns after the Table is fully assembled. This is from the
history of Table/Index/etc. to look just like DDL, the Index is created
separately.
easy enough to break out of helper methods and just use events,
mapper_configured requires that mappers are configured, else use
instrument_class and use class_.__table__.c.name instead:
class MyMixin(object):
name = Column('name', String(64), nullable=False)
@event.listens_for(MyMixin, "mapper_configured", propagate=True)
def add_index(mapper, class_):
Index('mymodel_lower_name_idx', func.lower(class_.name),
postgresql_ops={'name': 'text_pattern_ops'})
class MyModel(MyMixin, Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
configure_mappers()
https://bitbucket.org/zzzeek/sqlalchemy/issue/3028/support-text-in-index-thats-already-table
is added to support text()
On Apr 18, 2014, at 12:24 AM, Joshua Ma <[email protected]> wrote:
> Hi Mike,
>
> In hindsight I might have responded prematurely - got around to trying it and
> with text() I get the following:
>
> __table_args__ = (
> ...
> Index('folder_lower_name_idx', text('lower(name)'),
> postgresql_ops={'name': 'text_pattern_ops'}),
> )
>
> File
> "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/sql/schema.py",
> line 2798, in __init__
> ColumnCollectionMixin.__init__(self, *columns)
> File
> "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/sql/schema.py",
> line 2231, in __init__
> for c in columns]
> File
> "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/sql/schema.py",
> line 2223, in _to_schema_column_or_string
> raise exc.ArgumentError(msg % element)
> ArgumentError: Element <sqlalchemy.sql.elements.TextClause object at
> 0x108e2b910> is not a string name or column element
>
> With declared_attr:
>
> @declared_attr
> def __table_args__(cls):
> return (
> ...
> Index('folder_lower_name_idx', func.lower(cls.name),
> postgresql_ops={'name': 'text_pattern_ops'}),
> )
>
> File
> "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/flask_sqlalchemy.py",
> line 477, in __init__
> DeclarativeMeta.__init__(self, name, bases, d)
> File
> "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/ext/declarative/api.py",
> line 53, in __init__
> _as_declarative(cls, classname, cls.__dict__)
> File
> "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/ext/declarative/base.py",
> line 251, in _as_declarative
> **table_kw)
> File
> "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/sql/schema.py",
> line 352, in __new__
> table._init(name, metadata, *args, **kw)
> File
> "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/sql/schema.py",
> line 429, in _init
> self._init_items(*args)
> File
> "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/sql/schema.py",
> line 72, in _init_items
> item._set_parent_with_dispatch(self)
> File
> "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/sql/base.py",
> line 421, in _set_parent_with_dispatch
> self._set_parent(parent)
> File
> "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/sql/schema.py",
> line 2803, in _set_parent
> ColumnCollectionMixin._set_parent(self, table)
> File
> "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/sql/schema.py",
> line 2241, in _set_parent
> self.columns.add(col)
> File
> "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/sql/base.py",
> line 490, in add
> self[column.key] = column
> File
> "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/sql/base.py",
> line 499, in __setitem__
> if key in self:
> File
> "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/sql/base.py",
> line 554, in __contains__
> raise exc.ArgumentError("__contains__ requires a string argument")
> ArgumentError: __contains__ requires a string argument
>
> Is the functional index supported even if I'm using postgresql_ops? I've
> re-installed and upgraded to SQLAlchemy 0.9.4, same errors.
>
> - Josh
>
>
> On Thu, Apr 17, 2014 at 4:57 PM, Joshua Ma <[email protected]> wrote:
> Awesome, thanks so much for the quick response.
>
>
> On Sun, Apr 13, 2014 at 7:00 PM, Michael Bayer <[email protected]>
> wrote:
> you need to turn your __table_args__ into a callable:
>
> @declared_attr
> def __table_args__(cls):
> return (Index(..., func.lower(cls.name), ...), )
>
> or just use a string for your functional index: Index(...,
> text("LOWER(name)"), ...)
>
>
> On Apr 13, 2014, at 9:22 PM, Joshua Ma <[email protected]> wrote:
>
>> Is there a way to create a functional index in a declarative model without
>> referencing the actual column? I currently have something like
>>
>> class MyModel(db.Base):
>> name = db.Column('name', db.String(255))
>> __table_args__ = (
>> Index('mymodel_lower_name_idx', func.lower(name),
>> postgresql_ops={'name': 'text_pattern_ops'}),
>> )
>>
>> which is slightly inconvenient because I usually declare all my columns
>> under __table_args__.
>>
>> More importantly, though, I use a mixin for some models, with the
>> __table_args__ in the model and the column in the mixin:
>>
>> class MyMixin(object):
>> name = db.Column(db.String(64), nullable=False)
>>
>> class MyModel(MyMixin, db.Base):
>> __table_args__ = (
>> Index('mymodel_lower_name_idx', func.lower(MyMixin.name),
>> postgresql_ops={'name': 'text_pattern_ops'}),
>> )
>>
>> but this doesn't seem to work, I get "ArgumentError: __contains__ requires a
>> string argument" at runtime.
>>
>> The rest of my indices are just strings (e.g. Index('mymodel_name_idx',
>> 'name')) - is there a similar way to create functional ones? I tried
>> 'lower(name)' but it doesn't like that either since it's not a valid column
>> (KeyError: 'lower(name)').
>>
>> Thanks!
>> Josh
>>
>> --
>> 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 [email protected].
>>
>> To post to this group, send email to [email protected].
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> You received this message because you are subscribed to a topic in the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/CgSJUlelhGs/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
