[머신 러닝] 2-1 & 2 SQLite3 기초(1)

혯응·2024년 9월 4일

머신러닝 여행자들을 위한 차근차근 가이드 TIL

목록 보기

8/23

import sqlite3 # built-in library (Python 2.x & 3.x)

상단에 sqlite3 임포트 해주기

커넥션(Connection)과 커서(Cursor)

1. 커넥션 생성

입력

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

SQLite 데이터베이스에 연결하는 connection 객체 반환함

conn.cursor() # 연결된 DB를 위에서 돌아다니는 심부름꾼 만들어주는 것. 
conn.commit() # 작업물 저장 
conn.rollback() # 마지막 commit 이후의 변경사항 모두 취소 
conn.close() # 커넥션 연결 해제

2. 커서 생성

입력

cur = conn.cursor()

cur.execute('CREATE TABLE ~~~~')  #한문장 짜리 SQL 문 실행
cur.executescript('') #서로 다른 여러개의 문장을 모아둔 SQL 스크립트 문 실행
cur.executemany('SQL 하나',~~~) #하나의 SQL 문 데이터 넣어가면서 여러번 반복

cur.fetchone() #한 행만 읽어옴
cur.fetchall() #모두 읽어옴

SQLite3 실행

1. SQlite3 데이터 타입

데이터 타입	설명
NULL	정보가 없거나 알 수 없음
INTEGER	정수 값 (양수 또는 음수), 1, 2, 3, 4, 8바이트 크기 가능
REAL	실수 값 (소수점 포함), 8바이트 크기 부동 소수점
TEXT	문자열 데이터, 다양한 문자 인코딩 지원 (UTF-8, UTF-16)
BLOB	이진 대형 객체, 모든 종류의 데이터를 저장 가능

이미지 파일 데이터는 AWS와 같은 서버에 이미지를 저장
저장한 이미지 파일 링크를 받아와 TEXT 타입으로 데이터 베이스에 저장하여 관리

2. SQL 문 멀티 라인으로 작성하는 법

입력

sql_query = """
CREATE TABLE employees(
id INTEGER PRIMARY KEY, 
name TEXT NOT NULL, 
salary REAL, 
department TEXT, 
position TEXT, 
hireDate TEXT)
"""

SQL 문 시작/종료 지점에 """ 작성하면 멀티 라인으로 작성할 수 있다.

3. SQL 스크립트 작성하는 법

입력

script = """
-- 1) employees 테이블이 이미 있다면 제거
DROP TABLE IF EXISTS employees;

-- 2) employees 테이블 생성 
CREATE TABLE employees( 
id INTEGER PRIMARY KEY AUTOINCREMENT, --숫자를 저장, 이 테이블의 주요 Key(기본 키)로 지정, 값을 따로 입력하지 않으면 자동 증가 숫자 부여
name TEXT 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'); 
"""

cur.executescript(script) # 여러개의 SQL 명령어를 한 장의 "Script"처럼 한번에 실행합니다.
conn.commit() # connect -> commit/rollback -> close, 실제로 DB에 위 Table & Data를 저장합니다.

주석 작성 시 --- 앞에 붙이기
커서는 executescript 함수를 부른다

4. SQL 문에 데이터를 넣는 경우

입력

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()

커서의 executemany 함수를 사용하여 작성한다

5. 열 이름 확인하는 법

입력

#열 이름 확인
print([col[0] for col in cur.description]) # "Description" of the table
# print(cur.description) # https://j.mp/3f2xc6l

출력

6. 판다스를 활용하여 테이블 불러오기

입력

import pandas as pd

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

# 세미콜론(;)이 빠져도 실행은 되지만, 추후 문법 상 오류가 발생하지 않도록 최대한 세미콜론을 꼭 붙여주도록 하겠습니다.

출력