sqlite3 모듈을 통해 sqlite3.db 파일 관리하기

파이썬으로 sqlite3 DB을 관리하는 방법을 알아보기 전 간단하게 데이터베이스와 그 개념에 대해 살펴보고, 그 지식을 활용하는 형식으로 sqlite3을 이용해 보자. 글의 순서는 데이터베이스가 무엇인지에 대해 살펴보고, sqlite SQL 쿼리문 작성, python에서 sqlite3.db관리 순으로 정리한다.

 

 

데이터베이스

데이터베이스 (DB) 정의

데이터베이스란 구조화된 정보 또는 데이터의 조직화된 모음으로 일반적으로 컴퓨터의 시스템에 전자적으로 저장되는 데이터의 모음을 의미한다. 일반적으로 DBMS(Data Base Management System)에 의해 관리되고 처리된다. 

 

주로 삽입(Create), 조회(Read), 수정(Update), 삭제(Delete)를 통해 데이터가 수정되고 처리된다. 

📢 줄여서 CRUD라고도 한다.

 

 

 

 

데이터베이스 구성요소

 

데이터 베이스는 여러 테이블의 집합으로 이루어져 있으며 이를 구성하는 테이블은 행(row)의 집합으로 구성되어 있다. 

행은 한 단위의 데이터 기록을 의미하며 여기서 열은 데이터의 항목(field)을 의미한다. 

 

 

 

 

 

SQL (Structured Query Language)

SQL이란 데이터 베이스를 관리하기 위해 사용되는 언어를 의미한다. 주로 아래의 2가지 종류로 구분한다. 

• DDL(Data Definition Language) : 데이터 정의 언어 
• DML(Data Manipulation Language) : 데이터 조작 언어

 

 

 

 

Sqlite3

sqlite3

SQLite는 MySQL이나 PostgreSQL와 같은 데이터베이스 관리 시스템이지만, 서버가 아니라 응용 프로그램에 넣어 사용하는 비교적 가벼운 데이터베이스 중 하나이다.

 

 

 

 

SQLite 데이터 타입

SQLite는 아래와 같은 데이터 타입을 가진다.

데이터 타입	설명
integer	정수
real	실수
text	문자열
null	null값

 

 

 

SQLite 테이블 생성 명령

SQLite에서 테이블을 생성할 때에는 아래와 같은 명령어를 이용하여 테이블을 생성할 수 있다. 

CREATE TABLE 테이블명 (컬럼명1 데이터타입, 컬럼명2 데이터타입);

# 예시
CREATE TABLE person(
    id integer primary key autoincrement,
    phone_number integer not null unique,
    title text not null default "제목 없음", 
    content text not null default "내용 없음
)

 

• primary key : 주요키, 기본키라고도 하며 레코드(행)를 구분 짓는 값으로 사용한다. 
• null : 빈 값을 의미하며 not null은 값을 채워야 한다는 의미를 지니고 있다. 
• default : 기본 값을 의미한다. 
• unique : 유일 값을 의미한다. 만약에 값이 중복되어 들어온다면 오류를 발생시킨다. 
• autoincrement : 자동 증가를 의미하며, 데이터가 추가될수록 값이 증가한다.

 

SQLite 테이블 제거 명령

DROP TABLE 테이블명;

 

 

SQLite 테이블 수정 명령

테이블 명 수정하기

ALTER TABLE 테이블명 RENAME TO 새로운 테이블명;

 

칼럼 수정하기

ALTER TABLE 테이블명 ADD COLUMN 컬럼명;
ALTER TABLE 테이블명 DELETE COLUMN 컬럼명;
ALTER TABLE 테이블명 RENAME 컬럼명1 TO 컬럼명2;

 

 

 

SQLite 데이터 조작 명령

조작명령에는 SQL insert, select, update, delete 가 있다. 

 

데이터(행) 추가 명령

INSERT INTO 테이블명 (컬럼명1, 컬럼명2) VALUES (값1, 값2);

 

데이터 조회 명령

SELECT 컬럼명1, 컬럼명2 FROM 테이블명;
SELECT 컬럼명1, 컬럼명2 FORM 테이블명 WHERE 조건;
SELECT * FORM 테이블명 ORDER BY 컬럼명 ASC 혹은 DESC;

 

