How to write Pandas dataframe to sqlite with Index
In recent pandas the index will be saved in the database (you used to have to reset_index
first).
Following the docs (setting a SQLite connection in memory):
import sqlite3
# Create your connection.
cnx = sqlite3.connect(':memory:')
Note: You can also pass a SQLAlchemy engine here (see end of answer).
We can save price2
to cnx
:
price2.to_sql(name='price2', con=cnx)
We can retrieve via read_sql
:
p2 = pd.read_sql('select * from price2', cnx)
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')
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)
You can also use a SQLAlchemy engine:
from sqlalchemy import create_engine
e = create_engine('sqlite://') # pass your db url
price2.to_sql(name='price2', con=cnx)
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
Below is the code which worked for me. I was able to write it to 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()
Unfortunately, pandas.io.write_frame
no longer exists in more recent versions of Pandas in regards to the current accepted answer. For example I'm using pandas 0.19.2. You can do something like
from sqlalchemy import create_engine
disk_engine = create_engine('sqlite:///my_lite_store.db')
price.to_sql('stock_price', disk_engine, if_exists='append')
And then in turn preview your table with the following:
df = pd.read_sql_query('SELECT * FROM stock_price LIMIT 3',disk_engine)
df.head()