Issue

This Content is from Stack Overflow. Question asked by Ningshan Li

I am working on a MySQL database. I first create a table by peewee, then insert all records, and the final step is creating indexes for columns. Based on this question, I know I can use peewee migrate to create index after the table has been created.

For example, let’s say I have a table as below

import peewee as pw
import playhouse.migrate

db = pw.MySQLDatabase(...)
migrator = playhouse.migrate.MySQLMigrator(db)

class User(pw.Model):
    username = pw.CharField()
    userval = pw.FloatField()
    class Meta:
        database = db

db.create_tables([User])
# insert all records
...

# add index
with db.atomic():
    playhouse.migrate.migrate(
        migrator.add_index('User', ('userval',)),
    )

I can successfully create the index for this single column “userval”, but my question is how to create a descending index for this column? Since the add_index only accepts strings, I can’t use User.userval.desc(), then how to pass the keyword DESC to the migrator?

Or there is actually no way to create the descending index via peewee, and I have to login into MySQL, and then create the index by

CREATE INDEX userval_index ON User (userval DESC)

Solution

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.

This Question was asked in StackOverflow by kyrenia and Answered by doremi It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.