sqlite-study.py

# *coding*: utf-8
import sqlite3
import time

# 데이터베이스 연결 생성
# 존재하지 않는 파일이라고 가정. 새 파일을 생성함
db_filename = 'test.db' 
conn = sqlite3.connect(db_filename)

# 테이블 제거, 생성
# DROP, CREATE 문을 사용함
conn.execute("DROP TABLE IF EXISTS temp")
conn.execute("CREATE TABLE IF NOT EXISTS temp ("
             "id INTEGER PRIMARY KEY AUTOINCREMENT, " 
             "word VARCHAR(256), "
             "datetime DATETIME DEFAULT CURRENT_TIMESTAMP"
             ")"
)


# 삽입
# 쿼리문에 값을 내삽하기 보다는 ? 값을 넘기고 이를 내부에서 합성하도록
# 하는 것이 안전한 방법이다. 
words = "apple banana chrerry orange pineapple".split()
for word in words:
    conn.execute("INSERT INTO temp (word) VALUES (?)", (word,))
# 단일 레코드의 칼럼별 값은 사전의 구조와 비슷하며,
# execute 내에서 사전의 키에 대해서 `:key`와 같은 식으로 연결하는 방법도` 있다.
data = {'word': 'data from dictionary', 'datetime': '2020-12-25 06:30:00'}
conn.execute("INSERT INTO temp (word, datetime) VALUES (:word, :datetime)", data)


# 조회문을 실행하면 cursor 객체가 리턴된다.
# 이 객체의 `fetchone()`,`fetchall()` 메소드를 사용해서 데이터를 얻을 수 있다.
# 기본적으로 각각의 row는 튜플 형태로 리턴된다.
cs = conn.execute('SELECT * FROM temp')
for row in cs.fetchall():
    print(row)


cs = conn.execute('SELECT * FROM temp')
# `row_factory` 값을 `sqlite3.Row`로 주면 각 칼럼의 이름으로 값을 액세스할 수 있는
# dict-like 객체가 된다.
cs.row_factory = sqlite3.Row
for row in cs.fetchall():
    print(row['id'], row['word'])

# 다음과 같이 팩토리 함수를 만들어서 원하는 타입의 값으로 변환할 수 있다.
dict_factory = lambda c, rows : dict(zip((x[0] for x in c.description), rows))
cs = conn.execute('SELECT * FROM temp')
cs.row_factory = dict_factory
print(cs.fetchone())
    


# 레코드 갱신
time.sleep(2)
cs = conn.execute("UPDATE temp SET word = upper(word), "
             "datetime = DATETIME('NOW') WHERE word > 'b';")

# 변경된 row의 수를 cursor.rowcount로 확인할 수 있다.
# 전체 내역을 다시 출력하여 확인해본다.
print(f'{cs.rowcount} rows are updated.')
cs = conn.execute('SELECT * FROM temp')
for row in cs.fetchall():
    print(row)


# 삭제
query = "DELETE from temp WHERE word > 'F';"
# 커서를 별도로 미리 생성할 수 있으며, 커서를 사용해서 쿼리를 실행할 수 있다.
cs_del = conn.cursor()
cs_del.execute(query)
print(f'{cs_del.rowcount} rows are deleted.')
cs_del.execute('SELECT * FROM temp')
for row in cs_del.fetchall():
    print(row)


# 데이터베이스 닫기
# 삽입, 갱신, 삭제 등을 처리했다면 commit을 해야 파일에 반영된다.
# 또한, 동시에 같은 db파일을 열어서 사용하는 다른 프로그램에게도
# 변경 사항이 반영된다. 
# 커밋하지 않은 변경이 있는 동안에는 다른 프로세스에게는 데이터베이스 파일이 잠기게 된다.
conn.commit()
conn.close()

# with 문을 사용해서 파일을 열고 변경을 자동으로 커밋하게 한다.
# 이를 사용해서 본 소스를 라인별로 DB에 저장할 수 있다.
with sqlite3.connect(db_filename) as db:
    with open(__file__, 'r', encoding='utf8') as f:
        for line in f:
            db.execute('INSERT INTO temp (word) VALUES (?)', (line.strip(),))
    cs = db.execute('SELECT word FROM temp')
    cs.row_factory  = sqlite3.Row
    for row in cs.fetchall():
        print(row['word'])