Python / December 6, 2021

[파이썬] 예제 스크립트로 알아보는 SQLite3 기초 사용법

파이썬에서 sqlite 데이터베이스 사용법에 대해 알아봅니다.

Oracle, MS-SQL, MySql 등의 데이터베이스는 별도의 설치 및 연동과정이 필요합니다.

SQLite3는 별도의 설치 과정 없이 바로 import하여 사용가능하며 데이터베이스 서버의 구성이 필요없습니다.

SQLite3 기본 연동 사용법

1. 데이터베이스를 사용하기 위해 connect()함수를 사용하여 db파일을 생성하고 연결합니다.

isolation_level=None 옵션을 주게되면, update, insert, delete문과 같이 데이터베이스의 데이터에 변화를 주는 쿼리문에 대하여 자동 커밋(commit) 처리를 합니다.

오토커밋을 해제하려면 isolation_level=None 옵션을 제거합니다.

자동 커밋을 해제한 경우 반드시 conn.commit() 또는 conn.rollback()함수를 실행해야합니다.

그렇지 않으면 다른 사용자가 접근할 때 테이블에 락이 걸려 기다리는 상태가 됩니다.

commit()은 데이터베이스에 적용함을 의미하며, rollback()은 작업한 내용을 모두 날린다는 의미입니다.

import sqlite3

#DB 파일 생성 및 연결
conn = sqlite3.connect("C:\python\database\customer_database.db", isolation_level=None)

2. connection에 Cursor객체를 생성 후 execute()함수를 호출하여 SQL 명령을 실행합니다.

# 커서 연결
cursor = conn.cursor()

여기까지 모든 작업을 위한 기본 작업입니다. 위 두 줄의 코드는 항상 쌍으로 구성되어 사용됩니다.

CRUD작업을 할 경우 항상 따라다니게 됩니다.

CRUD는 생성(INSERT), 읽기(SELECT), 갱신(UPDATE), 삭제(DELETE)를 의미합니다. 테이블을 생성하고, 추가하고, 조회하고, 업데이트하고 삭제할때 항상 위 두줄의 코드는 항상 함께합니다.

import sqlite3

#DB 파일 생성 및 연결
conn = sqlite3.connect("C:\python\database\customer_database.db", isolation_level=None)

# 커서 연결
cursor = conn.cursor()

연동작업이 끝났습니다. 이제 테이블 생성을 시도합니다.

모든 쿼리문은 CURSOR의 execute()함수를 사용하여 실행됩니다.

sqlite 테이블 생성(CREATE TABLE)

테이블 생성 구문은 CREATE문를 사용하여 생성합니다.

import sqlite3

#DB 파일 생성 및 연결
conn = sqlite3.connect("C:\python\database\customer_database.db", isolation_level=None)

# 커서 연결
cursor = conn.cursor()

# 테이블 생성
target_tb = "CREATE TABLE IF NOT EXISTS TB_USER_INFO(" \
            "userid INTEGER PRIMARY KEY, \
          username text, " \
            "email text, " \
            "phone text, " \
            "area text, " \
            "regdate text)"

# SQL 실행
cursor.execute(target_tb)

# DB연결 해제
conn.close()

sqlite 데이터 삽입(INSERT)

INSERT INTO 문을 사용하여 테이블에 데이터를 추가할 수 있습니다.

여러개의 데이터를 추가할 경우 executemany()함수를 사용하여 처리하고, 데이터 타입은 튜플이나 리스트 자료형만 가능합니다.

import sqlite3
import datetime


#DB 파일 생성 및 연결
conn = sqlite3.connect("C:\python\database\customer_database.db", isolation_level=None)

# 커서 연결
cursor = conn.cursor()

current_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')


