Can pandas with MySQL support text indexes?

Question

If I try to store a dataframe with a text index in a MySQL database I get the error "BLOB/TEXT column used in key specification without a key length", for example:

import pandas as pd
import sqlalchemy as sa
df = pd.DataFrame(
    {'Id': ['AJP2008H', 'BFA2010Z'], 
     'Date': pd.to_datetime(['2010-05-05', '2010-07-05']), 
     'Value': [74.2, 52.3]})
df.set_index(['Id', 'Date'], inplace=True)
engine = sa.create_engine(db_connection)
conn = engine.connect()
df.to_sql('test_table_index', conn, if_exists='replace')
conn.close()

Will generate the error:

InternalError: (pymysql.err.InternalError) 
(1170, "BLOB/TEXT column 'Id' used in key specification without a key length") 
[SQL: 'CREATE INDEX `ix_test_table_index_Id` ON test_table_index (`Id`)']

If I don't set the index it works fine. Is there any way to store it without dropping directly down to SQLAlchemy to create the table first?

(This is my current SQLAlchemy workaround:

table = Table(
            name, self.metadata,
            Column('Id', String(ID_LENGTH), primary_key=True),
            Column('Date', DateTime, primary_key=True),
            Column('Value', String(VALUE_LENGTH)))
sa.MetaData().create_all(engine)  # Creates the table if it doens't exist

)

Answer 1

you can specify a SQLAlchemy data type explicitly, using dtype argument when calling to_sql() method:

In [48]: from sqlalchemy.types import VARCHAR

In [50]: df
Out[50]:
                     Value
Id       Date
AJP2008H 2010-05-05   74.2
BFA2010Z 2010-07-05   52.3

In [51]: df.to_sql('test_table_index', conn, if_exists='replace', 
                   dtype={'Id': VARCHAR(df.index.get_level_values('Id').str.len().max())})

Let's check it on the MySQL side:

mysql> show create table test_table_index\G
*************************** 1. row ***************************
       Table: test_table_index
Create Table: CREATE TABLE `test_table_index` (
  `Id` varchar(8) DEFAULT NULL,
  `Date` datetime DEFAULT NULL,
  `Value` double DEFAULT NULL,
  KEY `ix_test_table_index_Id` (`Id`),
  KEY `ix_test_table_index_Date` (`Date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> select * from test_table_index;
+----------+---------------------+-------+
| Id       | Date                | Value |
+----------+---------------------+-------+
| AJP2008H | 2010-05-05 00:00:00 |  74.2 |
| BFA2010Z | 2010-07-05 00:00:00 |  52.3 |
+----------+---------------------+-------+
2 rows in set (0.00 sec)

now let's read it back into a new DF:

In [52]: x = pd.read_sql('test_table_index', conn, index_col=['Id','Date'])

In [53]: x
Out[53]:
                     Value
Id       Date
AJP2008H 2010-05-05   74.2
BFA2010Z 2010-07-05   52.3

you can find the maximum length of your object column this way:

In [75]: df.index.get_level_values('Id').str.len().max()
Out[75]: 8