Sqlite3 모듈의 기본 사용법

간단한 예제로 sqlite3 모듈을 사용해서 데이터베이스를 조작하는 방법을 살펴보자.

파이썬은 sqlite3 데이터베이스를 사용할 수 있도록 sqlite3 모듈을 내장하고 있다. 기본적인 사용 순서는 다음과 같다.

  1. sqlite3.connect()를 사용해서 데이터베이스 파일을 열고 연결한다. connection 객체가 생성되며, 이를 통해 쿼리를 전달할 수 있다.
  2. connection.execute()를 사용해서 쿼리를 전송하고 실행할 수 있다.
  3. execute()의 결과로 cursor 객체를 얻게 되는데, 이 객체를 사용해서 영향을 받은 row의 수를 확인하거나, SELECT 문의 경우 각각의 row를 읽어올 수 있다.

sqlit3 모듈의 로딩과 데이터베이스 연결

sqlite3 모듈을 임포트하고 파일 경로를 넘겨 데이터베이스를 오픈한다. 만약 전달된 경로에 파일이 없다면 새 데이터베이스 파일이 만들어진다.

import sqlite3

db_file = 'temp.db'
conn = sqlite3.connect(db_file)

파일경로 대신 ":memory:"를 사용하면 인메모리 형식의 임시 데이터 베이스를 만들어서 사용할 수 있다.

테이블 삭제와 생성

DROP 문을 사용해서 테이블 삭제하고, CREATE 문을 사용해서 테이블을 생성할 수 있다.

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

데이터 삽입

conn.execute() 메소드를 사용하여 INSERT 문을 실행하여 데이터베이스에 레코드를 추가할 수 있다. 쿼리문은 일반 문자열이므로 format()등의 문자열 interpolation을 사용하여 쿼리를 생성하여 실행할 수 있지만, 일반적으로 sqlite3 라이브러리 내부에서 쿼리를 컴파일하고 바인딩하는 방법을 사용하는 것이 안전하다. 여기에는 두 가지 방법이 있는데, 먼저 쿼리 내에 바인딩되어야 할 부분을 ?으로 표시하고, execute() 메소드의 두 번째 인자로 튜플을 전달하는 방법이 있다.

아래 예에서는 칼럼이 하나 밖에 없기 때문에 (word, )와 같은 식으로 단일 요소 튜플을 만들어서 넘겨주는 점에 유의하자.

# 삽입
# 쿼리문에 값을 내삽하기 보다는 ? 값을 넘기고 이를 내부에서 합성하도록
# 하는 것이 안전한 방법이다. 
words = "apple banana chrerry orange pineapple".split()
for word in words:
    conn.execute("INSERT INTO temp (word) VALUES (?)", (word,))

다른 방법은 사전을 이용하는 것이다. 쿼리문 내에 :key 형식으로 사전의 키를 명시하고, execute() 메소드의 두 번째 인자로 사전을 전달하면 된다.

# 단일 레코드의 칼럼별 값은 사전의 구조와 비슷하며,
# 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)

데이터베이스 테이블의 단일 레코드는 각 필드에 대해 값이 묶여있는 구조이기 때문에 파이썬의 사전 객체와 구성이 비슷하다. 이 방식은 특히 UPDATE 문에서 편리하게 사용될 수 있다.

조회

execute()문을 사용하면 sqlite3.Cursor 객체라 리턴된다. 커서 객체는 수행된 쿼리에 대한 결과에 액세스할 수 있는 기능을 포함한다. fetchone()을 통해서 한 번에 하나의 row를 가져올 수 있으며, fetchall()를 통해서 모든 row를 리스트 형식으로 가져올 수 있다.

기본적으로 모든 row는 SELECT 문에서 명시한 칼럼들의 값이 순서대로 들어차 있는 튜플의 형태로 리턴된다.

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

row_factory

앞서 각 레코드가 사전과 비슷하다고 했던 것과 같이 하나의 레코드에서 각각의 컬럼 값을 각 컬럼의 이름으로 액세스할 수 있다면 조금 편리할 것이다. 커서에 대해서 row_factory 값을 지정하여 개별 행을 원하는 파이썬 타입으로 변환할 수 있다. sqlite3.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'])

sqlite3.Row 객체는 dict()에 전달해서 사전으로 변환할 수 있다. 사전으로 바로 얻고 싶다면 사전을 생성하는 팩토리 함수를 row_factory 속성으로 주면 된다. (이 속성은 fetch하기 전에만 주면 된다.)

# 다음과 같이 팩토리 함수를 만들어서 원하는 타입의 값으로 변환할 수 있다.
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())

커스텀 팩토리 함수

팩토리 함수는 (Cursor, Tuple)을 인자로 받는 함수로 커서와 각 행을 인자로 받아, 매 행을 특정 타입으로 변환할 수 있다. 커서는 description이라는 속성이 있어서 결과 매트릭스의 각 컬럼의 정보를 얻을 수 있다. 이 때 컬럼 이름은 각 정보(튜플)의 첫 요소이다. 람다식으로 표현한 팩토리 함수가 읽기 힘들다면 아래 함수를 참고하자.

def dict_factory(cursor, row):
    result = {}
    for col, item in enumerate(cursor.description):
        result[item[0]] = row[col]
    return result

레코드 갱신

레코드 갱신은 UPDATE 문을 사용한다.

# 레코드 갱신
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)

닫기

데이터베이스를 닫는 것은 connection.close()를 사용한다. 이 때, 만약 추가, 변경, 삭제등 데이터 베이스 내용을 변경하는 작업을 수행했다면 commit()을 먼저 호출해야 한다. sqlite3 데이터베이스는 동시에 여러 프로세스가 액세스할 수 있는데, 그 중 어느 하나의 프로세스가 데이터베이스를 변경하면 커밋하기 전까지는 다른 프로세스에는 해당 변경이 보이지 않는다. 또한 변경이 시작되면 데이터베이스는 다른 프로세스에서는 잠기게 되어 동시에 변경이 허용되지 않는다. 닫는 시점이 아니더라도 중간에 변경을 완료하는 시점에는 commit()을 호출해주어야 한다.

# 삽입, 갱신, 삭제 등을 처리했다면 commit을 해야 파일에 반영된다.
conn.commit()
conn.close()

컨텍스트 매니저

connection 객체는 컨텍스트 매니저 프로토콜을 따르며, 이는 with 문에서 사용될 수 있다는 의미이다. with문을 빠져나갈 때 변경 사항은 자동으로 커밋된다. 다음 예는 현재 소스 파일의 내용을 데이터베이스에 기록하는 것이다.

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'])

https://gist.github.com/sooop/5098139