python - How to read MySQL timestamp(6) into pandas? -

i have mysql table timestamps have microsecond resolution:

+----------------------------+------+ | time                       | seq  |  +----------------------------+------+ | 2015-06-19 02:17:57.389509 |    0 |  | 2015-06-19 02:17:57.934171 |   10 | +----------------------------+------+ 

i want read pandas dataframe. using

import pandas pd con = get_connection() result = pd.read_sql("select * mytable;", con=con) print result 

returns nat (not time):

    time  seq  0   nat    0   1   nat   10   

how can read timestamp?

in general, convert timestamps, can use pandas.to_datetime().

>>> import pandas pd >>> pd.to_datetime('2015-06-19 02:17:57.389509') timestamp('2015-06-19 02:17:57.389509') 

from docs, when reading in sql, can explicitly force columns parsed dates:

pd.read_sql_table('data', engine, parse_dates=['date']) 

or more explicitly, specify format string, or dict of arguments pass pandas.to_datetime():

pd.read_sql_table('data', engine, parse_dates={'date': '%y-%m-%d'}) 

or

pd.read_sql_table('data', engine, parse_dates={'date': {'format': '%y-%m-%d %h:%m:%s'}}) 

adding quick proof of concept. note, using sqlite. assuming storing timestamps strings:

from sqlalchemy import create_engine, table, column, integer, string, metadata import pandas pd  engine = create_engine('sqlite:///:memory:', echo=true)  datapoints = [{'ts': '2015-06-19 02:17:57.389509', 'seq': 0},               {'ts':'2015-06-19 02:17:57.934171', 'seq': 10}] metadata = metadata() mydata = table('mydata', metadata,     column('ts', string),     column('seq', integer), ) metadata.create_all(engine) ins = mydata.insert() conn = engine.connect() conn.execute(ins, datapoints)  foo = pd.read_sql_table('mydata', engine, parse_dates=['ts']) print(foo) 

outputs:

                           ts  seq 0  2015-06-19 02:17:57.389509    0 1  2015-06-19 02:17:57.934171   10 

or, if storing them datetime objects, works same (the code differences me getting data database in datetime format):

from datetime import datetime sqlalchemy import create_engine, table, column, integer, datetime, metadata import pandas pd  engine = create_engine('sqlite:///:memory:', echo=true)  datapoints = [{'ts': datetime.strptime('2015-06-19 02:17:57.389509', '%y-%m-%d %h:%m:%s.%f'), 'seq': 0},               {'ts':datetime.strptime('2015-06-19 02:17:57.934171', '%y-%m-%d %h:%m:%s.%f'), 'seq': 10}] metadata = metadata() mydata = table('mydata', metadata,     column('ts', datetime),     column('seq', integer), ) metadata.create_all(engine) ins = mydata.insert() conn = engine.connect() conn.execute(ins, datapoints)  foo = pd.read_sql_table('mydata', engine, parse_dates=['ts']) print(foo) 

outputs same:

                          ts  seq 0 2015-06-19 02:17:57.389509    0 1 2015-06-19 02:17:57.934171   10 

hope helps.

edit attempt address concern of @joris, true sqlite stores datetime objects strings, built-in adapter automatically converts these datetime objects when fetched. extending second example with:

from sqlalchemy.sql import select s = select([mydata]) res = conn.execute(s) row = res.fetchone() print(type(row['ts'])) 

results in <class 'datetime.datetime'>