Python으로 SQLite 데이터베이스 다루기: sqlite3 모듈 완벽 가이드

SQLite 개요

SQLite는 서버 없이 동작하는(serverless) 오픈 소스 데이터베이스로, 별도의 설치나 설정 과정이 필요하지 않습니다. 데이터베이스 전체가 하나의 디스크 파일로 저장되므로 운영체제 파일 시스템의 어느 위치에든 자유롭게 배치할 수 있습니다. SQLite의 명령어는 표준 SQL과 거의 유사하며, 웹 브라우저 같은 애플리케이션의 내부 데이터 저장소로 널리 사용되고 있습니다. 또한 임베디드 기기를 위한 간편한 데이터 저장 수단으로도 활용됩니다.

파이썬의 sqlite3 모듈과 DB-API

파이썬 표준 라이브러리에는 SQLite 데이터베이스 연결 기능이 기본적으로 내장되어 있습니다. 핵심은 sqlite3 모듈로, 게르하르트 해링(Gerhard Häring)이 작성한 이 모듈은 DB-API 2.0 규격을 준수합니다.

DB-API는 PEP-249 문서에 따라 정의된 표준으로, 서로 다른 데이터베이스 제품에 접근하는 파이썬 모듈들이 일관된 인터페이스를 갖도록 보장합니다. 덕분에 MySQL, PostgreSQL 등 다른 DB를 사용하더라도 비슷한 방식으로 코드를 작성할 수 있습니다.

데이터베이스 처리의 기본 흐름

데이터베이스 작업의 첫 번째 단계는 데이터베이스를 대표하는 커넥션(connection) 객체를 얻는 것입니다. 그다음 이 커넥션을 통해 커서(cursor) 객체를 생성합니다. 커서는 반복자(iterator)와 유사한 개념으로, 쿼리 결과로 반환된 행(row)들을 하나씩 처리하는 데 사용됩니다. 즉, 커서를 결과 행 집합을 가리키는 포인터라고 생각하면 됩니다.

커서 객체를 활용하면 SQL 쿼리를 실행해 기반 데이터베이스에 대해 생성(Create), 조회(Read), 수정(Update), 삭제(Delete) 같은 CRUD 작업을 수행할 수 있습니다.

connect() 함수

sqlite3 모듈의 connect() 함수는 디스크에 존재하는 데이터베이스를 나타내는 커넥션 객체를 반환하며, 해당 데이터베이스가 아직 없다면 새로 생성합니다.

import sqlite3
conn = sqlite3.connect('mydb.sqlite3')

SQLite는 인메모리(in-memory) 데이터베이스 생성도 지원합니다. 이 경우 특별한 이름인 ':memory:'를 사용하면 됩니다.

conn = sqlite3.connect(':memory:')

Connection 클래스의 주요 메서드

cursor() — 커서 객체를 반환합니다.

cur = conn.cursor()

commit() — 진행 중인 트랜잭션을 디스크에 영구적으로 반영(커밋)합니다.

rollback() — 마지막 commit() 호출 시점 이후의 변경 사항을 되돌려 데이터베이스 상태를 복원합니다. SQL 트랜잭션 처리 중 예외가 발생했을 때 데이터 일관성을 유지하는 데 유용합니다.

try:
    conn.commit()
except:
    conn.rollback()

execute() — 내부적으로 커서를 생성하고 해당 커서의 execute() 메서드를 호출합니다.

executemany() — 내부적으로 커서를 생성하고 해당 커서의 executemany() 메서드를 호출합니다.

create_function() — SQL 문 안에서 나중에 사용할 수 있는 사용자 정의 함수를 생성합니다.

create_aggregate() — 집계(aggregate) 함수를 생성합니다.

iterdump() — 데이터베이스 전체를 SQL 텍스트 형식의 덤프(dump)로 생성합니다.

import sqlite3

con = sqlite3.connect('mydb.sqlite3')
with open('dump.sql', 'w') as f:
    for line in con.iterdump():
        f.write('%s\n' % line)

backup() — 데이터베이스가 사용 중인 경우에도 SQLite 데이터베이스의 백업을 생성합니다.

source = sqlite3.connect('mydb.sqlite3')
dest = sqlite3.connect(':memory:')
source.backup(dest)

커서 객체로 SQL 실행하기

커넥션 객체에서 얻은 커서를 사용하면 execute() 메서드를 통해 다양한 SQL 작업을 수행할 수 있습니다. 예를 들어 다음 문장은 현재 데이터베이스에 테이블을 생성합니다.

cur = conn.cursor()
cur.execute("""CREATE TABLE guests (
   ID INTEGER PRIMARY KEY,
   name TEXT (20) NOT NULL,
   address TEXT (40),
   city TEXT (20)
);""")

다음은 테이블에 레코드 하나를 삽입하는 코드입니다.

cur.execute("""INSERT INTO GUESTS VALUES(1,'RAJU','ABIDS','HYDERABAD');""")

대량의 데이터를 한 번에 추가할 때는 executemany() 메서드를 사용하는 것이 효율적입니다.

addresses = [(2, 'KISHAN', 'TILAK ROAD', 'PUNE'),
            (3, 'LATA', 'GANDHI NAGAR', 'AURANGABAD')]
sql = 'INSERT INTO GUESTS VALUES (:ID, :NAME, :ADD, :CITY)'
cur.executemany(sql, addresses)

SELECT 결과 조회: fetchone()과 fetchall()

SELECT 쿼리를 실행하면 쿼리 응답으로 반환된 모든 레코드를 담은 결과 집합(result set)이 만들어집니다. execute() 메서드에는 SELECT 쿼리 문자열을 인자로 전달하며, DB-API는 커서에 담긴 결과 집합에서 행을 가져오는 두 가지 메서드를 제공합니다.

fetchone() — 결과 집합에서 다음으로 사용 가능한 레코드 하나를 튜플(tuple) 형태로 반환합니다. 튜플의 각 요소는 해당 레코드의 각 열(column) 값입니다.

fetchall() — 남아 있는 모든 레코드를 튜플의 리스트 형태로 반환합니다. 각 튜플은 한 행에 대응하며 테이블의 각 열 값을 포함합니다.

다음 코드는 fetchone() 메서드를 사용해 guests 테이블의 모든 레코드를 출력합니다.

sql = "SELECT * from guests;"
cur = conn.cursor()
cur.execute(sql)
while True:
    record = cur.fetchone()
    if record == None:
        break
    print(record)

fetchall() 메서드의 사용 예는 다음과 같습니다.

sql = "SELECT * from guests;"
cur = conn.cursor()
cur.execute(sql)
rows = cur.fetchall()
for record in rows:
    print(record)

SQLite와 파이썬 타입 매핑

SQLite의 데이터 타입은 기본적으로 다음 표와 같이 대응되는 파이썬 데이터 타입으로 자동 변환(매핑)됩니다.

주요 예외(Exception)

DB-API는 SQLite 데이터베이스 관련 SQL 작업에서 발생할 수 있는 다음과 같은 예외를 정의하고 있습니다.