How can I get an SQLAlchemy Index object corresponding to a column that has index=True?
For example, in the code below, I'd like to have the Index associated to MyTable.name.
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase):
pass
class MyTable(Base):
__tablename__ = "my_table"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(index=True)
The end purpose is to call Index.drop(), insert a lot of rows at once, and Index.create() afterwards, for performance reasons.
I could do session.execute(text(drop index ...)) but I don't know what the name of the index is. If there's a way to get the index name without generating the Index object, that'd be fine too.
(I'm using SQLAlchemy 2.0)
After some additional research here's a solution for future reference. A bit convoluted but it works.
The main point is that you can use MyTable.__table__.indexes to get a set of all indexes available on MyTable. Then it's just a matter of finding which one is mapped to MyTable.name.
def get_name_index():
"""Return Index object for MyTable.name."""
def get_name_column():
"""Return imperative Column object for MyTable.name."""
for column in MyTable.__table__.c:
if column.name == MyTable.name.name:
return column
name_column = get_name_column()
for index in MyTable.__table__.indexes:
if index.c == [name_column]:
return index
(If there's a way to replace get_name_column() by something simpler, please let me know!)