Python sqlite3 사용법(aka. SQLite DB 연동)

SQLite3는 매우 가볍고 부담이 적어서 모바일 어플리케이션에서도 많이 사용합니다. 그럼 Python sqlite3 사용법을 알아보겠습니다.

Python sqlite3 사용법 개요

SQLite3 패키지 설치부터 DB 연결과 테이블 생성, 데이터 추가, 업데이트, 삭제를 해 볼 것입니다. 그리고 쿼리 결과를 튜플로 받지 않고 Python의 Dictionary 객체로 가져오는 방법과 커서 및 DB 연결을 종료하는 방법까지 쭉 살펴보겠습니다. 위에 목차가 있으니까, 필요한 게 있는 분은 필요한 부분만 찾아서 읽어보시고, python sqlite3 사용법을 모르는 분들은 분량이 많지 않으니 처음부터 끝까지 정주행 하시면 도움이 될 거예요.

sqlite3 패키지 설치

우선 python에서 sqlite3 라이브러리를 사용하려면 sqlite3 패키지를 설치해야 합니다. 이미 설치가 돼 있는 분은 넘어가시면 됩니다. 설치 명령은 아래와 같이 pip install 명령어를 사용해서 설치합니다.

pip install sqlite3
ShellScript

sqlite3 DB 연결(connect)

사용하고자 하는 sqlite3 DB에 연결하는 작업입니다. sqlite3 데이터베이스 파일이 tutorial.db라고 한다면 다음과 같이 합니다. 연결할 때에는 connect() 메서드를 사용합니다.

import sqlite3 as sq

# 연결할 때
conn = sq.connect("tutorial.db")
Python

Cursor 객체 생성

conn으로 바로 쿼리를 사용할 수는 없고, 커서 객체를 생성한 후 커서 객체로 쿼리를 실행할 수 있습니다.

# Cursor 객체 생성
c = conn.cursor()
Python

테이블 생성(CREATE TABLE)

이제 테이블을 생성해 보겠습니다. Query문은 execute()를 통해서 실행하면 됩니다.

c.execute("CREATE TABLE users (id integer primary key, name text, age integer)")
Python

데이터 추가(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)
Python