• WHERE : 조건을 걸어 조회를 할 수 있다. 
• ORDER BY : 정렬하여 조회를 할 수 있다. 

 

데이터 수정 명령

UPDATE 테이블명 SET 컬럼명 = 값, ... WHERE 조건식;

• 만약 WHERE 조건식 없이 UPDATE을 할 경우 모든 레코드에 대한 값이 바뀌게 되니 WHERE 조건식 쓰는 것을 꼭 하길 바람

 

데이터 삭제 명령

DELETE FROM 테이블명 WHERE 조건식;

 

 

 

 

 

GROUP BY와 JOIN

 

GROUP BY

그룹화하여 계산하는 명령을 의미한다. 

SELECT count(*) FROM 테이블명 GROUP BY 컬럼명;

# 예시
SELECT gender, count(*) FROM user GROUP BY gender;
SELECT gender, avg(*) FROM user WHERE age > 20 GROUP BY gender;

• count : 칼럼별로 몇 개가 존재하는지를 나타내 준다.
• avg : 평균을 의미한다. 

 

 

 

JOIN

2개의 다른 테이블을 조합하여 결과를 만들어 내고 싶을 때 사용합니다. 

* FROM 테이블1 INNER JOIN 테이블2 WHERE 조건;
* FROM 테이블1, 테이블2 WHERE 조건;

 

 

 

 

 

 

 

파이썬에서 Sqlite3 다루기

파이썬에서 sqlite3 DB을 다루기 위해서는 다음과 같은 과정이 필요하다. 

• Database 파일 열기
• 커서 생성 (사용자로부터 SQL문을 받아 DBMS에 전달하고 그 결괏값을 가져와 주는 역할)
• SQL 명령 실행
• 커밋 또는 롤백
• 데이터 베이스 닫기

 

 

🖥️ Table 생성

# 모듈 추가
import sqlite3

# DB 연결
conn = sqlite3.connect('.db')

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

# SQL문 작성
CREATE_SQL = """
    CREATE TABLE IF NOT EXISTS Item(
        it integer primary key autoincrement,
        code text not null,
        name text not null,
        price integer not null
    )
"""

# SQL 명령 실행
cur.execute(CREATE_SQL)


# DB 닫기
conn.close()

 

 

 

🖥️ Table에 데이터 추가

# 모듈 추가
import sqlite3

# DB 연결
conn = sqlite3.connect('test.db')

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

# SQL문 작성
INSERT_SQL = """
    INSERT INTO item(code, name, price) VALUES (?, ?, ?)
"""

# SQL 명령 실행
cur.execute(INSERT_SQL, ("hello world", "jheaon", "100"))

# 커밋 : INSERT, UPDATE, DELETE는 commit을 해야 실제 데이터베이스에 반영된다.
conn.commit()

# DB 닫기
conn.close()

 

만약에 데이터를 추가할 때 여러 개의 값을 한 번에 추가하고 싶다면 cur.executemany을 이용하여 처리할 수 있다. 

 

 

 

 

🖥️ Table에서 데이터 읽기

# 모듈 추가
import sqlite3

# DB 연결
conn = sqlite3.connect('test.db')

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

# SQL문 작성
SELECT_SQL = """
    SELECT * FROM item 
"""

# SQL 명령 실행
cur.execute(SELECT_SQL)

rows = cur.fetchall()

for row in rows:
    print(row)


# DB 닫기
conn.close()

SELECT문으로 읽은 데이터를 cur.fetchall()을 통해 가져와야지 데이터를 처리할 수 있다. 

 

 

 

 

 

🖥️ Table에서 데이터 수정

# 모듈 추가
import sqlite3

# DB 연결
conn = sqlite3.connect("test.db")

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

# SQL문 작성
UPDATE_SQL = """
    UPDATE item SET code = "A12345" WHERE it = 2
"""

# SQL 명령 실행
cur.execute(UPDATE_SQL)

conn.commit()

# DB 닫기
conn.close()

 

 

 

🖥️ Table 에서 데이터 삭제

# 모듈 추가
import sqlite3

# DB 연결
conn = sqlite3.connect("test.db")

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

# SQL문 작성
DELETE_SQL = """
    DELETE FROM item WHERE code = 'A12345'
"""

# SQL 명령 실행
cur.execute(DELETE_SQL)

conn.commit()

# DB 닫기
conn.close()