python pandas parse_dates column wildcard for sqlalchemy in pandas 0.14.1? -

i'm using sqlalchemy allows sql queries released 0.14.1 version of pandas.

import pandas pd dateutil import parser sqlalchemy import create_engine import datetime  a=[['datetime', 'now date', 'numbers', 'mixed'], ['1/2/2014', datetime.datetime.now(),6, 'z1'], ['1/3/2014', datetime.datetime.now(), 3, 'z1']] df = pd.dataframe(a[1:],columns=a[0]) df['datetime']=df['datetime'].map(lambda x: parser.parse(x))  engine=create_engine('sqlite:///:memory:') df.to_sql('db_table',engine, index=false) df_new=pd.read_sql_query("select * db_table ",engine)  >>> df.dtypes datetime    datetime64[ns] date    datetime64[ns] numbers              int64 mixed               object dtype: object  >>> df_new.dtypes datetime    object date    object numbers      int64 mixed       object dtype: object 

as can see, original datetime format lost when feeding engine. pandas gives way parsing.

df_new=pd.read_sql_query("select * db_table ",engine, parse_dates=['datetime','now date'])  >>> df_new.dtypes datetime    datetime64[ns] date    datetime64[ns] numbers              int64 mixed               object dtype: object 

the problem i'm feeding different kinds of datetimes engine different column names, can't manually specify each column name. have many things parse , changing. i'm looking solution equivalent of this:

df_new=pd.read_sql_query("select * db_table ",engine, parse_dates=['*date*']) 

sqlite has no date or datetime type. datetime values stored strings , when fetching query come strings.
there different options here deal this:

• use read_sql_table instead of read_sql_query (if need "select * ..." or columns, , need no clause). use information in table schema , detect datetime columns , convert them (sqlalchemy this):

  in [13]: df_new2 = pd.read_sql_table("db_table",engine)  in [15]: df_new2.dtypes out[15]:  datetime    datetime64[ns] date    datetime64[ns] numbers              int64 mixed               object dtype: object 

• you can specify sqlite3.parse_decltypes (see docs or question: how read datetime sqlite datetime instead of string in python?) when using sqlite connection:

  in [33]: con = sqlite3.connect(':memory:', detect_types=sqlite3.parse_decltypes)  in [34]: df.to_sql('db_table', con, index=false)  in [35]: df_new = pd.read_sql_query("select * db_table",con)  in [36]: df_new.dtypes  out[36]:  datetime    datetime64[ns] date    datetime64[ns] numbers              int64 mixed               object dtype: object 

  this not seem work nice sqlalchemy (http://docs.sqlalchemy.org/en/rel_0_9/dialects/sqlite.html#compatibility-with-sqlite3-native-date-and-datetime-types)

• you can parsing afterwards, automatically on columns contain 'date':

  in [45]: date_cols = [col col in df.columns if 'date' in col]  in [47]: col in date_cols:    ....:     df[col] = pd.to_datetime(df[col])    ....: