[Python] 파이썬 SQLite3 모듈 사용법 & 예제 총정리

데이터를 효율적으로 관리하기 위해 우리는 데이터베이스를 사용합니다. 하지만 간단한 프로그램에서 별도의 데이터베이스를 연결하는 것이 번거로운 작업일 수 있겠죠. 이럴 때는 파이썬에서 기본적으로 제공하는 SQLite3 모듈을 사용하면 별도의 DB연동 없이 데이터베이스를 사용할 수 있는데요. SQLite는 파이썬에서 제공하는 데이터베이스 라이브러리로 경량이면서 별도의 서버가 필요 없이 사용할 수 있는 내장형 데이터베이스 엔진으로 가볍게 사용하기에 좋습니다.

 

---

 

 파이썬 SQLite3 모듈 사용법 

SQLite 연결하기

import sqlite3

# SQLite 연결 객체 생성
con1 = sqlite3.connect('test.db') # 파일 DB접속
con2 = sqlite3.connect(':memory:') # 메모리 DB접속(1회성)

# 커서 객체 생성
cursor1 = con1.cursor()
cursor2 = con2.cursor()

SQLite 데이터베이스에 연결하려면 먼저 sqlite3 내장 모듈을 임포트 하고 connect() 메서드를 사용하여 커넥션 객체를 생성해야 합니다. 데이터베이스 연결이 성공하면 Connection 연결 객체가 반환되고, 이 객체를 통해 데이터베이스 작업을 수행할 수 있습니다. 이렇게 생성된 데이터베이스 연결은 프로그램이 종료되거나 close() 메서드가 호출될 때까지 유지됩니다. 그리고 연결 객체를 생성한 뒤에는 cursor() 메서드를 호출하여 커서도 생성해줘야 합니다. 커서는 SQL문을 실행하고 결과를 처리하는 데 사용됩니다.

 

 ※ connect() 메서드의 인자값으로 :memory:를 전달하면 메모리 DB를 사용할 수 있습니다. 

 

메모리 DB란?
SQLite는 메모리 DB를 지원합니다. 메모리 DB는 말 그대로 메모리 안에 저장되는 휘발성 데이터베이스를 뜻하며 프로그램이 종료되면 데이터가 사라집니다. 메모리 데이터베이스는 주로 작은 규모의 데이터를 임시로 저장하거나 테스트 목적으로 사용됩니다. 실제로 디스크에 데이터가 저장되지 않기 때문에 빠른 속도로 작업할 수 있다는 장점도 있습니다.

 

---

 

테이블 생성하기 (CREATE)

import sqlite3

# SQLite 연결 객체 생성
con = sqlite3.connect('test.db') 

# 커서 객체 생성
cursor = con.cursor()

# 테이블 생성 SQL 문
create_sql = '''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);
'''

# 테이블 생성
cursor.execute(create_sql)

# 변경사항 저장
con.commit()

데이터를 데이터베이스에 저장하려면 먼저 테이블이 있어야 합니다. 데이터베이스에 테이블을 생성하려면 CREATE문을 사용합니다. CREATE TABLE 쿼리를 작성하고 아까 받아왔던 커서 객체에서 execute() 메서드를 사용하여 작성한 CREATE TABLE 쿼리를 전송하면 테이블이 생성됩니다. 참고로 마지막으로 데이터베이스에 변경사항이 생겼다면 꼭 commit() 메서드를 호출해야 변경사항을 영구적으로 저장됩니다. 

 

※ 메모리 DB는 commit을 생략하셔도 무방합니다.

 

---

 

데이터 삽입하기 (INSERT)

import sqlite3

# SQLite 연결 객체 생성
con = sqlite3.connect('test.db') 

# 커서 객체 생성
cursor = con.cursor()

# 데이터 삽입 SQL 문
insert_sql = "INSERT INTO users (name, age) VALUES ('이순신', 25);"
cursor.execute(insert_sql)

# 변경사항 저장
con.commit()

테이블을 만들었으면 데이터를 삽입해야 합니다. 마찬가지로 커서객체에서 execute() 메서드를 호출한 뒤 INSERT 쿼리를 전송합니다. 그리고 commit() 메서드를 호출하여 변경사항을 영구적으로 저장합니다.

 

 

쿼리문 파라미터 사용하기

import sqlite3

# SQLite 연결 객체 생성
con = sqlite3.connect('test.db') 

# 커서 객체 생성
cursor = con.cursor()

# 데이터 삽입 SQL 문
insert_sql = "INSERT INTO users (name, age) VALUES (?, ?);"

# 데이터 삽입
user = ('김유신', 25)
cursor.execute(insert_sql, user)

# 변경사항 저장
con.commit()

