SQLAlchemy Partial Index with composite keys having the same name

Question

I am using Python 2.7 and SQLAlchemy 0.9.8 and trying to use partial indexes.

I have two tables (just the relevant part)

class DesignerRange
    __tablename__ = 'designer_ranges'  
    shortcode = Column(Unicode(12))  

class Design
    __tablename__ = 'designs'  
    shortcode = Column(Unicode(12))
    designer_range_id = Column(
        Integer,
        ForeignKey('designer_ranges.id'),
        nullable=False,
     )
    designer_range = relationship(
        DesignerRange,
        backref=backref(
        'designs',
        single_parent=True,
    ),
    lazy='joined',
)

And I want to create a partial Index that when the shortcode of the Design is not null (it exists) it should be unique within the DesignerRange.

I am trying something like this

 @declarative.declared_attr
 def __table_args__(cls):
     return ( 
            Index('design_shortcode',
                table('designer_ranges', column('shortcode')).c.shortcode,
                cls.shortcode, unique=True,
                postgresql_where=(cls.shortcode!=None)),
            )

This is the resulting Alembic migration

op.create_index('design_shortcode', 'designs', ['shortcode', 'shortcode'], unique=True, postgresql_where=sa.text('designs.shortcode IS NOT NULL'))

But I am getting this warning

sqlalchemy/sql/base.py:508: SAWarning: Column 'shortcode' on table <sqlalchemy.sql.selectable.TableClause at 0x7f50a1768550; designer_ranges> being replaced by Column('shortcode', Unicode(length=12), table=<designs>), which has the same key.  Consider use_labels for select() statements.

I've no idea where to apply use_labes in this case, it seems to exist only within a select clause. Thanks

score 0 · Answer 1 · answered Nov 17 '15 at 15:16

I've solved my problem by using

@declarative.declared_attr
def __table_args__(cls):
    return (
    Index('design_shortcode_idx',
            cls.designer_range_id,
            cls.shortcode, unique=True,
            postgresql_where=(cls.shortcode!=None)),
)

So, instead of trying to get the shortcode from the foreign key table I am just getting its unique id.

SQLAlchemy Partial Index with composite keys having the same name

1 Answers1