How to read MySQL (6) timestamp in pandas?
I have a MySql table with timestamps that have resolution in microseconds:
+----------------------------+------+
| time | seq |
+----------------------------+------+
| 2015-06-19 02:17:57.389509 | 0 |
| 2015-06-19 02:17:57.934171 | 10 |
+----------------------------+------+
I want to read it into pandas Dataframe. Using
import pandas as pd
con = get_connection()
result = pd.read_sql("SELECT * FROM MyTable;", con=con)
print result
returns NaT (not time):
time seq
0 NaT 0
1 NaT 10
How can I read it into a timestamp?
source to share
In general you can use to convert timestamps pandas.to_datetime()
.
>>> import pandas as pd
>>> pd.to_datetime('2015-06-19 02:17:57.389509')
Timestamp('2015-06-19 02:17:57.389509')
From the docs, when reading from SQL, you can explicitly force the parse columns as dates:
pd.read_sql_table('data', engine, parse_dates=['Date'])
or more explicitly, provide a format string or argument argument to navigate to 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 a quick proof of concept. NOTE. I am using SQLITE
. Assuming you are storing timestamps as strings:
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData
import pandas as 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 you store them as datetime objects, it works the same (differences in code are getting data in database in datetime format):
from datetime import datetime
from sqlalchemy import create_engine, Table, Column, Integer, DateTime, MetaData
import pandas as 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 the same:
ts seq
0 2015-06-19 02:17:57.389509 0
1 2015-06-19 02:17:57.934171 10
Hope it helps.
EDIT To try and solve @joris's problem, it is true that it SQLITE
stores all objects datetime
as strings, however, the inline adapter will automatically convert them back to datetime
objects when retrieved. Extending the second example with
from sqlalchemy.sql import select s = select([mydata]) res = conn.execute(s) row = res.fetchone() print(type(row['ts']))
leads to <class 'datetime.datetime'>
source to share