CODEWITHSUNDEEP
pythonmysqlpandas
bigbobr
bigbobr

Reputation: 355

Write pandas dataframe to MySQL

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?

Upvotes: 1

Views: 2094

Answers (1)

joris
joris

Reputation: 139322

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.

Upvotes: 2

Related Questions