데이터 업데이트 및 삭제(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'")
Python

반영된 레코드 개수 확인(rowcount)

INSERT, UPDATE, DELETE 문을 사용해서 추가, 업데이트, 삭제된 레코드 개수를 확인하고 싶을 때에는 커서 객체의 rowcount 값을 확인하면 됩니다.

c.rowcount
Python

Commit/Rollback

데이터를 추가하고 업데이트 한 내용을 DB에 반영할 것이라면 commit, 되돌리고 모든 변경을 취소할 것이라면 rollback을 실행하면 됩니다. 커서로 실행하지 않고 Database의 커넥션 객체를 사용해야 하는 점 주의하시기 바랍니다.

conn.commit()
conn.rollback()
Python

데이터 쿼리(SELECT)

SELECT 문을 이용해서 데이터를 읽어오겠습니다. 읽는 방법에는 여러가지가 있습니다. 하나씩 살펴보겠습니다.

c.execute("SELECT * FROM users");
Python

위와 같이 SQL문을 실행한 후, fetch 하는 방식에는 3가지 종류가 있습니다. 하나의 레코드만 불러올 때에는 fetchone()을 사용합니다.

row = c.fetchone()
Python

그리고 모든 레코드를 불러올 때에는 fetchall()을 사용합니다.

rows = c.fetchall()
Python

그리고 특정 개수만큼 fetch 하고자 하면 fetchmany()를 사용합니다. 파라미터로 array size를 넣어주면 됩니다.

rows = c.fetchmany(size=3)
Python

쿼리 결과 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")
Python

위와 같이 conn 객체의 row_factory를 지정해 주지 않았을 땐 아래와 같이 튜플 형식의 리스트로 돌려받습니다.

그림 1. Python sqlite3 사용법: 튜플로 구성된 리스트 출력
그림 1. Python sqlite3 사용법: 튜플로 구성된 리스트 출력

아래 코드와 같이 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")
Python
그림 2. Python sqlite3 사용법: 딕셔너리로 구성된 리스트 출력
그림 2. Python sqlite3 사용법: 딕셔너리로 구성된 리스트 출력

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")
Python

만약 Dictionary 객체까지 만들지 않고 바로 DataFrame 객체로 활용할 일이 있다면 매우 편하게 사용할 수 있습니다.

커서 및 DB 연결 종료(close)

여기까지 읽느라 고생하셨습니다. 이제 Python sqlite3 사용법의 마지막 섹션입니다. 생성한 커서 객체를 모두 사용하고 정리할 때에는 close() 메서드를 실행해서 정리해 주면 됩니다. 그리고 db 연결 또한 마찬가지로 close() 메서드로 정리해 주면 됩니다.

# 다 사용한 커서 객체를 종료할 때
c.close()

# 연결 리소스를 종료할 때
conn.close()
Python

관련 자료

간단하게 python sqlite3 사용법을 살펴보았습니다. 더 자세한 python sqlite3 사용법을 익히기 원하시면 Python의 sqlite3 공식 문서를 참고하시기 바랍니다.

같이 읽으면 좋은 글

Python mysqlclient 설치 오류 해결 방법 2가지(MySQLdb)

Python에서 MySQL 데이터베이스에 엑세스할 때 사용하는 라이브러리 중에 MySQLdb가 쉽게 설치되지 않는 경우가 있습니다. Ubuntu와 Windows에서 mysqlclient 설치시에 발생하는 문제와 해결하는 방법을 함께 알아보도록 하겠습니다.

Ubuntu 환경에서 mysqlclient 설치

mysqlclient 설치 오류 상황

pip install mysqlclient
ShellScript

만약 Anaconda를 사용하면서 위와 같은 명령을 실행했는데 아래와 같은 오류가 발생하는 경우가 있습니다.

그림 1. pip install 명령어 이용시 mysqlclient 설치 오류 상황
그림 1. pip install 명령어 이용시 mysqlclient 설치 오류 상황

Anaconda 사용시 설치 오류 해결 방법

아나콘다를 사용하는 경우에는 conda install 명령어를 사용해서 설치해 주시면 됩니다.

conda install mysqlclient
ShellScript

아래와 같이 무사히 설치가 되는 것을 확인할 수 있습니다.

그림 2. conda install로 mysqlclinet 설치 성공
그림 2. conda install로 mysqlclinet 설치 성공

Anaconda를 사용하지 않는 경우

아래와 같이 Python 3 및 MySQL 개발 헤더와 라이브러리를 설치해 준 후 다시 mysqlclient를 설치해 주세요.

sudo apt install python3-dev default-libmysqlclient-dev build-essential
pip install mysqlclient
ShellScript

Windows 환경에서 에러 해결방법

Windows에서도 pip install mysqlclient 명령만을 사용하면 오류가 발생하며 설치가 안 됩니다.

Windows의 경우에는 직접 소스코드를 빌드하는 방법과 미리 빌드된 wheel을 받아서 설치하는 방법이 있습니다. 전자의 경우에는 mysqlclient 페이지에서 빌드가 잘 안되는 경우에도 지원 요청을 하지 말라고 명시하고 있으니, 빌드를 꼭 해보고 싶은 경우가 아니라면 아래에 소개된 미리 빌드해 놓은 wheel 패키지를 받아서 설치하기 바랍니다.

Christoph Gohlke의 비공식 python Binary Wheels를 어바인 캘리포니아 주립대학 페이지에 32/64bit 별로 컴파일 해 둔 웹 페이지가 있습니다. 여기에서 mysqlclient를 찾고, 자신에게 맞는 wheels 파일을 받아서 설치하시기 바랍니다.

파이썬 버전과 32비트용과 64비트 용으로 나뉘어 whl 파일이 준비되어 있습니다.

그림 3. mysqlclient 윈도우용으로 빌드된 wheel
그림 3. mysqlclient 윈도우용으로 빌드된 wheel

다운 받은 후 pip install 명령어를 사용해서 설치하면 됩니다. 만약 python 3.9를 사용하며 64비트용으로 다운 받아서 설치한다면 아래와 같이 설치하면 됩니다.

pip install mysqlclient-1.4.6-cp39-cp39-win_amd64.whl
ShellScript

Python에서 테스트

python에서 mysqlclient가 제대로 설치되었다면 import 했을 때 오류가 없어야 합니다. mysqlclient라는 이름이지만 패키지 명은 MySQLdb입니다.

import MySQLdb
ShellScript

관련자료

mysqlclient 페이지를 참고하면 보다 자세한 설명을 확인할 수 있습니다.

같이 읽으면 좋은 글

Python 대용량 테이블 쿼리 속도 느려지는 문제 해결 방법 3가지

Python에서 큰 용량의 테이블에 자료를 insert하고 update 하다가 속도 느려지는 문제 경험하셨나요? 그랬다면 오랜 시간을 기다려야 해서 힘드셨죠? 대용량 테이블 쿼리 속도 빠르게 insert, update 할 수 있는 방법 3가지 알려드립니다.

속도 느려지는 문제 발견(INSERT, UPDATE 쿼리)

테이블 하나에 약 800만개의 레코드를 가진 테이블(약 1.2GB)을 만들고 분석할 일이 있었습니다. 그런데 매일 새로 생긴 데이터를 추가해주고, 변경된 데이터는 업데이트 해 주어야 하는 상황이었습니다.

약 1,000개의 레코드를 업데이트하는데 25초 정도 걸렸습니다. 이렇게 오래 걸린다는 게 수상합니다. 이건 분명 뭔가 잘못됐다는 신호입니다. 초당 40개 정도의 업데이트밖에 되지 않았는데, CPU가 그렇게 밖에 처리를 못할리가 없습니다.

대용량 테이블 쿼리 속도 빠르게 하는 방법

해결책은 그리 복잡하지 않았습니다. 저는 아래와 같은 3가지 방법으로 문제를 해결했습니다.

1000번의 SELECT 쿼리를 1번의 쿼리로 처리

SELECT query를 사용하여 레코드 하나씩 1,000번씩 쿼리를 던져서 필요한 값들을 확인하던 것을 한 번의 쿼리로 작업 단위에 필요한 만큼 불러온 후, python에서 관련 값들을 확인하고 처리하도록 변경했습니다. Database에 SELECT 쿼리를 한 번 날리는 게 얼마나 컴퓨터에 부하를 주는 작업인지 새삼 느끼게 된 경험이었습니다.

1000개의 INSERT 쿼리를 1개의 쿼리로 처리

DB에 데이터 INSERT시 1,000개의 INSERT query를 실행하던 것을 다음과 같이 한 개의 INSERT query로 변경했습니다. 이 방법을 사용하는 경우 query문이 너무 길어지는 경우 패킷 크기가 너무 크다면서 실행되지 않을 수 있습니다.

max_allowed_packet으로 쿼리 크기 설정

패킷 크기가 너무 크다고 오류 메시지로 알려준다면, my.cnf 파일의 [mysqld] 섹션에 max_allowed_packet값의 크기를 조정해 주면, 해당 크기만큼의 쿼리를 실행할 수 있습니다. 이 값을 어떻게 설정하느냐에 따라 하나의 쿼리를 실행할 수 있는 크기가 결정됩니다.

그리고 아래와 같은 INSERT 쿼리를 이용하면 1개의 쿼리로 여러개의 값을 INSERT 할 수 있습니다.

insert into 테이블명 (필드1, 필드2, ...) values (값1a, 값2a, ...), (값1b, 값2b, ...), ..., (값na, 값 nb, ...)

최소한의 Key사용, 불필요한 인덱스 사용하지 않기

UPDATE시 1,000개의 query를 실행하는 것은 동일하지만, WHERE 절에 Key 두 개를 사용해서 업데이트 하던 것을 primary key 하나만 사용하도록 변경하였습니다. 알만한 분들은 다들 아시는 내용이겠지만, Query를 최적화하는 것은 매우 중요합니다. 작업하는 Table의 특성에 대해서도 명확히 파악해야, 가장 정확하고 빠른 Query를 사용할 수 있겠죠?

최적화 결과, 0.6초 이내에 처리!

처음에 1,000개 레코드 처리에 25초 정도 걸렸다고 말씀드렸었죠? 위의 3가지 방법을 모두 적용한 후 평균 0.6초 이내로 처리시간이 단축되었습니다.

적용하기 전에는 Mysqld 프로세스의 CPU 점유율이 단일코어기준으로 100%이었는데, 변경 후에는 약6% 언저리에서 작동했습니다. 작업 환경은 다음과 같습니다. 사양이 아주 좋은 PC도 아니었습니다. CPU는 인텔 G4400, 메모리 8G, SSD 128G, OS는 Windows 10, MariaDB 버전은 10.2를 사용했습니다. 그리고 프로그래밍 언어는 Python3에서 MySQLdb를 사용하였습니다.

특히 대용량 테이블의 경우에는 DB의 부하를 줄이는 방향으로 최적화 해야 합니다. 위의 사례를 보면 무엇보다도 기본적인 원칙이 가장 중요하다고 할 수 있습니다.

관련자료

MySQL의 너무 큰 패킷을 위한 설정 방법에 대한 문서를 참고하시면 max_allowed_packet 파라미터 사용에 대한 자세한 내용을 확인할 수 있습니다.

같이 읽으면 좋은 글