[4주차 총정리] Python 기반 SQL 프로그래밍(2) _SQL 입문자를 위한 기초실습

SQL을 쓰는 이유

- 데이터가 매우 커졌을 때는 csv/excel 보다는 DB 사용하는 것이 더 용량, 속도, 동시접속 측면에서 더 좋음

- 수십만건을 넘어 수백만건 이상이면 DB 쓰는 것이 낫다.

 

---

(실습은 모두 SQLite3으로 진행될 것이며 기초적인 함수들만 구현해볼 것이며, 이는 다른 SQL을 쓴다 하더라도 모두 가지고 있는 기능이므로, 읽어보는 것이 도움이 될 것입니다.)

 

0. 라이브러리 설치

# 라이브러리 설치
import sqlite3 # built-in library (Python 2.x & 3.x)

print(sqlite3.sqlite_version) # sqlite version (220407 기준 3.35.4)
print(sqlite3.version) # pysqlite version (20220407 기준 2.6.0)

 

---

1. Database 파일 만들고, 연결하기

# 1. 만드려는 Database 파일의 이름을 지정합니다.
dbpath = "maindb.db" # or "maindb.sqlite"

# 2. Database 파일에 '연결'합니다.
conn = sqlite3.connect(dbpath) 

# 3. 연결한 DB에 마우스 커서 올리기
cur = conn.cursor()

 

conn 기초 필수 함수
1) conn.cursor() <- 수정, 추가, 삭제 등등을 진행하기 위해 필수적
2) conn.commit/rollback() <- 저장 / 마지막 commit(저장) 이후의 변경사항 모두 취소
3) conn.close()

 

cur 기초 필수 명령 (크게 다섯 개)

1) 실행
- cur.execute     (단일 SQL문)
- cur.executescript  (여러 SQL문이 담긴 Script)
- cur.executemany()  (동일한 구조의 단일 SQL문, 데이터 묶음)

2) 행 꺼내기
- cur.fetchone / .fetchall()   (더 편리한 대체제: pd.read_sql_squery(~~~, conn))

cur.execute("SELECT * FROM tracks;") # "*" means "all columns"
result = cur.fetchall()
print(result[0])
# >(1, 'For Those About To Rock (We Salute You)', 1, 1, 1, 'Angus Young, Malcolm Young, Brian Johnson', 343719, 11170334, 0.99)

---

2. Script에 테이블 생성 및 데이터 삽입하기

# 따옴표 세 개로 문자열 여러 줄로 만들기
script = """
-- 1) employees 테이블이 이미 있다면 제거
DROP TABLE IF EXISTS employees;

-- 2) employees 테이블 생성 
CREATE TABLE employees( 
-- 열이름 / 데이터타입 / attribute
id INTEGER PRIMARY KEY AUTOINCREMENT, -- AUTOINCREMENT: 값을 따로 입력하지 않으면 자동 증가 숫자 부여
name TEXT NOT NULL, --제약조건(PRIMARY KEY, NOT NULL, ...), 빈 값이 저장되는 것을 허용하지 않음
salary REAL, --소수점이 들어간 자료형
department TEXT,
position TEXT,
hireDate TEXT);

-- 3) employees 테이블에 데이터(row == record == instance) 넣기
-- Format) INSERT INTO 테이블명(필드명, 필드명, ...) VALUES(실제값, 실제값, ...) 
-- 지정한 필드의 수와 넣어주는 값의 갯수가 동일해야 함
INSERT INTO employees(name, salary, department, position, hireDate) VALUES('Dave', 300, 'Marketing', 'LV1', '2020-01-01');
INSERT INTO employees(name, salary, department, position, hireDate) VALUES('Clara', 420, 'Sales', 'LV2', '2018-01-11');
INSERT INTO employees(id, name, salary, department, position, hireDate) VALUES(3, 'Jane', 620, 'Developer', 'LV4', '2015-11-01');
-- 전체 필드에 빠짐없이 값을 넣을 시 필드명 생략 가능 
INSERT INTO employees VALUES(4, 'Peter', 530, 'Developer', 'LV2', '2020-11-01'); 
"""

 

Datatypes

- NULL

- INT

- REAL

- TEXT (or VARCHAR)

- BLOB : stands for a binary large object that can store any kind of data. (모든 것 담아낼 수 있음)

 

---

3. Script 실행하고 저장하기

'''executescript'''
# 여러개의 SQL 명령어를 한 장의 "Script"처럼 한번에 실행합니다.
cur.executescript(script) 
conn.commit() 


'''executemany'''
data = [('Elena', 510, 'Recruiter', 'LV3', '2020-07-01'), 
        ('Sujan', 710, 'HR', 'LV5', '2014-06-01'),
        ('Jake', 210, 'CEO', 'LV8', '2012-01-01')]
# 많은("many") 데이터를 한번에 INSERT/UPDATE/DELETE 합니다. 
cur.executemany("INSERT INTO employees(name, salary, department, position, hireDate) VALUES(?, ?, ?, ?, ?);", data)
conn.commit()


'''execute'''
cur.execute("SELECT * FROM employees;") # 단 하나의 SQL 문을 실행("execute")합니다.
employee_list = cur.fetchall() # fetchall or fetchone
employee_list
# for employee in employee_list:
#     print(employee)

 

Checking column names with fetched data (열 이름을 얻어내고 싶을 때)

print([col[0] for col in cur.description])
# >['id', 'name', 'salary', 'department', 'position', 'hireDate']

 

Closing connection of SQLite3

conn.close() 
# connect -> commit/rollback -> close

---

4. SQL Query로 데이터 불러오기

import pandas as pd

# 현재 연결(connect)되어있는 DB로부터 "SQL Query"를 활용해 데이터를 읽어온다
df = pd.read_sql_query("SELECT * FROM employees;", conn) 
df