如何使用索引将Pandas数据框写入sqlite

Question

我在熊猫DataFrame中有一个从Yahoo提取的股市数据列表（请参见下面的格式）。该日期用作DataFrame中的索引。我想将数据（包括索引）写出到SQLite数据库中。

             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代码的了解，它目前不支持编写index。我尝试使用to_records代替，但是遇到了Numpy
1.6.2和datetimes
的问题。现在，我尝试使用.itertuples编写元组，但是SQLite抛出一个错误，指出该数据类型不受支持（请参见下面的代码和结果）。我是Python，Pandas和Numpy的新手，所以很可能我缺少明显的东西。我认为尝试将日期时间写入SQLite时遇到问题，但我认为这可能会使问题变得过于复杂。

我认为我 可以 通过升级到Numpy
1.7或Pandas的开发版本来解决此问题，该版本已在GitHub上发布了修复程序。我更喜欢使用发行版的软件进行开发-
这是我的新手，我不希望稳定性问题进一步困扰您。

有没有办法使用Python 2.7.2，Pandas 0.10.0和Numpy 1.6.2做到这一点？也许以某种方式清理日期时间？我有些烦恼，不胜感激。

码：

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.

Answer 1

在最近的大熊猫中，索引将保存在数据库中（您以前必须reset_index先保存）。

遵循文档（在内存中设置SQLite连接）：

import sqlite3
# Create your connection.
cnx = sqlite3.connect(':memory:')

注意：您还可以在此处传递SQLAlchemy引擎（请参见答案结尾）。

我们可以保存price2到cnx：

price2.to_sql(name='price2', con=cnx)

我们可以通过read_sql以下方式检索：

p2 = pd.read_sql('select * from price2', cnx)

但是，存储（和检索）的 日期unicode不是Timestamp。要转换回我们开始使用的内容，可以使用pd.to_datetime：

p2.Date = pd.to_datetime(p2.Date)
p = p2.set_index('Date')

我们返回与以下相同的DataFrame 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一起使用）：

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