Length Limit for MySQL Identifiers (table names, column names) using SQLAlchemy with Elixir

Question

I am using SQLAlchemy with Elixir and everything works fine using SQLite. However, when I switch to MySQL there is a table name and column name length limit (64 characters). I have quite a lot of entities with several ManyToMany relationships and I did not find a way to clamp the resulting long table names like MyFirstEntity_rel_otherEntities__MySecondEntity_rel_theFirstEntities in an automated way.

Foreign key column names for ManyToOne relationships are too long depending on the class and relation name. create_engine("...", label_length = 64) did not do the trick and I already use shortcolumns = True to avoid having the entire Python module name as prefix as well.

The only way I see is to go through the whole schema and define tablename (for ManyToMany relationships) and columnname (for ManyToOne relationships) manually. Are there any other other options?

I solved this by using optional parameters colname (ManyToOne) and tablename (ManyToMany) for all relations which exceeded MySQL's 64-character limit. However, I wonder if there is an easier, more automatic solution. — Daniel, Sep 19 '12 at 13:23

score 0 · Answer 1 · answered Mar 04 '13 at 01:09

Yes, there is a more automatic solution: update the default elixir options with a tablename re-definition:

elixir.options_defaults.update(
    dict(
        tablename = lambda cls: cls.__name__.lower()
    )
)

This will cause tables to be referred to by their short names (ie, model name only).

Length Limit for MySQL Identifiers (table names, column names) using SQLAlchemy with Elixir

1 Answers1