Как написать 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()