Как написать Pandas dataframe для sqlite с индексом

У меня есть список данных фондового рынка, извлеченных из Yahoo в pandas DataFrame (см. формат ниже). Дата служит индексом в 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

Основываясь на моем чтении кода write_frame для Pandas, он в настоящее время не поддерживает запись индекса. Вместо этого я попытался использовать to_records, но столкнулся с проблемой с Numpy 1.6.2 и datetimes. Теперь я пытаюсь написать кортежи с использованием .itertuples, но SQLite выдает ошибку, что тип данных не поддерживается (см. Код и результат ниже). Я относительно новичок в Python, pandas и Numpy, поэтому вполне возможно, что я пропустил что-то очевидное. Я думаю, что у меня возникла проблема с попыткой написать datetime для 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.

Ответ 1

Как вы помните, на данный момент вы сохраняете индекс, но мы можем сделать reset_index, сохранив старый индекс как столбец ('Date').

price2 = price.reset_index()

In [11]: price2
Out[11]: 
<class 'pandas.core.frame.DataFrame'>
Int64Index: 1006 entries, 0 to 1005
Data columns:
Date    1006  non-null values
AAPL    1006  non-null values
GE      1006  non-null values
dtypes: datetime64[ns](1), float64(2)

Следуя docs (установка соединения SQLite в памяти):

import sqlite3
from pandas.io import sql
# Create your connection.
cnx = sqlite3.connect(':memory:')

Мы можем сохранить price2 до cnx:

sql.write_frame(price2, name='price2', con=cnx)

Мы можем получить через read_frame:

p2 = sql.read_frame('select * from price2', cnx)

Однако при сохранении (и восстановлении) дат unicode, а не Timestamp. Чтобы вернуться к тому, с чего мы начали, мы могли apply Timestamp в колонку и set_index:

from pandas.lib import Timestamp
p2.Date = p2.Date.apply(Timestamp)
p = p2.set_index('Date')

Мы возвращаем тот же DataFrame, что и prices:

In [20]: p
Out[20]: 
<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)

Ответ 2

К сожалению, pandas.io.write_frame больше не существует в более поздних версиях Pandas в отношении текущего принятого ответа. Например, я использую панды 0.19.2. Вы можете сделать что-то вроде

from sqlalchemy import create_engine

disk_engine = create_engine('sqlite:///my_lite_store.db')
price.to_sql('stock_price', disk_engine, if_exists='append')

А затем, в свою очередь, просмотрите таблицу со следующим:

df = pd.read_sql_query('SELECT * FROM stock_price LIMIT 3',disk_engine)
df.head()

Ответ 3

Ниже приведен код, который работал для меня. Я смог записать его в SQLite DB.

import pandas as pd
import sqlite3 as sq
data = <This is going to be your pandas dataframe>
sql_data = 'D:\\SA.sqlite' #- Creates DB names SQLite
conn = sq.connect(sql_data)
cur = conn.cursor()
cur.execute('''DROP TABLE IF EXISTS SA''')
data.to_sql('SA', conn, if_exists='replace', index=False) # - writes the pd.df to SQLIte DB
pd.read_sql('select * from SentimentAnalysis', conn)
conn.commit()
conn.close()