pandas 如何将 MySQL 时间戳（6）读入熊猫？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30929471/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read MySQL timestamp(6) into pandas?
提问by Thomas Schreiter
I have a MySql table with timestamps that have a microsecond resolution:
我有一个 MySql 表，其时间戳具有微秒分辨率：
+----------------------------+------+
| time | seq |
+----------------------------+------+
| 2015-06-19 02:17:57.389509 | 0 |
| 2015-06-19 02:17:57.934171 | 10 |
+----------------------------+------+
I want to read it into a pandas Dataframe. Using
我想将它读入Pandas数据框。使用
import pandas as pd
con = get_connection()
result = pd.read_sql("SELECT * FROM MyTable;", con=con)
print result
returns NaT (not a time):
返回 NaT（不是时间）：
time seq
0 NaT 0
1 NaT 10
How can I read it into a timestamp?
如何将其读入时间戳？
回答by Christopher Pearson
In general, to convert timestamps, you can to use the pandas.to_datetime().
一般来说，要转换时间戳，您可以使用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 in from SQL, you can explicitly force columns to be parsed as dates:
从文档中，当从 SQL 读入时，您可以明确强制将列解析为日期：
pd.read_sql_table('data', engine, parse_dates=['Date'])
or more explicitly, specify a format string, or a dict of arguments to pass to pandas.to_datetime():
或更明确地，指定格式字符串或要传递给的参数字典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 the timestamps as strings:
添加一个快速的概念证明。注意，我正在使用SQLITE. 假设您将时间戳存储为字符串：
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 are storing them as datetime objects, it works the same (the code differences are me getting the data into a database in datetime format):
或者，如果您将它们存储为 datetime 对象，它的工作原理是相同的（代码差异是我以 datetime 格式将数据放入数据库中）：
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 this helps.
希望这可以帮助。
EDITTo attemptto address a concern of @joris, it is true sqlitestores all datetimeobjects as strings, however the built-in adapter automatically converts these back to datetimeobjects when fetched. Extending the second example with:
编辑为了试图解决@joris的关注，它是真正的sqlite存储所有datetime对象作为字符串，但内置的适配器自动转换这些回datetime对象时取出。扩展第二个例子：
from sqlalchemy.sql import select
s = select([mydata])
res = conn.execute(s)
row = res.fetchone()
print(type(row['ts']))
results in <class 'datetime.datetime'>
结果是 <class 'datetime.datetime'>
