SQLite3는 매우 가볍고 부담이 적어서 모바일 어플리케이션에서도 많이 사용합니다. 그럼 Python sqlite3 사용법을 알아보겠습니다.
목차
Python sqlite3 사용법 개요
SQLite3 패키지 설치부터 DB 연결과 테이블 생성, 데이터 추가, 업데이트, 삭제를 해 볼 것입니다. 그리고 쿼리 결과를 튜플로 받지 않고 Python의 Dictionary 객체로 가져오는 방법과 커서 및 DB 연결을 종료하는 방법까지 쭉 살펴보겠습니다. 위에 목차가 있으니까, 필요한 게 있는 분은 필요한 부분만 찾아서 읽어보시고, python sqlite3 사용법을 모르는 분들은 분량이 많지 않으니 처음부터 끝까지 정주행 하시면 도움이 될 거예요.
sqlite3 패키지 설치
우선 python에서 sqlite3 라이브러리를 사용하려면 sqlite3 패키지를 설치해야 합니다. 이미 설치가 돼 있는 분은 넘어가시면 됩니다. 설치 명령은 아래와 같이 pip install 명령어를 사용해서 설치합니다.
pip install sqlite3sqlite3 DB 연결(connect)
사용하고자 하는 sqlite3 DB에 연결하는 작업입니다. sqlite3 데이터베이스 파일이 tutorial.db라고 한다면 다음과 같이 합니다. 연결할 때에는 connect() 메서드를 사용합니다.
import sqlite3 as sq
# 연결할 때
conn = sq.connect("tutorial.db")Cursor 객체 생성
conn으로 바로 쿼리를 사용할 수는 없고, 커서 객체를 생성한 후 커서 객체로 쿼리를 실행할 수 있습니다.
# Cursor 객체 생성
c = conn.cursor()테이블 생성(CREATE TABLE)
이제 테이블을 생성해 보겠습니다. Query문은 execute()를 통해서 실행하면 됩니다.
c.execute("CREATE TABLE users (id integer primary key, name text, age integer)")데이터 추가(INSERT INTO)
여러 개의 데이터를 추가할 때에는 execute() 보다 executemany()를 사용하는 편이 효율적입니다. 값의 자리에 ?를 작성하고, 추가 파라미터로 값들을 전달해 줍니다. 데이터는 tuple 단위로 담겨있어야 합니다.
users = [
("David", 73),
("Kylie", 12),
("Joy", 48),
("Hank", 87),
("Chloe", 24),
("Jacob", 56),
("Kimberly", 27),
]
c.executemany("INSERT INTO users (name, age) VALUES (?, ?)", users)데이터 업데이트 및 삭제(UPDATE & DELETE)
데이터 업데이트는 UPDATE 쿼리를 execute()을 통해서 실행해 주면 됩니다. 사용자 중 Kyle인 사람의 나이를 15로 변경하겠습니다. 만약 Kyle인 사람이 여럿이라면 모두 나이가 변경되겠죠? 그리고 David인 사용자는 삭제해 보겠습니다.
c.execute("UPDATE users SET age = 15 WHERE name = 'Kyle'")
c.execute("DELETE FROM users WHERE name = 'David'")반영된 레코드 개수 확인(rowcount)
INSERT, UPDATE, DELETE 문을 사용해서 추가, 업데이트, 삭제된 레코드 개수를 확인하고 싶을 때에는 커서 객체의 rowcount 값을 확인하면 됩니다.
c.rowcountCommit/Rollback
데이터를 추가하고 업데이트 한 내용을 DB에 반영할 것이라면 commit, 되돌리고 모든 변경을 취소할 것이라면 rollback을 실행하면 됩니다. 커서로 실행하지 않고 Database의 커넥션 객체를 사용해야 하는 점 주의하시기 바랍니다.
conn.commit()
conn.rollback()데이터 쿼리(SELECT)
SELECT 문을 이용해서 데이터를 읽어오겠습니다. 읽는 방법에는 여러가지가 있습니다. 하나씩 살펴보겠습니다.
c.execute("SELECT * FROM users");위와 같이 SQL문을 실행한 후, fetch 하는 방식에는 3가지 종류가 있습니다. 하나의 레코드만 불러올 때에는 fetchone()을 사용합니다.
row = c.fetchone()그리고 모든 레코드를 불러올 때에는 fetchall()을 사용합니다.
rows = c.fetchall()그리고 특정 개수만큼 fetch 하고자 하면 fetchmany()를 사용합니다. 파라미터로 array size를 넣어주면 됩니다.
rows = c.fetchmany(size=3)쿼리 결과 Dictionary 형식으로 받기
dict_factory 객체 별도로 만들어서 사용하기
쿼리 결과를 단순히 튜플 형식으로 받아오지 않고, dictionary로 받아오고 싶다면 아래와 같이 row_factory를 세팅해 준 후 커서를 생성한 후 이용하면 됩니다. MySQLdb는 MySQLdb.cursors.DictCursor가 준비되어 있어서 바로 쓰면 되는데, sqlite3 쪽은 요런 작업을 해줘야 한다네요.
c = conn.cursor()
c.execute("SELECT * FROM users")
rows = c.fetchall()
print(*rows, sep="\n")위와 같이 conn 객체의 row_factory를 지정해 주지 않았을 땐 아래와 같이 튜플 형식의 리스트로 돌려받습니다.
아래 코드와 같이 dict_factory 객체를 row_factory에 지정해 주면 그림 2와 같이 rows 객체에 딕셔너리 항목의 list로 데이터가 들어온 것을 확인할 수 있습니다. dict_factory 코드는 sqlite3 공식 문서에 있는 내용입니다.
def dict_factory(cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
conn.row_factory = dict_factory
c = conn.cursor()
c.execute("SELECT * FROM users")
rows = c.fetchall()
print(*rows, sep="\n")
pandas 이용해서 Dictionary로 받기
하지만 위와 같이 dict_factory를 별도로 설정해야 하는 과정은 귀찮고 번거롭습니다. 개발자들은 항상 레버리지를 사용해야 하는 거 아시죠? 이렇게 하면 좋을 것 같다는 생각이 드는 건 이미 만들어져 있는 경우가 많습니다.
그럼 이번에는 pandas를 이용해서 sqlite3에서 필요한 데이터를 바로 가지고 와 보겠습니다. 읽어오면 df는 pandas의 DataFrame 객체로 들어옵니다. to_dict 메서드를 사용하면 DataFrame 객체를 딕셔너리 객체로 변경할 수 있습니다. 파라미터로 “records”를 넣어주면 그림 2와 동일하게 출력되는 것을 확인할 수 있습니다.
import pandas as pd
import sqlite3
conn = sqlite3.connect("tutorial.db")
c = conn.cursor()
df = pd.read_sql_query("SELECT * FROM users", conn)
print(*df.to_dict("records"), sep="\n")만약 Dictionary 객체까지 만들지 않고 바로 DataFrame 객체로 활용할 일이 있다면 매우 편하게 사용할 수 있습니다.
커서 및 DB 연결 종료(close)
여기까지 읽느라 고생하셨습니다. 이제 Python sqlite3 사용법의 마지막 섹션입니다. 생성한 커서 객체를 모두 사용하고 정리할 때에는 close() 메서드를 실행해서 정리해 주면 됩니다. 그리고 db 연결 또한 마찬가지로 close() 메서드로 정리해 주면 됩니다.
# 다 사용한 커서 객체를 종료할 때
c.close()
# 연결 리소스를 종료할 때
conn.close()관련 자료
간단하게 python sqlite3 사용법을 살펴보았습니다. 더 자세한 python sqlite3 사용법을 익히기 원하시면 Python의 sqlite3 공식 문서를 참고하시기 바랍니다.