[Python] 파이썬 SQLite3 연동 및 기본 사용법 및 pandas 연계 방법

떠돌이개발자Dec 27, 2023

파이썬에서 데이터베이스를 연동하여 사용해봅니다. MySql, Oracle, MS-SQL 등의 데이터베이스는 별도의 설치 및 연동과정이 필요합니다. SQLite3는 별도의 설치 과정 없이 바로 import하여 사용가능합니다. 무엇보다 데이터베이스 서버의 구성이 필요없습니다. 모든 관계형 데이터베이스와 마찬가지로 데이터베이스에 대한 연결 개체를 만들고, 데이터베이스에 테이블을 만들고, 테이블에 레코드를 삽입하고, WHERE절을 기반으로 테이블에서 조회를 하는 방법과 WHERE절을 기반으로 업데이트하는 방법 등에 대해 알아봅니다.

SQLite 연동

데이터베이스를 사용하기 위해 db파일을 제일 먼저 생성하게됩니다. 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:pythondatabasecustomer_database.db", isolation_level=None)

db에 접근하기 위한 다음 작업으로 connection에 Cursor객체를 생성 후 execute()함수를 호출하여 SQL 명령을 실행합니다.

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

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

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

import sqlite3

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

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

연동작업이 끝났습니다. 이제 테이블 생성을 시도합니다. 모든 쿼리문은 CURSOR의 execute()함수를 사용하여 실행됩니다.

테이블 생성(CREATE TABLE)

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

import sqlite3

#DB 파일 생성 및 연결
conn = sqlite3.connect("C:pythondatabasecustomer_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()

데이터 삽입(INSERT)

INSERT INTO 문을 사용하여 테이블에 데이터를 추가할 수 있습니다. 여러개의 데이터를 추가할 경우 executemany()함수를 사용하여 처리합니다. 이때 데이터 타입은 튜플이나 리스트 자료형만 가능합니다.

import sqlite3
import datetime


#DB 파일 생성 및 연결
conn = sqlite3.connect("C:pythondatabasecustomer_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()

데이터 조회(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:pythondatabasecustomer_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:pythondatabasecustomer_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:pythondatabasecustomer_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()

데이터 갱신(UPDATE)

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

import sqlite3

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

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

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

# DB연결 해제
conn.close()

데이터 삭제(DELETE)

DELETE FROM 문을 사용하여 테이블의 데이터를 삭제할 수 있습니다. SQLite는 테이블 전체 데이터 삭제 기능인 TRUNCATE를 지원하지 않습니다. DELETE FROM 테이블명과 같은 형식으로 모든 데이터를 삭제합니다. 모든 데이터의 삭제를 방지하려면 WHERE절을 사용하여 처리합니다.

import sqlite3

#DB 파일 생성 및 연결
conn = sqlite3.connect("C:pythondatabasecustomer_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 연동 해제

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

# DB연결 해제
conn.close()

동일한 기본키로 추가한 데이터를 또 등록하게 되면 어떻게 될까요?

import sqlite3
import datetime


#DB 파일 생성 및 연결
conn = sqlite3.connect("C:pythondatabasecustomer_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:UsersilikeAppDataLocalProgramsPythonPython39python.exe C:/python/Workspace/main.py
Traceback (most recent call last):
  File "C:pythonWorkspacemain.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:pythonWorkspacemain.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)"

테이블 삭제하기

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

import sqlite3
import datetime

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

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

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

# DB연결 해제
conn.close()

DB데이터 백업하기

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

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

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

import sqlite3

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

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

with conn:
    with open("C:pythondatabasedump_script.sql", 'w') as f:
        for line in conn.iterdump():
            f.write('%sn' % 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:pythondatabasecustomer_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]