SQL에 인자값을 받을 수도 있습니다. SQL문의 ? 물음표 기호가 파라미터를 받는다는 뜻입니다. 인자값으로 전달할 변수는 반드시 튜플 형태로 지정해야 하며, 튜플에 데이터가 하나만 있는 경우에 콤마를 뒤에 붙여야 합니다. 위의 예시는 INSERT문이지만 INSERT뿐만이 아니라 모든 쿼리문에서 사용이 가능합니다.

 

 

다중 행 작업하기

import sqlite3

# SQLite 연결 객체 생성
con = sqlite3.connect('test.db') 

# 커서 객체 생성
cursor = con.cursor()

# 데이터 삽입 SQL 문
insert_sql = "INSERT INTO users (name, age) VALUES (?, ?);"

# 다중의 행을 삽입
userlist = (("이순신","30"), ("박문수","27"))
cursor.executemany(insert_sql, userlist)

# 변경사항 저장
con.commit()

다중행을 작업하려면 커서의 execute() 함수 대신 executemany() 함수를 사용하시면 됩니다.

 

---

 

데이터 조회하기 (SELECT)

import sqlite3

# SQLite 연결 객체 생성
con = sqlite3.connect('test.db') 

# 커서 객체 생성
cursor = con.cursor()

# 데이터 조회 SQL 문
select_sql = "SELECT * FROM users;"

# 데이터 조회
cursor.execute(select_sql)

# 결과 가져오기
rows = cursor.fetchall()

# 결과 출력
for row in rows:
    print(row)

조금 전에 INSERT 한 데이터를 조회해 보도록 하겠습니다. 데이터베이스에서 데이터를 조회할 때는 SELECT문을 사용합니다. execute() 메서드를 통해 날려야 합니다. 그리고 결과는 fetchall() 메서드를 호출하여 쿼리의 결과를 가지고 오며, 결과는 튜플의 리스트로 반환됩니다. 이렇게 반환된 리스트는 반복문을 활용하는 등의 후처리를 통해 다양하게 활용할 수 있습니다.

 

---

 

데이터 갱신하기 (UPDATE)

import sqlite3

# SQLite 연결 객체 생성
con = sqlite3.connect('test.db') 

# 커서 객체 생성
cursor = con.cursor()

# 데이터 갱신 SQL 문
update_sql = "UPDATE users SET age = ? WHERE name = ?;"

# 데이터 갱신
age = 26
name = '김유신'
cursor.execute(update_sql, (age, name))

# 변경사항 저장
con.commit()

데이터베이스에서 데이터를 수정할 때는 UPDATE문을 사용합니다.  마찬가지로 execute() 메서드를 통해 작성한 UPDATE 쿼리를 날리면 되고, 데이터베이스 변경사항을 COMMIT 하여 저장합니다.

 

---

데이터 삭제하기 (DELETE)

import sqlite3

# SQLite 연결 객체 생성
con = sqlite3.connect('test.db') 

# 커서 객체 생성
cursor = con.cursor()

# 데이터 삭제 SQL 문
delete_sql = "DELETE FROM users WHERE name = ?;"

# 데이터 삭제
user_name = '이순신'
cursor.execute(delete_sql, (user_name,))

# 변경사항 저장
con.commit()

데이터베이스에서 데이터를 삭제할 때는 DELETE문을 사용합니다. UPDATE와 마찬가지로 execute() 메서드를 통해 작성한 DELETE 쿼리를 날린 뒤 데이터베이스 변경사항을 COMMIT 하여 저장합니다.

 

 

연결 종료하기

import sqlite3

try:
    # 데이터베이스에 연결
    conn = sqlite3.connect('test.db')

    # 커서 생성
    cursor = conn.cursor()

    # 여러 데이터베이스 작업 수행

finally:
    # 연결 종료 (예외가 발생해도 실행됨)
    conn.close()

데이터베이스는 작업이 끝나면 항상 close() 메서드를 호출하여 연결을 끊어줘야 합니다. 이렇게 해야 예기치 않게 발생할 수 있는 리소스 누수를 방지할 수 있고 데이터베이스의 데이터 일관성도 유지할 수 있습니다. 일반적으로는 위와 같이 try와 finally를 사용하여 예외가 발생해도 연결을 닫을 수 있도록 합니다.

 

• 리소스 누수(Resource Leak): 데이터베이스 연결을 끊지 않으면 시스템의 자원을 계속해서 사용하게 되어, 시간이 갈수록 시스템에 부담을 줄 수 있습니다. 특히 프로그램이 계속 실행되는 동안 여러 번 데이터베이스에 연결하고 해제하는 경우에는 리소스 누수로 인해 프로그램 오작동 가능성이 높아집니다.
• 트랜잭션 관리: 데이터베이스 연결을 끊지 않으면 트랜잭션(Transaction)이 자동으로 커밋되지 않을 수 있습니다. 트랜잭션은 여러 SQL 문을 하나의 작업으로 묶는 데 사용되며, 이 트랜잭션이 커밋되어야 데이터베이스에 영구적으로 적용됩니다.