[월:] 2018년 05월

SQLite 연동

1. 설치

sudo apt-get install sqlite3

2. 파이썬에서 sqlite3 import

import sqlite3

3. CREATE(DB생성 및 테이블 생성)
connect 할때 DB명을 주게 되면 DB 파일이 없는경우 실행경로에 DB를 생성한다.

dbCon = sqlite3.connect(DATABASE_NAME)
dbCursor = dbCon.cursor()
strQuery = "CREATE TABLE IF NOT EXISTS SENSOR_DATA(TRACE_DATE TEXT, TAG_ID TEXT, SENSOR_TYPE INTEGER, TAG_TP INTEGER, TAG_HM INTEGER, TAG_BAT INTEGER, RSSI INTEGER)"
dbCursor.execute(strQuery)
dbCursor.close()
dbCon.close()

3. INSERT

dbCon = sqlite3.connect(DATABASE_NAME)
dbCursor = dbCon.cursor()
(중간생략)
strQuery = "INSERT INTO TEST_DATA(TRACE_DATE, ID) VALUES (?, ?)"
dbCursor.execute(strQuery, (clsInfo.getTraceDate(), clsInfo.getId()))
dbCon.commit()
dbCursor.close()
dbCon.close()

3. COUNT

dbCursor.execute("SELECT COUNT(*) FROM TEST_DATA")
intCount = dbCursor.fetchone()[0]
print("Count:%d" % intCount)

4. SELECT

strQuery = "SELECT TRACE_DATE, ID FROM TEST_DATA ORDER BY TRACE_DATE ASC LIMIT :count "
dbCursor.execute(strQuery, {"count" : MAX_REPORT_COUNT})
arrRows = dbCursor.fetchall()
for objRow in arrRows:
	strTraceDate    = objRow[ 0]

5 Delete

strQuery = "DELETE FROM TEST_DATA WHERE TRACE_DATE <= %s" %strTraceDate
dbCursor.execute(strQuery)