Write pandas dataframe to MySQL

Question

I've got some weird problem here and stuck. I'm rewriting python script that generates some CSV files, and I need to write the same info on MySQL server.

I've managed to get it working... somehow.

Here is the part that creates CSV:

final_table.get_tunid_town_pivot().to_csv('result_pivot_tunid_town_' + ConsoleLog.get_curr_date_underline() + '.csv', sep=';')

And here is the part that loads data into MySQL table:

conn = pymysql.connect(host='localhost', port=3306, user='test', passwd='test', db='test')
final_table.get_tunid_town_pivot().to_sql(con=conn, name='TunID', if_exists='replace', flavor='mysql', index=False, chunksize=10000)
conn.close()

The problem is that there are 4 columns in dataframe, but in MySQL i get only one last column. I have no idea why is that happening, and I found zero similar problems. Any help please?

What version of pandas are you using? Further, can you try this with using an SQLAlchemy engine (see pandas.pydata.org/pandas-docs/stable/io.html#sql-queries). Lastly, can you show final_table.get_tunid_town_pivot().info() ? — joris
– joris, Commented Dec 8, 2015 at 14:27
SQLAlchemy was my first try with same result. info shows this: MultiIndex: 258 entries, (21, 85.21.230.5, eriwan) to (21, 85.21.230.5, zmm) Data columns (total 1 columns): Count 258 non-null int64. How i could be possible that this table have 1 column? — bigbobr
– bigbobr, Commented Dec 8, 2015 at 14:37
So you have 1 column in your dataframe? The it sounds logical to also have 1 column in the SQL table? If you want to include the MultiIndex as columns in the SQL table, specify index=True instead of index=False — joris
– joris, Commented Dec 8, 2015 at 14:55
Yes, i've already tried to use index=True, and it gives me error ValueError: Invalid MySQL identifier 'Tunnel ID' — bigbobr
– bigbobr, Commented Dec 8, 2015 at 14:57

joris · Accepted Answer · 2015-12-08 14:57:23Z

2

Your DataFrame has (probably due to the pivoting) a MultiIndex of 3 levels and only 1 column. By default, to_sql will also write the index to the SQL table, but you did specify index=False, so only the one column will be written to SQL.

So either do not specify to not include the index (so use index=True), or either reset the index and write the frame then (df.reset_index().to_sql(..., index=False)).

Also note that using a pymysql connection in to_sql is deprecated (it should give you a warning), you have to use it through an SQLAlchemy engine.

answered Dec 8, 2015 at 14:57

joris

140k37 gold badges257 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Write pandas dataframe to MySQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related