Python을 이용한 sqlite3 사용 예제입니다. sqlite는 python을 설치하면 기본으로 설치되어있으며, db를 파일 형태로 저장하기 때문에 backup이나 관리가 쉬운 편입니다. 하지만 속도나 대용량 지원등 여러가지 제약 사항은 있습니다.
구현 하고자 하는 내용은 저장은 sqlite3로 하고 읽는 부분은 pandas dataframe을 이용하였습니다.
아래와 같이 테이블을 만들고 body 부분에 text를 넣는 예제입니다.
예제에서 입력을 원하는 db table 구조
table : maintbl
date : body
-----------+------
2019-02-18 : hello
2019-02-19 : nice
응용 해볼 수 있는 곳은 웹에서 자료를 읽어서 db에 읽어서 날짜별로 저장하고 읽을때는 DataFrame 사용하기 위한 용도입니다.
좀 더 단순화 위해서 database class를 제작하였습니다. 기본 idx 가 기본키로 지정되어 있습니다. pandas로 data를 읽는 부분은 다음 코드를 이용합니다.
df = pd.read_sql_query("SELECT * FROM %s" % table, self.con)
database.py
import os
import sqlite3
import datetime
import pandas as pd
from pandas import Series, DataFrame
#print(str(sqlite3.version))
class SqliteDb():
def __init__(self):
super().__init__()
def connect(self, name):
self.con = sqlite3.connect(name)
self.cursor = self.con.cursor()
return self.cursor
def make_table(self, table ):
self.cursor.execute("CREATE TABLE "+ table + "(idx INTEGER PRIMARY KEY)")
def exists_table(self, table):
try :
self.cursor.execute("SELECT * FROM " + table)
return True
except :
return False
def exists_table_column(self, table, coulumn) :
try :
self.cursor.execute("SELECT %s FROM %s "%(coulumn, table))
return True
except :
return False
def add_column(self, table, column_name_type) :
# add column https://www.sqlite.org/datatype3.html
# add_column(TABLE_NAME,'column_name float')
sql = "ALTER TABLE %s ADD COLUMN %s" % (table, column_name_type)
self.cursor.execute(sql)
def insert_data(self, table, values):
# column 순서 대로 입력됨
#insert_data(TABLE_NAME,"'2018-01-01',1000")
sql = "INSERT INTO %s VALUES(%s)" % (table, values)
self.cursor.execute(sql)
def insert_data_with_cols(self, table, cols, values):
# 원하는 column 에 원하는 값을 넣을때 사용
#insert_data_with_cols("table_name","column_name1,column_name2","value1,value2")
sql = "INSERT INTO %s(%s) VALUES(%s)" % (table, cols, values)
self.cursor.execute(sql)
def get_value(self, table, cols, condition):
#value = get_value("table_name","colname","idx=value")
sql = "SELECT %s FROM %s WHERE %s" % (cols, table, condition)
cursor = self.cursor.execute(sql)
row = cursor.fetchone()
if row==None : return None
if len(row)==0 : return None
return row[0]
def update_value(self, table, setvalue, condition):
#update_value("table_name","colname=value","idx=value")
sql = "UPDATE %s SET %s WHERE %s" % (table, setvalue, condition)
self.cursor.execute(sql)
def get_dataframe(self, table):
df = pd.read_sql_query("SELECT * FROM %s" % table, self.con)
return df
def close(self):
self.con.commit()
self.con.close()
컬럼을 추가할때 (
db.add_column(PARAM_DB_TABLE_MAIN,"body TEXT")) db의 datatype은
https://www.sqlite.org/datatype3.html 링크를 참고합니다.
maintbl을 생성하는 예제 코드
import database
PARAM_DB_NAME = "dbname.db"
PARAM_DB_TABLE_MAIN = "maintbl"
db = database.SqliteDb()
db.connect(PARAM_DB_NAME)
# Table
if not db.exists_table(PARAM_DB_TABLE_MAIN):
db.make_table(PARAM_DB_TABLE_MAIN)
# Columns
if not db.exists_table_column(PARAM_DB_TABLE_MAIN,"date"):
db.add_column(PARAM_DB_TABLE_MAIN,"date TEXT")
if not db.exists_table_column(PARAM_DB_TABLE_MAIN,"body"):
db.add_column(PARAM_DB_TABLE_MAIN,"body TEXT")
# insert update
idxvalue = db.get_value(PARAM_DB_TABLE_MAIN, "idx", "date='2019-02-18'")
if idxvalue==None:
db.insert_data_with_cols(PARAM_DB_TABLE_MAIN,"date","'2019-02-18'")
db.insert_data_with_cols(PARAM_DB_TABLE_MAIN,"body","'hello'")
else:
db.update_value(PARAM_DB_TABLE_MAIN,"body='hello'","idx=%d"%idxvalue)
# insert update
idxvalue = db.get_value(PARAM_DB_TABLE_MAIN, "idx", "date='2019-02-19'")
if idxvalue==None:
db.insert_data_with_cols(PARAM_DB_TABLE_MAIN,"date","'2019-02-19'")
db.insert_data_with_cols(PARAM_DB_TABLE_MAIN,"body","'nice'")
else:
db.update_value(PARAM_DB_TABLE_MAIN,"body='nice'","idx=%d"%idxvalue)
db.close()
db2 = database.SqliteDb()
db2.connect(PARAM_DB_NAME)
df = db2.get_dataframe(PARAM_DB_TABLE_MAIN)
print(df)
db에서 insert data를 할때 주의할점
insert data를 하면 row가 하나 증가됩니다.
즉, 위 실행 예제로 하면 아래와 같이 나옵니다.
실행예제1)
idx date body
0 1 2019-02-18 None
1 2 None hello
2 3 2019-02-19 None
3 4 None nice
다시 동작시키면 아래와 같이 날짜 뒤 body 컬럼이 채워집니다.
실행예제2)
idx date body
0 1 2019-02-18 hello
1 2 None hello
2 3 2019-02-19 nice
3 4 None nice
원했던 내용이 아닙니다.
insert를 하게되면 row가 하나 생겨나기 때문에 idx를 얻어서 나머지 컬럼은 update를 하는 방식을 사용하였습니다. 아래 부분이 변경되었습니다.
idxvalue = db.get_value(PARAM_DB_TABLE_MAIN, "idx", "date='2019-02-18'")
if idxvalue==None:
db.insert_data_with_cols(PARAM_DB_TABLE_MAIN,"date","'2019-02-18'")
idxvalue = db.get_value(PARAM_DB_TABLE_MAIN, "idx", "date='2019-02-18'")
db.update_value(PARAM_DB_TABLE_MAIN,"body='hello'","idx=%d"%idxvalue)
변경된 소스
import database
PARAM_DB_NAME = "dbname.db"
PARAM_DB_TABLE_MAIN = "maintbl"
db = database.SqliteDb()
db.connect(PARAM_DB_NAME)
# Table
if not db.exists_table(PARAM_DB_TABLE_MAIN):
db.make_table(PARAM_DB_TABLE_MAIN)
# Columns
if not db.exists_table_column(PARAM_DB_TABLE_MAIN,"date"):
db.add_column(PARAM_DB_TABLE_MAIN,"date TEXT")
if not db.exists_table_column(PARAM_DB_TABLE_MAIN,"body"):
db.add_column(PARAM_DB_TABLE_MAIN,"body TEXT")
# insert update
idxvalue = db.get_value(PARAM_DB_TABLE_MAIN, "idx", "date='2019-02-18'")
if idxvalue==None:
db.insert_data_with_cols(PARAM_DB_TABLE_MAIN,"date","'2019-02-18'")
idxvalue = db.get_value(PARAM_DB_TABLE_MAIN, "idx", "date='2019-02-18'")
db.update_value(PARAM_DB_TABLE_MAIN,"body='hello'","idx=%d"%idxvalue)
# insert update
idxvalue = db.get_value(PARAM_DB_TABLE_MAIN, "idx", "date='2019-02-19'")
if idxvalue==None:
db.insert_data_with_cols(PARAM_DB_TABLE_MAIN,"date","'2019-02-19'")
idxvalue = db.get_value(PARAM_DB_TABLE_MAIN, "idx", "date='2019-02-19'")
db.update_value(PARAM_DB_TABLE_MAIN,"body='nice'","idx=%d"%idxvalue)
db.close()
db2 = database.SqliteDb()
db2.connect(PARAM_DB_NAME)
df = db2.get_dataframe(PARAM_DB_TABLE_MAIN)
print(df)
최종 실행결과
idx date body
0 1 2019-02-18 hello
1 2 2019-02-19 nice
주의 : clean 테스트를 위해서는 현재 폴더에 있는 dbname.db 삭제해야합니다.
