Using pandas.io.sql.read_frame, can I parse_dates, as in read_csv?

Question

I am reading a data_frame directly from a database using pandas.io.sql.read_frame:

cnx = pandas.io.sql.connect(host='srv',user='me',password='pw',database='db')
df = pandas.io.sql.read_frame('sql_query',cnx)

It works nicely in retrieving the data. But I would like to parse one of the columns as a datetime64, akin to what can be done when reading from a CSV file, e.g.:

df2 = pandas.io.read_csv(csv_file, parse_dates=[0])

But there is no parse_dates flag for read_frame. What alternative approach is recommended?

The same question applies to the index_col in read_csv, which indicates which col. should be the index. Is there a recommended way to do this with read_frame?

• Dan Allan: I contributed the latest version of pandas.io.sql to pandas, and it is still a work in progress, particularly detection of specific datatypes. I expect an upcoming version will contain big improvements. You can catch up on some recent discussion here: github.com/pydata/pandas/issues/1662 and here: github.com/pydata/pandas/issues/2717
• Dan Allan: That said, for me, MySQL TIMESTAMP columns are parsed correctly as pd.tslib.Timestamp objects. And there is an index_col argument for read_frame. Are you using the latest stable release of pandas?
• random.me: @DanAllan Good work on pandas.io.sql! I am using pandas v. '0.10.1' I was trying to use index_col=[0], as I do with pandas.io.read_csv, and it failed: KeyError: u'no item named 0'. After reading your comment, I tried index_col=[key_name_string] instead, and it worked. Also, as the required column index is a datetime, pandas now correctly identifies the DataFrame as having a DatetimeIndex. So my problem is solved, thank you! However, before I set the col. as index, the DateTime type was not parsed correctly, so a parse_dates argument for pandas.io.sql.read_frame would be great.
• Dan Allan: I added this to our discussion. Thanks for your feedback.
• Mechanical snail: This is also relevant for reading from SQLite dbs, since SQLite has no datetime column affinity (you just store timestamps as ISO 8601 text).

Answer 1

This question is very old by now. pandas 0.10 is very old as well. In the newest version of pandas 0.16, the read_frame method has been depricated in favour of the read_sql. Even so, the documentation says that just like the read_csv function, it takes a parse_dates argument Pandas 0.16 read_frame

It seems the parse_dates argument appeared in 0.14, at the same time as read_frame was depricated. The read_sql function seems to be a rename of the read_frame, so just updating your pandas version to 0.14 or higher and renaming your function will give you access to this argument. Here is the doc for the read_sql function: Pandas 0.16 read_sql

Answer 2

data_frame["column"] = pandas.to_datetime(data_frame["column"])

should work by default but if not you can specify options. See the doc.

Answer 3

df = pandas.io.sql.read_frame('sql_query', index=['date_column_name'], con=cnx)

where date_column_name is the name of the column in the database that contains date elements. sql_query should then be of the form select date_column_name, data_column_name from ...

Pandas (as of 0.13+) will then automatically parse it to a date format if it resembles a date string.

In [34]: df.index
Out[34]: 
 <class 'pandas.tseries.index.DatetimeIndex'>