How to properly declare a postgresql partial index?

[Russ]

What is the proper way to declare a postgresql partial index when using the @declared_attr decorator?

This form gives me "Cannot compile Column object until its 'name' is assigned":

    track_type       = Column(SmallInteger, nullable = False)

    @declared_attr

    def __table_args__(cls):

        return (Index("idx_track_type", "track_type",

                      postgresql_where = (cls.track_type != 0)),

                )

and this form gives me "AttributeError: 'str' object has no attribute '_compiler_dispatch'":

    track_type       = Column(SmallInteger, nullable = False)

    @declared_attr

    def __table_args__(cls):

        return (Index("idx_track_type", "track_type",

                      postgresql_where = "track_type != 0"),

                )

From [this post][1] I learned about the use of sqlalchemy.sql.text, so this is now working for me:

    from sqlalchemy.sql import text as sql_text

    # <snip>

    @declared_attr

    def __table_args__(cls):

        return (Index("idx_track_type", "track_type",

                      postgresql_where = sql_text("track_type != 0")),

                )

That post also indicated there may be a bug here, but that was almost 2 years ago.  Is there a better way to do it now?  More importantly, will the working code above continue to work in the future?

[1]: http://goo.gl/Fmgynh

[Michael Bayer]

Russ <russand...@gmail.com> wrote:

> What is the proper way to declare a postgresql partial index when using the @declared_attr decorator?

these two concepts aren’t really connected

>
> This form gives me "Cannot compile Column object until its 'name' is assigned":
>
> track_type = Column(SmallInteger, nullable = False)
> @declared_attr
> def __table_args__(cls):
> return (Index("idx_track_type", "track_type",
> postgresql_where = (cls.track_type != 0)),
> )
>

> and this form gives me "AttributeError: 'str' object has no attribute '_compiler_dispatch’":

please give me stack traces. or at least versions. works for me. Here’s 0.9:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr

Base = declarative_base()

class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)

track_type = Column(SmallInteger, nullable = False)

@declared_attr
def __table_args__(cls):
return (Index("idx_track_type", "track_type",
postgresql_where = (cls.track_type != 0)),
)

e = create_engine("postgresql://scott:tiger@localhost/test", echo =True)

Base.metadata.drop_all(e)
Base.metadata.create_all(e)


CREATE TABLE a (
id SERIAL NOT NULL,
track_type SMALLINT NOT NULL,
PRIMARY KEY (id)
)


2015-02-02 17:56:12,610 INFO sqlalchemy.engine.base.Engine {}
2015-02-02 17:56:12,612 INFO sqlalchemy.engine.base.Engine COMMIT
2015-02-02 17:56:12,613 INFO sqlalchemy.engine.base.Engine CREATE INDEX idx_track_type ON a (track_type) WHERE track_type != 0
2015-02-02 17:56:12,613 INFO sqlalchemy.engine.base.Engine {}
2015-02-02 17:56:12,614 INFO sqlalchemy.engine.base.Engine COMMIT

>
> track_type = Column(SmallInteger, nullable = False)
> @declared_attr
> def __table_args__(cls):
> return (Index("idx_track_type", "track_type",
> postgresql_where = "track_type != 0"),
> )
>
> From [this post][1] I learned about the use of sqlalchemy.sql.text, so this is now working for me:
>
> from sqlalchemy.sql import text as sql_text
> # <snip>
> @declared_attr
> def __table_args__(cls):
> return (Index("idx_track_type", "track_type",
> postgresql_where = sql_text("track_type != 0")),
> )
>
> That post also indicated there may be a bug here, but that was almost 2 years ago. Is there a better way to do it now? More importantly, will the working code above continue to work in the future?
>
> [1]: http://goo.gl/Fmgynh
>

> --
> 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.

[Russ]

> What is the proper way to declare a postgresql partial index when using the @declared_attr decorator?

these two concepts aren’t really connected

Sorry --  I described that poorly, then.  However, I only see the problem (in v0.9.8) when I am using @declared_attr as in the case of a mixin.

Your test script works for me, but not when I tweak it to have mixin behaviour.  Try this version:

from sqlalchemy import * 

from sqlalchemy.orm import * 

from sqlalchemy.ext.declarative import declarative_base, declared_attr 

Base = declarative_base() 

class A_TableDef(object): 

    __tablename__ = 'a' 

    id = Column(Integer, primary_key=True) 

    track_type       = Column(SmallInteger, nullable = False) 

    @declared_attr 

    def __table_args__(cls): 

        return (Index("idx_track_type", "track_type", 

                      postgresql_where = (cls.track_type != 0)), 

                ) 

class A_Model(Base, A_TableDef):

    pass

[Russ]

I should have also indicated that the addition of sqlalchemy.sql.text fixes the small mixin example.  The little script below works, but I don't know if it is a sketchy hack, or a safe long term solution:

from sqlalchemy import * 

from sqlalchemy.orm import * 

from sqlalchemy.ext.declarative import declarative_base, declared_attr 

from sqlalchemy.sql import text as sql_text

Base = declarative_base() 

class A_TableDef(object): 

    __tablename__ = 'a'

    

    id = Column(Integer, primary_key=True)

    track_type       = Column(SmallInteger, nullable = False)

    @declared_attr

    def __table_args__(cls):

        return (Index("idx_track_type2", "track_type",

                      postgresql_where = sql_text("track_type != 0")),

                )

class A_Model(Base, A_TableDef):

    pass

e = create_engine("postgresql://postgres:postgrespw@localhost:5433/edms", echo =True)

Base.metadata.drop_all(e)

Base.metadata.create_all(e)

[Michael Bayer]

with mixins, this will work as is in latest master, see
http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#improvements-to-declarative-mixins-declared-attr-and-related-features.

In 0.9, the declared_attr here is called sooner than we’d like, though this
particular example works if we just give the column a name (more complex
things will still not work very well with the mixins here though):

class A_TableDef(object):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)

track_type = Column('track_type', SmallInteger, nullable = False)

@declared_attr
def __table_args__(cls):
return (Index("idx_track_type", "track_type",
postgresql_where=(cls.track_type != 0)),
)

The version with text() is perfectly fine as postgresql_where isn’t significant anywhere except in the DDL.

[Russ]

Thanks.  The name addition seems tidier to me so I switched to that for the moment.

On a somewhat unrelated note, I love the "what's new in 1.0" docs you linked.  I had not checked them out yet.  The "Performance" section is particularly awesome and well written.  Aspects of it bring me back to putting together my profiling talk [1] from a while ago (optimizing SQLAlchemy inserts was a perfect vehicle for the talk).  I'll have to update that thing now with the fancy new bulk operations... they look quite convenient for decent gain with little pain. Nice!

Russ

[1]: https://speakerdeck.com/rwarren/a-brief-intro-to-profiling-in-python