如何使用索引将Pandas数据框写入sqlite [英] How to write Pandas dataframe to sqlite with Index

问题描述

我在熊猫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.

推荐答案

在最近的熊猫中，索引将保存在数据库中(您以前必须

In recent pandas the index will be saved in the database (you used to have to reset_index first).

按照 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

这篇关于如何使用索引将Pandas数据框写入sqlite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！