Setting an index limit in SQLAlchemy

Question

I would like to set up a maximum limit for an index within a Column definition or just through the Index constructor but I don't seem to find a way to achieve it.

Basically, I would like to simulate this MySQL behaviour:

CREATE TABLE some_table (
  id int(11) NOT NULL AUTO_INCREMENT,
  some_text varchar(2048) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY some_text (some_text(1024)), # <- this line
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

In SQLAlchemy I would have something like:

class SomeTable(BaseModel):
  __tablename__ = 'some_table'
  __seqname__ = 'some_table_id_seq'

  id = sa.Column(sa.Integer(11), sa.Sequence(__seqname__), primary_key=True)
  some_text = sa.Column(sa.String(2048), index=True) # <- this line

but I can't find anything that would suggest the limit of the index can be customised. Something like:

some_text = sa.Column(sa.String(2048), index=True, index_length=1024)

I guess since this option for the Column constructor is just an alias for the Index constructor, is there a custom param to include in the Index constructor to allow this setting?

Thanks!

Answer 1

I think you can do something like:

class SomeTable(BaseModel):
  __tablename__ = 'some_table'
  __seqname__ = 'some_table_id_seq'
  __table_args__ = (
      sa.Index("idx_some_text", "some_text", mysql_length=1024),
  )
  id = sa.Column(sa.Integer(11), sa.Sequence(__seqname__), primary_key=True)
  some_text = sa.Column(sa.String(2048))

Reference: http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#index-length