(Python : Basic) 데이터 DB로 관리

import sqlite3

con = sqlite3.connect("./test.db")
cursor = con.cursor()
cursor.execute("CREATE TABLE kakao(Date text, Open int, High int, Low int, Closing int, Volumn int)")
cursor.execute("INSERT INTO kakao VALUES('16.06.03', 97000, 98600, 96900, 98000, 321405)")

con.commit()
# close connection
con.close()

import sqlite3

con = sqlite3.connect("./test.db")
cursor = con.cursor()

cursor.execute("SELECT * FROM kakao")

kakao = cursor.fetchall()

print(kakao[0][0])   # '16.06.03'
print(kakao[0][1])   # 97000

# close connection
con.close()

import pandas as pd
from pandas import Series, DataFrame
import sqlite3

raw_data = {'col0': [1, 2, 3, 4], 'col1': [10, 20, 30, 40], 'col2':[100, 200, 300, 400]}
df = DataFrame(raw_data)

"""
In [5]: df
Out[5]:         
col0 col1 col2
0  1   10   100
1  2   20   200
2  3   30   300
3  4   40   400
"""

con = sqlite3.connect("./test.db")
df.to_sql('testcolumn', con)

# 데이터가 클 경우 chunksize를 지정해 줘야한다
df.to_sql('testcolumn', con, chunksize=1000)

import pandas as pd
from pandas import Series, DataFrame
import sqlite3


con = sqlite3.connect("./test.db")

df = pd.read_sql("SELECT * FROM kakao", con, index_col=None)

# 특정 column을 index로 지정가능
df = pd.read_sql("SELECT * FROM testcolumn", con, index_col='index')

# write
import pandas as pd
import pandas_datareader.data as web
import datetime
import sqlite3

start = datetime.datetime(2010, 1, 1)
end = datetime.datetime(2016, 6, 12)
df = web.DataReader("078930.KS", "yahoo", start, end)

con = sqlite3.connect("./kospi.db")
df.to_sql('078930', con, if_exists='replace')

con.close()

# read
import pandas as pd
import pandas_datareader.data as web
import datetime
import sqlite3

con = sqlite3.connect("./kospi.db")
readed_df = pd.read_sql("SELECT * FROM '078930'", con, index_col = 'Date')

con.close()