SQLite

SQLite?

• 서버 필요없이 DB의 파일에 기초하여 DB 처리를 구현한 임베디드 SQL DB 엔진
• 별도의 설치없이 사용할 수 있음
• Mac OS 나 Linux에 기본적으로 설치되어 있음
• 파이썬은 버전 2.5이상일 경우 모듈이 기본 내정 되어 있음

1. 파이썬과 DB를 연결

import os
db_path = os.getenv('HOME')+'/mydb.db'
conn = sqlite3.connect(db_path) #mydb.db에 연결

• DB 이름을 정하여 입력
• 위 코드의 경우 mydb.db 형태로 저장

2. Cursor 객체

c = conn.cursor()

• conn 객체는 SQL 연결과 관련된 셋팅이 포함되어 있음
• Cursor 객체는 Connect()함수의 연결을 사용
• Cursor 객체
  • SQL 질의(Query)를 수행하고 결괄르 얻는데 사용하는 객체
  • Insert처럼 DB에만 적용되는 명령어를 사용하는 것이 아니라 Select 같이 데이터를 불러올 때는 SQL 질의 수행 결과에 접근하기 위한 Cursor가 반드시 필요
  • conn.coursor() 사용을 습관화 하자

3. SQL문 실행

# stocks이라는 이름의 테이블을 하나 생성
c.execute("CREATE TABLE  IF NOT EXISTS  stocks (date text, trans text, symbol text, qty real, price real)")

# stocks 테이블에 데이터를 하나 insert
c.execute("INSERT INTO stocks VALUES ('20200701', 'TEST', 'AIFFEL', 1, 10000)")

# 방금 insert한 데이터를 조회해 봅니다.
c.execute("SELECT * FROM stocks")

# 조회된 내역을 커서를 통해 가져와 출력해 봅니다.
print(c.fetchone())

4. conn.commit()

• SQL질의가 끝났다면 conn.commit() 를 호출해서 DB가 실제로 update

conn.commit()   # commit()은 cursor의 메소드가 아니라 connection의 메소드

• commit()을 통해 데이터베이스에 데이터 변경이 반영됨
  • 이런 것을 데이터베이스에서는 트랜잭션(transation) 관리 라고 함
  • 트랜재션이란?
    • 쪼개지면 안되는 업무 단위
    • ex) 온라인 쇼핑에서의 결제완료와 상품배송처리가 쪼개져서는 안됨

5. DB와 연결 끊기

c.close()      # 먼저 커서를 닫은 후
conn.close()    # DB 연결을 닫아 줍니다.

• commit()을 완료했다면 DB와 연결 끊기

6. DDL 문으로 테이블 생성

import sqlite3
import os
db_path = os.getenv('HOME')+'/mydb.db'

conn = sqlite3.connect(db_path)
c = conn.cursor()

#- ! 재실행 시 테이블이 존재할 수 있으므로 아래처럼 해당 테이블들을 모두 지워줍니다.
c.execute("DROP TABLE IF EXISTS 도서대출내역")
c.execute("DROP TABLE IF EXISTS 도서대출내역2")
c.execute("DROP TABLE IF EXISTS 대출내역")
c.execute("DROP TABLE IF EXISTS 도서명")

#----- 1st table : 도서대출내역 -----#
c.execute("CREATE TABLE IF NOT EXISTS 도서대출내역 (ID varchar, 이름 varchar, 도서ID varchar, 대출일 varchar, 반납일 varchar)")
#- 생성(create)문 : 테이블명, 변수명, 변수타입을 지정

data = [('101','문강태','aaa','2020-06-01','2020-06-05'),
             ('101','문강태','ccc','2020-06-20','2020-06-25'),
             ('102','고문영','bbb','2020-06-01',None),
             ('102','고문영','ddd','2020-06-08',None),
             ('103','문상태','ccc','2020-06-01','2020-06-05'),
             ('104','강기둥',None,None,None)]
#- 입력할 데이터를 그대로 입력 (변수명 순서 기준대로)

c.executemany('INSERT INTO 도서대출내역 VALUES (?,?,?,?,?)', data)
#- 입력할 데이터를 실제 테이블에 insert하기
#-----------------------------------------------#

#----- 2nd table : 도서대출내역2 -----#
c.execute("CREATE TABLE IF NOT EXISTS 도서대출내역2 (ID varchar, 이름 varchar, 대출년월 varchar, 대출일수 varchar)")

data = [('101','문강태','2020-06','20일'),
             ('102','고문영','2020-06','10일'),
             ('103','문상태','2020-06','8일'),
             ('104','강기둥','2020-06','3일')]
c.executemany('INSERT INTO 도서대출내역2 VALUES (?,?,?,?)', data)
#--------------------------------------------------#

#----- 3rd table : 대출내역 -----#
c.execute("CREATE TABLE IF NOT EXISTS 대출내역 (ID varchar, 이름 varchar, 도서ID varchar)")

data = [('101','문강태','aaa'),
             ('102','고문영','bbb'),
             ('102','고문영','fff'),
             ('103','문상태','ccc'),
             ('104','강기둥',None)]
c.executemany('INSERT INTO 대출내역 VALUES (?,?,?)', data)
#-----------------------------------------#

#----- 4th table : 도서명 -----#
c.execute("CREATE TABLE IF NOT EXISTS 도서명 (도서ID varchar, 도서명 varchar)")

data = [('aaa','악몽을 먹고 자란 소년'),
             ('bbb','좀비아이'),
             ('ccc','공룡백과사전'),
             ('ddd','빨간구두'),
             ('eee','잠자는 숲속의 미녀')]

c.executemany('INSERT INTO 도서명 VALUES (?,?)', data)
#--------------------------------------#

conn.commit()
conn.close()
print("뿅💛")

c.execute("CREATE TABLE IF NOT EXISTS 도서명 (도서ID varchar, 도서명 varchar)")

• 도서명에서 CREATE TABLE IF NOT EXISTS 도서명 (도서ID varchar, 도서명 varchar) 은 만약 테이블이 존재하지 않는다면 테이블을 생성, 그 때 변수명과 각 변수 타입을 지정

data = [('aaa','악몽을 먹고 자란 소년'),
             ('bbb','좀비아이'),
             ('ccc','공룡백과사전'),
             ('ddd','빨간구두'),
             ('eee','잠자는 숲속의 미녀')]

• 각 변수명에 맞게 데이터를 실제로 생성

c.executemany('INSERT INTO 도서명 VALUES (?,?)', data)

• INSERT INTO 도서명 VALUES (?,?) 각 테이블 변수에 입력한 데이터들을 넣겠다는 뜻
• ? 를 이용해서 데이터를 쿼리에 바인딩
• c.executemany() 메소드 : 한꺼번에 여러개의 데이터 처리 가능
• 데이터 조회

conn = sqlite3.connect(db_path)
c = conn.cursor()

for row in c.execute('SELECT * FROM 도서명'):
  print(row)

#결과
('aaa','악몽을 먹고 자란 소년'),
('bbb','좀비아이'),
('ccc','공룡백과사전'),
('ddd','빨간구두'),
('eee','잠자는 숲속의 미녀')

7. 데이터 타입 정의 확인 : pragma table_info('테이블명')

import os
db_path = os.getenv('HOME')+'/mydb.db'
conn = sqlite3.connect(db_path)  # mydb.db에 연결합니다.
c = conn.cursor()

for row in c.execute('pragma table_info(도서대출내역)'):
  print(row)