CODEWITHSUNDEEP
indexingsqlalchemyalembic
mmway
mmway

Reputation: 173

SQLAlchemy create multi column index using naming_convention

I am trying to create multi (i.e. 2-column index) for a model based table. But I'd like not to give specific name for this index. I'd like that naming_convention and alembic revision --autogenerate would do it's job with naming index. So far I have code like this:

from sqlalchemy import MetaData
from sqlalchemy.ext.declarative import as_declarative
from sqlalchemy.schema import Index

metadata = MetaData(
        naming_convention={
            'pk': '%(table_name)s_pk',
            'ix': '%(table_name)s_%(column_0_N_name)s_ix',
        },
    )

@as_declarative(metadata=metadata)
class Base:
    pass

class Foo(Base):
    id = Column(Integer, primary_key=True)
    col1 = Column('Col1', Integer)
    col2 = Column('Col2', DateTime)

Index(
    metadata.naming_convention['ix'] % {
        'table_name': Foo.__tablename__,
        'column_0_N_name': Foo.col1.expression.name + "_" + Foo.col2.expression.name
    },
    Foo.col1,
    Foo.col2,
)

So I'd like to avoid the 'creating name' part of code:

    metadata.naming_convention['ix'] % {
        'table_name': Foo.__tablename__,
        'column_0_N_name': Foo.col1.expression.name + "_" + Foo.col2.expression.name
    }

Upvotes: 1

Views: 3619

Answers (1)

mmway
mmway

Reputation: 173

after more search there is very simple solution. According to github comment in SQLAlchemy issue if you would like to create index by Index() you silmply need to pass name=None argument, and fill arguments for columns.

so the code above should look like (part that stays the same):

from sqlalchemy import MetaData
from sqlalchemy.ext.declarative import as_declarative
from sqlalchemy.schema import Index

metadata = MetaData(
        naming_convention={
            'pk': '%(table_name)s_pk',
            'ix': '%(table_name)s_%(column_0_N_name)s_ix',
        },
    )

@as_declarative(metadata=metadata)
class Base:
    pass

Option 1 (declare multi column index out of table model)

class Foo(Base):
    id = Column(Integer, primary_key=True)
    col1 = Column('Col1', Integer)
    col2 = Column('Col2', DateTime)

Index(None, Foo.col1, Foo.col2)

Option 2 (declare multi column index inside of table model)

class Foo(Base):
    id = Column(Integer, primary_key=True)
    col1 = Column('Col1', Integer)
    col2 = Column('Col2', DateTime)
    __table_args__ = (
        Index(None, 'Col1', 'Col2'),
    )

Then index name (in both options) would be:

Foo_Col1_Col2_ix

I have not found this kind of solution in SQLALchemy documentation (maybe there is?), but it's good that there are some answers on github issues in SQLAlchemy github :)

Upvotes: 8

Related Questions