Retroactively create indexes with Peewee?

Question

Is there any way to retroactively index a column of a sql file with Peewee? (i.e. One where the initial specification was not indexed.) Asking because it should be quicker to do all the inserts, and then index, rather than the other way around.

Answer 1

Is there any way to retroactively index a column of a sql file with Peewee?

Yes. Pewee's SchemaMigrator class includes support for adding an index:

add_index(table, columns[, unique=False])

table (str) – Name of table on which to create the index.
columns (list) – List of columns which should be indexed.unique
unique (bool) – Whether the new index should specify a unique constraint.

If you are using Pewee with the pewee_migrate package, you can create a database migration for adding the index on a table that already exists like so:

unique_index = False

def migrate(migrator, database, fake=False, **kwargs):
    migrator.create_index('some_table', ('name_of_indexed_column',), unique_index)

def rollback(migrator, database, fake=False, **kwargs):
    migrator.drop_index('some_table', ('name_of_indexed_column',), unique_index)

And then run the migration.

Answer 2

I wanted to show a complete example of using add_index (referenced by @doremi in the other answer):

from peewee import SqliteDatabase
from playhouse.migrate import SqliteMigrator, migrate

db = SqliteDatabase('db.sqlite3')
migrator = SqliteMigrator(db)

migrate(
    # Create a unique, multi-column index
    migrator.add_index('table_name', ('col1', 'col2'), True),
)

Note that this is a unique multi-column index (I'm adding an index for unique values on both columns col1 and col2 in table_name). Adjust the code above so that it suits your needs!

Documentation is available under "Supported Operations". Scroll down a bit to find examples for "Adding an index".