pandas dataframe with Date index -> insert into MySQL

Question

The object df is of type pandas.core.frame.DataFrame.

In [1]: type(df)
Out[1]: pandas.core.frame.DataFrame

The index of df is a DatetimeIndex

In [2]: type(df.index)
Out[2]: pandas.tseries.index.DatetimeIndex

And con gives a working MySQLdb connection

In [3]: type(con)
Out[3]: MySQLdb.connections.Connection

I've not been able to get this dataframe entered into a MySQL database correctly, specifically, the date field comes through as null when using the following (as well as some variations on this).

df.to_sql( name='existing_table',con=con, if_exists='append', index=True, index_label='date', flavor='mysql', dtype={'date': datetime.date})

What are the steps required to have this dataframe entered correctly into a local MySQL database, with 'date' as a date field in the db?

It's a long-shot and that's why I'm writing a comment and not an answer, but does it work if instead of dtype={'date': datetime.date} you use dtype={'date': datetime.datetime}? If not, does it simply insert null to the db or does it throw an error? Also, by reading to_sql documentation here: pandas.pydata.org/pandas-docs/stable/generated/… , it seems like dtype is optional, but if present I believe it should contain all the columns. — DeepSpace
– DeepSpace, Commented Jul 23, 2015 at 9:15
made that change but it still just enters nulls. It doesn't throw an error in either case — datavoredan
– datavoredan, Commented Jul 23, 2015 at 9:21
Plain MySQLdb connections are not supported for writing to a database. You have to wrap that connection in a SQLAlchemy engine (pandas uses that for the database flavor specific differences). With a plain DBAPI connection, only sqlite is supported. See pandas.pydata.org/pandas-docs/stable/io.html#io-sql — joris
– joris, Commented Jul 23, 2015 at 9:24
You will have to upgrade your pandas version to at least 0.15 to be able to correctly write the dates to a MySQL database. With this older version, you can indeed use a MySQLdb connection, but datetime types were not processed correctly. Newer pandas versions improved the support by using SQLAlchemy (see my previous comment). — joris
– joris, Commented Jul 23, 2015 at 9:40

joris · Accepted Answer · 2015-07-23 10:19:51Z

1

To correctly write datetime data to SQL, you need at least pandas 0.15.0.

Starting from pandas 0.14, the sql functions are implemented using SQLAlchemy to deal with the database flavor specific differences. So to use to_sql, you need to provide it an SQLAlchemy engine instead of a plain MySQLdb connection:

from sqlalchemy import create_engine
engine = create_engine('mysql+mysqldb://....')

df.to_sql('existing_table', engine, if_exists='append', index=True, index_label='date')

Note: you don't need to provide the flavor keyword anymore.

Plain DBAPI connections are no longer supported for writing data to SQL, except for sqlite.

See http://pandas.pydata.org/pandas-docs/stable/io.html#io-sql for more details.

answered Jul 23, 2015 at 10:19

joris

140k37 gold badges257 silver badges207 bronze badges

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

Collectives™ on Stack Overflow

pandas dataframe with Date index -> insert into MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related