eberbis eberbis - 2 months ago 8
MySQL Question

Setting an index limit in SQLAlchemy

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

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