Functional and declarative Index

[Joshua Ma]

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

[Michael Bayer]

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)”), …)

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Joshua Ma]

Awesome, thanks so much for the quick response.

--
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 sqlalchemy+...@googlegroups.com.

[Joshua Ma]

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

[Michael Bayer]

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()

[Joshua Ma]

I tried with the sample code, and I get the following:

  File "/Users/joshma/aurelia/benchling/models/folder.py", line 273, in <module>

    configure_mappers()

  File "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 2560, in configure_mappers

    mapper._post_configure_properties()

  File "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1673, in _post_configure_properties

    prop.init()

  File "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py", line 143, in init

    self.do_init()

  File "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1509, in do_init

    self._process_dependent_arguments()

  File "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1566, in _process_dependent_arguments

    self.target = self.mapper.mapped_table

  File "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 712, in __get__

    obj.__dict__[self.__name__] = result = self.fget(obj)

  File "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1489, in mapper

    configure=False)

  File "/Users/joshma/.envs/aurelia/lib/python2.7/site-packages/sqlalchemy/orm/base.py", line 383, in class_mapper

    raise exc.UnmappedClassError(class_)

UnmappedClassError: Class 'sqlalchemy.sql.schema.Sequence' is not mapped

I tried this with both mapper_configured and instrument_class. I'm using postgres and primary_key, so I think the Sequence it's using is getting in the way?

I understand it's not easy to debug a SQLA setup from afar; thanks in advance for the responses.

- Josh

[Michael Bayer]

full script is attached.  Works in 0.9, 0.8.

[Joshua Ma]

Ah, it turned out to be where configure_mappers() was being called. I moved it out of the module and into our create_app() and it worked (we're using Flask - do you see any issues with putting it at a non-module level?).

Thanks a bunch.

On Fri, Apr 18, 2014 at 7:38 AM, Michael Bayer <mik...@zzzcomputing.com> wrote:

full script is attached.  Works in 0.9, 0.8.

--
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 sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Michael Bayer]

the configure_mappers() is just to kick the mappings as created into their full form, which also triggers all the hooks like the one I’m using there.  So if your mappings don’t exist until create_app() is called, then you’d do configure_mappers() at that point.   It’s a super-safe function to call anywhere.

On Apr 18, 2014, at 10:45 PM, Joshua Ma <m...@josh.ma> wrote:

Ah, it turned out to be where configure_mappers() was being called. I moved it out of the module and into our create_app() and it worked (we're using Flask - do you see any issues with putting it at a non-module level?).

Thanks a bunch.

On Fri, Apr 18, 2014 at 7:38 AM, Michael Bayer <mik...@zzzcomputing.com> wrote:

full script is attached.  Works in 0.9, 0.8.

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.

To unsubscribe from this topic, visithttps://groups.google.com/d/topic/sqlalchemy/CgSJUlelhGs/unsubscribe.

To unsubscribe from this topic, visithttps://groups.google.com/d/topic/sqlalchemy/CgSJUlelhGs/unsubscribe.

To unsubscribe from this group and all its topics, send an email tosqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
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 tosqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
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, visithttps://groups.google.com/d/topic/sqlalchemy/CgSJUlelhGs/unsubscribe.

To unsubscribe from this group and all its topics, send an email tosqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.
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 tosqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
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 tosqlalchemy+...@googlegroups.com.