使用索引Pandas数据框写入sqlite
问题说明
我在熊猫DataFrame中有一个从Yahoo提取的股市数据列表(请参见下面的格式).该日期用作DataFrame中的索引.我想将数据(包括索引)写出到SQLite数据库中.
I have a list of stockmarket data pulled from Yahoo in a pandas DataFrame (see format below). The date is serving as the index in the DataFrame. I want to write the data (including the index) out to a SQLite database.
AAPL GE
Date
2009-01-02 89.95 14.76
2009-01-05 93.75 14.38
2009-01-06 92.20 14.58
2009-01-07 90.21 13.93
2009-01-08 91.88 13.95
根据我对Pandas的write_frame代码的了解,它目前不是支持编写索引.我尝试改用to_records,但遇到了带有Numpy 1.6.2和日期时间的问题.现在,我试图使用.itertuples编写元组,但是SQLite抛出一个错误,指出该数据类型不受支持(请参见下面的代码和结果).我是Python,Pandas和Numpy的新手,所以很可能我缺少明显的东西.我认为尝试将日期时间写入SQLite时遇到问题,但是我认为这可能会使问题变得过于复杂.
Based on my reading of the write_frame code for Pandas, it does not currently support writing the index. I've attempted to use to_records instead, but ran into the issue with Numpy 1.6.2 and datetimes. Now I'm trying to write tuples using .itertuples, but SQLite throws an error that the data type isn't supported (see code and result below). I'm relatively new to Python, Pandas and Numpy, so it is entirely possible I'm missing something obvious. I think I'm running into a problem trying to write a datetime to SQLite, but I think I might be overcomplicating this.
我认为我也许可以通过升级到Numpy 1.7或Pandas的开发版本来解决此问题,该版本已在GitHub上发布了修复程序.我更喜欢使用发行版的软件进行开发-这是我的新手,我也不希望稳定性问题进一步困扰您.
I think I may be able to fix the issue by upgrading to Numpy 1.7 or the development version of Pandas, which has a fix posted on GitHub. I'd prefer to develop using release versions of software - I'm new to this and I don't want stability issues confusing matters further.
有没有一种方法可以使用Python 2.7.2,Pandas 0.10.0和Numpy 1.6.2来实现?也许以某种方式清理日期时间?我有些烦恼,不胜感激.
Is there a way to accomplish this using Python 2.7.2, Pandas 0.10.0, and Numpy 1.6.2? Perhaps cleaning the datetimes somehow? I'm in a bit over my head, any help would be appreciated.
代码:
import numpy as np
import pandas as pd
from pandas import DataFrame, Series
import sqlite3 as db
# download data from yahoo
all_data = {}
for ticker in ['AAPL', 'GE']:
all_data[ticker] = pd.io.data.get_data_yahoo(ticker, '1/1/2009','12/31/2012')
# create a data frame
price = DataFrame({tic: data['Adj Close'] for tic, data in all_data.iteritems()})
# get output ready for database export
output = price.itertuples()
data = tuple(output)
# connect to a test DB with one three-column table titled "Demo"
con = db.connect('c:/Python27/test.db')
wildcards = ','.join(['?'] * 3)
insert_sql = 'INSERT INTO Demo VALUES (%s)' % wildcards
con.executemany(insert_sql, data)
结果:
---------------------------------------------------------------------------
InterfaceError Traceback (most recent call last)
<ipython-input-15-680cc9889c56> in <module>()
----> 1 con.executemany(insert_sql, data)
InterfaceError: Error binding parameter 0 - probably unsupported type.
正确答案
在最近的熊猫中,索引将保存在数据库中(您以前必须 docs (设置一个内存中的SQLite连接):
Following the docs (setting a SQLite connection in memory):
import sqlite3
# Create your connection.
cnx = sqlite3.connect(':memory:')
注意:您还可以在此处传递SQLAlchemy引擎(请参见答案结尾).
我们可以将price2
保存到cnx
:
price2.to_sql(name='price2', con=cnx)
我们可以通过read_sql
进行检索:
We can retrieve via read_sql
:
p2 = pd.read_sql('select * from price2', cnx)
但是,当存储(和检索)时,日期是unicode
,而不是Timestamp
.要转换回我们开始使用的内容,可以使用pd.to_datetime
:
However, when stored (and retrieved) dates are unicode
rather than Timestamp
. To convert back to what we started with we can use pd.to_datetime
:
p2.Date = pd.to_datetime(p2.Date)
p = p2.set_index('Date')
我们返回与prices
相同的DataFrame:
We get back the same DataFrame as prices
:
In [11]: p2
Out[11]:
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 1006 entries, 2009-01-02 00:00:00 to 2012-12-31 00:00:00
Data columns:
AAPL 1006 non-null values
GE 1006 non-null values
dtypes: float64(2)
您还可以使用 SQLAlchemy引擎:
from sqlalchemy import create_engine
e = create_engine('sqlite://') # pass your db url
price2.to_sql(name='price2', con=cnx)
这允许您使用read_sql_table
(只能与SQLAlchemy一起使用):
This allows you to use read_sql_table
(which can only be used with SQLAlchemy):
pd.read_sql_table(table_name='price2', con=e)
# Date AAPL GE
# 0 2009-01-02 89.95 14.76
# 1 2009-01-05 93.75 14.38
# 2 2009-01-06 92.20 14.58
# 3 2009-01-07 90.21 13.93
# 4 2009-01-08 91.88 13.95
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /reply/detail/tanhcfiakg
-
YouTube API 不能在 iOS (iPhone/iPad) 工作,但在桌面浏览器工作正常?
it1352 07-30 -
iPhone,一张图像叠加到另一张图像上以创建要保存的新图像?(水印)
it1352 07-17 -
保持在后台运行的 iPhone 应用程序完全可操作
it1352 07-25 -
使用 iPhone 进行移动设备管理
it1352 07-23 -
在android同时打开手电筒和前置摄像头
it1352 09-28 -
扫描 NFC 标签时是否可以启动应用程序?
it1352 08-02 -
检查邮件是否发送成功
it1352 07-25 -
Android微调工具-删除当前选择
it1352 06-20 -
希伯来语的空格句子标记化错误
it1352 06-22 -
Android App 和三星 Galaxy S4 不兼容
it1352 07-20