CODEWITHSUNDEEP
pythonsqlalchemy
Lien Michiels
Lien Michiels

Reputation: 23

How can I set an index on a declared attribute using SQLAlchemy Declarative ORM?

When I try to define an index on a declared attribute

class Event(Base):

    @declared_attr
    def user_id(cls):
        return Column(BigInteger, ForeignKey("users.user_id"), nullable=False)

    idx_event_user_id = Index('idx_event_user_id', user_id)

I get the following error:

sqlalchemy.exc.ArgumentError: Element <sqlalchemy.ext.declarative.api.declared_attr object at 0x1066ec648> is not a string name or column element

Is there another way to do this? Can I set indices on a declared attribute?

Upvotes: 2

Views: 2988

Answers (1)

Ilja Everil&#228;
Ilja Everil&#228;

Reputation: 52997

When dealing with inheritance/mixins you should pass indexes that you would like to create for all classes and their underlying Table as "inline" definitions using __table_args__, which also should be a declared attribute in this case, as explained in "Creating Indexes with Mixins":

class Event(Base):

    @declared_attr
    def user_id(cls):
        return Column(BigInteger, ForeignKey("users.user_id"), nullable=False)

    @declared_attr
    def __table_args__(cls):
        # Return a tuple of arguments to pass to Table
        return (Index(f'idx_{cls.__tablename__}_user_id', 'user_id'),)

This will avoid name conflicts between indexes created for different (sub)classes. Note that here this "inline" form uses string names to identify columns to index, but cls.foo_id will work as well when in a declared attribute. In general there's no need to assign an Index as a model attribute, and in some situations it may even lead to confusion.

The simple solution to indexing a column is to just pass index=True to Column. This is a shortcut for creating an anonymous index for the column in question:

class Event(Base):

    @declared_attr
    def user_id(cls):
        return Column(BigInteger, ForeignKey("users.user_id"), nullable=False, index=True)

When you are not dealing with inheritance/mixins, you do not need the @declared_attr wrapping:

class MyModel(Base):
    foo = Column(Integer)
    bar = Column(Integer)

    # "Inline", but gets the actual column instead of a string name.
    # Possible with Declarative.
    __table_args__ = (Index('idx_mymodel_foo', foo),)

# Finds the table through the Column used in the definition.
Index('idx_mymodel_bar', MyModel.bar)

The reason why you are getting the error is that during class construction the class definition's body is evaluated and the resulting namespace is then used as the class' namespace. During that evaluation

idx_event_user_id = Index('idx_event_user_id', user_id)

results in Index receiving the declared attribute descriptor object assigned to user_id in that namespace as is, and so SQLAlchemy complains.

When you access descriptors through a constructed class object, or an instance of it, they get to do their thing, which in case of a declared attribute means that it evaluates to the mapped property or special declarative member it represents.

Upvotes: 1

Related Questions