# 데이터 추가
cursor.execute("INSERT INTO TB_USER_INFO 
VALUES(1, 'IU', 'iu_test@gmail.com', '010-0000-0000', '서울', ?)", (current_time,))

cursor.execute("INSERT INTO TB_USER_INFO(userid, username, email, phone, area, regdate) 
VALUES (?, ?, ?, ?, ?, ?)", (2, 'SUJI', 'suji_test@gmail.com', '010-1234-5678', '광주', current_time))



# 여러개의 데이터를 추가하기(튜플, 리스트)
u_list = (
    (3, 'Hong Gildong', 'Gildong@gmail.com', '010-0000-1234', '서울', current_time),
    (4, 'Han Jimin', 'Jimin@gmail.com', '010-1234-8765', '서울', current_time)
)

cursor.executemany("INSERT INTO TB_USER_INFO(userid, username, email, phone, area, regdate) 
VALUES (?, ?, ?, ?, ?, ?)", u_list)

# DB연결 해제
conn.close()

sqlite 데이터 조회(SELECT)

테이블에 입력한 데이터를 조회하기 위해 SELECT문을 사용합니다.

조회한 결과를 보기 위해서는 fetchone(), fetchall(), fetchmany() 등 3개의 함수를 사용하여 조회결과를 확인할 수 있습니다.

메서드	내용
fetchone()	조회 결과에서 1개의 row를 가져옴(Fetches one row from the resultset.)
fetchmany(size=2)	조회 결과에서 지정한 size만큼의 여러 row를 가져옴 (Fetches several rows from the resultset.)
fetchall()	조회 결과에서 모든 행을 가져옴 (Fetches all rows from the resultset.)

import sqlite3

#DB 파일 생성 및 연결
conn = sqlite3.connect("C:\python\database\customer_database.db", isolation_level=None)

# 커서 연결
cursor = conn.cursor()
 
#테이블 조회 fetchone, fetchmany, fecthall
cursor.execute("SELECT * FROM TB_USER_INFO ORDER BY userid DESC")
print(cursor.fetchmany(size=1))

print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchall())


# DB연결 해제
conn.close()

다음은 for문을 사용하여 조회 결과 출력하는 예제입니다.

import sqlite3

#DB 파일 생성 및 연결
conn = sqlite3.connect("C:\python\database\customer_database.db", isolation_level=None)

# 커서 연결
cursor = conn.cursor()
 
#테이블 조회 fetchone, fetchmany, fecthall
cursor.execute("SELECT * FROM TB_USER_INFO ORDER BY userid DESC")

rows = cursor.fetchall()
for item in rows:
    print(f'{item}')


# DB연결 해제
conn.close()

다음은 where절을 사용하여 조회하는 예제입니다.

import sqlite3

#DB 파일 생성 및 연결
conn = sqlite3.connect("C:\python\database\customer_database.db", isolation_level=None)

# 커서 연결
cursor = conn.cursor()
 
#테이블 조회 fetchone, fetchmany, fecthall
cursor.execute("SELECT * FROM TB_USER_INFO ORDER BY userid DESC")


params = (2,)
cursor.execute("SELECT * FROM TB_USER_INFO WHERE userid=?", params)
print(f'{cursor.fetchall()}')

params = (1, 3)
cursor.execute("SELECT * FROM TB_USER_INFO WHERE userid IN (?,?)", params)
print(f'{cursor.fetchall()}')

# DB연결 해제
conn.close()

sqlite 데이터 삭제(DELETE)

DELETE FROM 문을 사용하여 테이블의 데이터를 삭제할 수 있습니다.

SQLite는 테이블 전체 데이터 삭제 기능인 TRUNCATE를 지원하지 않습니다.

DELETE FROM 테이블명과 같은 형식으로 모든 데이터를 삭제합니다.

모든 데이터의 삭제를 방지하려면 WHERE절을 사용하여 처리합니다.

import sqlite3

#DB 파일 생성 및 연결
conn = sqlite3.connect("C:\python\database\customer_database.db", isolation_level=None)

# 커서 연결
cursor = conn.cursor()

# 데이터 삭제
cursor.execute("DELETE FROM TB_USER_INFO WHERE userid = ?", (2,))

# DB연결 해제
conn.close()

다음은 모든 데이터 삭제하는 쿼리 예제 입니다.

cursor.execute("DELETE FROM TB_USER_INFO")

sqlite 데이터 갱신(UPDATE)

UPDATE문을 사용하여 테이블의 데이터를 갱신할 수 있습니다.

import sqlite3

#DB 파일 생성 및 연결
conn = sqlite3.connect("C:\python\database\customer_database.db", isolation_level=None)

# 커서 연결
cursor = conn.cursor()

# 데이터 수정
cursor.execute("UPDATE TB_USER_INFO SET area= ? WHERE userid= ?", ('전주', 2))

# DB연결 해제
conn.close()

SQLite 연동 해제

데이터베이스에서 작업을 완료한 경우 반드시 close()함수를 호출하여 연결을 해제해야합니다.

# DB연결 해제
conn.close()

동일한 기본키(PK)로 추가한 데이터를 또 등록하면 안됩니다. 기본키는 반드시 유니크해야합니다.

import sqlite3
import datetime


#DB 파일 생성 및 연결
conn = sqlite3.connect("C:\python\database\customer_database.db", isolation_level=None)

# 커서 연결
cursor = conn.cursor()

# 테이블에 데이터 삽입
current_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
cursor.execute("INSERT INTO TB_USER_INFO "
               "VALUES(1, 'IU', 'iu_test@gmail.com', '010-0000-0000', '서울', ?)", (current_time,))
               
cursor.execute("INSERT INTO TB_USER_INFO "
               "VALUES(1, 'IU', 'iu_test@gmail.com', '010-0000-0000', '서울', ?)", (current_time,))               

print(cursor.fetchall())

# DB연결 해제
conn.close()

테이블의 키값으로 이미 등록된 키로 또 다른 데이터를 insert를 시도하는 경우 , sqlite3.IntegrityError: UNIQUE constrainty failed: 테이블명.칼럼명 오류가 발생합니다.

테이블 생성시 테이블 칼럼(필드)의 속성으로 PRIMARY KEY 를 지정한 칼럼은 INSERT시 항상 단일키(유니크) 해야하는 것이죠.

사람들의 주민등록번호를 생각하면 이해가 빠를 것으로 생각됩니다.

C:\Users\ilike\AppData\Local\Programs\Python\Python39\python.exe C:/python/Workspace/main.py
Traceback (most recent call last):
  File "C:\python\Workspace\main.py", line 13, in <module>
    cursor.execute("INSERT INTO TB_USER_INFO "
sqlite3.IntegrityError: UNIQUE constraint failed: TB_USER_INFO.userid

데이터베이스 락이 걸렸을 때 sqlite3.OperationalError: database is locked 오류가 발생됩니다.

이는 다른 프로그램이나 또 다른 창에서 이미 데이터베이스에 접근하여 insert문이나 update문 혹은 delete문을 실행함으로 써 다른 사용자의 데이터베이스 작업을 못하도록 막는 장치 중에 하나입니다.

먼저 선점한 사용자의 작업이 완료되어야 접근할 수 있습니다.

conn.commit()를 치거나 conn.rollback()를 실행하여야 락이 해제됩니다.

Traceback (most recent call last):
  File "C:\python\Workspace\main.py", line 23, in <module>
    cursor.execute("DELETE FROM TB_USER_INFO")
sqlite3.OperationalError: database is locked

테이블이 이미 존재하는 경우에 동일한 테이블을 생성하는 경우 sqlite3.OperationalError: table TB_USER_INFO already exists 오류를 만나게 됩니다. 그럼으로 테이블 생성시 IF NOT EXISTS 조건을 두어 생성해야 합니다.

# 테이블 생성
target_tb = "CREATE TABLE IF NOT EXISTS TB_USER_INFO(" \
            "userid INTEGER PRIMARY KEY, \
          username text, " \
            "email text, " \
            "phone text, " \
            "area text, " \
            "regdate text)"

sqlite 테이블 삭제하기

테이블을 삭제할 때는 DROP TABLE 문을 사용합니다.

import sqlite3
import datetime

#DB 파일 생성 및 연결
conn = sqlite3.connect("C:\python\database\customer_database.db", isolation_level=None)

# 커서 연결
cursor = conn.cursor()

# 테이블 삭제
conn.execute("DROP TABLE TB_USER_INFO")

# DB연결 해제
conn.close()

sqlite 데이터베이스(DB) 데이터 백업하는 방법

1. db파일을 항상 백업합니다.

원본 파일 : customer_database.db
백업 파일 : customer_database_backup.2020.26.db

2. DB 데이터를 덤프(dump) 떠서 스크립트로 보관할 수 있습니다. 아래 스크립트를 실행하면 스크립트를 얻을 수 있어요.

import sqlite3

# DB 파일 생성 및 연결
conn = sqlite3.connect("C:\python\database\customer_database.db", isolation_level=None)

# 커서 연결
cursor = conn.cursor()

with conn:
    with open("C:\python\database\dump_script.sql", 'w') as f:
        for line in conn.iterdump():
            f.write('%s\n' % line)


conn.close()

dump_script.sql 파일을 메모장으로 열어보면 위에서 작업했던 테이블 생성문과 insert문으로 추가했던 스크립트가 그대로 생성됩니다.

나중에 DB이관 작업을 할 때 유용하게 사용될 수 있습니다.

BEGIN TRANSACTION;
CREATE TABLE "TB_LOGIN_HIST" (
	"SEQ"	INTEGER NOT NULL,
	"LOGIN_ID"	TEXT NOT NULL,
	"LOGIN_DATE"	TEXT NOT NULL,
	PRIMARY KEY("SEQ" AUTOINCREMENT)
);
CREATE TABLE TB_USER_INFO(userid INTEGER PRIMARY KEY,           username text, email text, phone text, area text, regdate text);
INSERT INTO "TB_USER_INFO" VALUES(1,'IU','iu_test@gmail.com','010-0000-0000','서울','2020-12-26 18:41:36');
INSERT INTO "TB_USER_INFO" VALUES(2,'SUJI','suji_test@gmail.com','010-1234-5678','전주','2020-12-26 18:41:36');
INSERT INTO "TB_USER_INFO" VALUES(3,'Hong Gildong','Gildong@gmail.com','010-0000-1234','서울','2020-12-26 18:41:36');
INSERT INTO "TB_USER_INFO" VALUES(4,'Han Jimin','Jimin@gmail.com','010-1234-8765','서울','2020-12-26 18:41:36');
DELETE FROM "sqlite_sequence";
COMMIT;

sqlite3 데이터베이스 조회 결과를 pandas DataFrame 타입으로 가져오는 방법

pandas.read_sql_query()함수를 사용하여 조회결과 전체를 DataFrame타입으로 변환할 수 있습니다.

import sqlite3
import pandas as pd

conn = sqlite3.connect("C:\python\database\customer_database.db", isolation_level=None)
df = pd.read_sql_query("SELECT * FROM TB_USER_INFO", conn)
print(df)



#실행결과
   userid      username  ... area              regdate
0       1            IU  ...   서울  2020-12-26 18:41:36
1       2          SUJI  ...   전주  2020-12-26 18:41:36
2       3  Hong Gildong  ...   서울  2020-12-26 18:41:36
3       4     Han Jimin  ...   서울  2020-12-26 18:41:36

[4 rows x 6 columns]