I have multiple SQLite3 databases for which the models are not available.

def index_db(name, tempdb):print(f'{name.ljust(padding)} Indexing file: {tempdb}')if tempdb.endswith('primary.sqlite'):conn = sqlite3.connect(tempdb)conn.execute('CREATE INDEX packageSource ON packages (rpm_sourcerpm)')conn.commit()conn.close()

How can I perform the same operation using SQLAlchemy?

I can come up with two ways to add that index through SQLAlchemy:

• if you do not reflect, execute the SQL statement directly
• if you reflect you table/model, add an index to it

Firstly, let's create the table to work on.

import sqlite3con = sqlite3.connect("/tmp/73526761.db")
con.execute("CREATE TABLE t73526761 (id INT PRIMARY KEY, name VARCHAR)")
con.commit()
con.close()

Then, without reflecting, you can execute your raw SQL with the following.

import sqlalchemy as saengine = sa.create_engine("sqlite:////tmp/73526761.db", future=True)with engine.begin() as con:con.execute(sa.text("CREATE INDEX t73526761_name_idx ON t73526761 (name)"))con.commit()

Or if you reflect the table only (SQLAlchemy core):

import sqlalchemy as sametadata_obj = sa.MetaData()engine = sa.create_engine("sqlite:////tmp/73526761.db", future=True)t73526761 = sa.Table("t73526761", metadata_obj, autoload_with=engine)t73526761_name_idx = sa.Index("t73526761_name_idx", t73526761.c.name)t73526761_name_idx.create(bind=engine) # emits CREATE INDEX t73526761_name_idx ON t73526761 (name)

Or if you reflect the model (SQLAlchemy orm):

import sqlalchemy as sa
from sqlalchemy import ormBase = orm.declarative_base()engine = sa.create_engine("sqlite:////tmp/73526761.db", future=True)class K73526761(Base):__table__ = sa.Table("t73526761", Base.metadata, autoload_with=engine)t73526761_name_idx = sa.Index("t73526761_name_idx", K73526761.name)t73526761_name_idx.create(bind=engine) # emits CREATE INDEX t73526761_name_idx ON t73526761 (name)