Python3, Pandas and MySQL index issue

user_1712045 0
Viewed 2675

I'm new on Python and trying to learn some basic Data Manipulation (the main focus is Data Science). So I'm still grasping Pandas and everything else. What I'm trying to achieve, is to create a DataFrame and store it on a MySQL database. This is my script (that don't work):

from sqlalchemy.types import VARCHAR
from sqlalchemy import create_engine
import pandas as pd
import numpy as np

frame = pd.DataFrame(np.random.random((4,4)),
                 index=['val1','val2','val3','val4'],
                 columns=['col1','col2','col3','col4'])
engine = create_engine('mysql+pymysql://user:password@localhost/python_samples')
frame.to_sql('rnd_vals', engine, dtype={'index':VARCHAR(5)})

When I try to execute this, I get the error saying that MySQL won't allow to create a TEXT/BLOB index withouth the length:

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

I believed that I could fix this, by specifying the dtype option on the to_sql() function, but it didn't help. I found a way of making this, by joining two DataFrames, one with the values, and the other one with the index:

from sqlalchemy.types import VARCHAR
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
frame = pd.DataFrame(np.random.random(25).reshape(5,5),
                 columns=['Jan','Feb','Mar','Apr','May'])
idxFrame = pd.DataFrame({'index':['exp1','exp2','exp3','exp4','exp5']})
frame = frame.join(idxFrame)
frame=frame.set_index('index')
    engine = create_engine('mysql+pymysql://user:password@localhost/python_samples')
frame.to_sql('indexes',engine,if_exists='replace', index_label='index',
         dtype={'index':VARCHAR(5)})

This works as expected, but I really doubt that this is the correct way of making this, can someone help me? What did I did wrong?

Thank you

pandas indexing sqlalchemy python-3.x mysql
5 Answers

I tried to find a direct way to enable pandas to directly import the index. In the end reset_index() seems the simplest method:

my_df.reset_index()
my_df.to_sql(name='my_table', con=engine, index=False, if_exists='replace')
user_8944870

With :

frame.to_sql('rnd_vals', engine, dtype={'None':VARCHAR(5)})

It was giving :

1170, "BLOB/TEXT column 'index' used in key specification without a key length") [SQL: 'CREATE INDEX ix_indexes_index ON indexes (index)'] (Background on this error at: http://sqlalche.me/e/e3q8)

This resolved the issue : 

frame.to_sql('indexes',engine,if_exists='replace', index_label='index',dtype={frame.index.name:VARCHAR(5)})
user_108101920

You are trying to make an index from a column of type text/blob. In this case, MySQL unable to put uniqueness to the columns because of dynamic nature. There is no length associated with this. You can provide the type of column while saving dataframe to MySQL or (if you don't need an index) just make index=False.

user_53969310
Related