파이썬으로 DB 다루기

파이썬으로 DB 다루기¶

---

Python DB API¶

• mkdir -p ~/aiffel/sql_to_db/sqlite

In [1]:

import sqlite3
print("뿅💛")

뿅💛

In [2]:

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

<sqlite3.Connection object at 0x7f7d184a3490>

In [3]:

# 결과를 조회하는 SELECT에서는 거의 필수 cursor
c = conn.cursor()
print(c)

<sqlite3.Cursor object at 0x7f7d184832d0>

In [4]:

# stocks이라는 이름의 테이블을 하나 생성합니다. 혹시 이미 생성되었다면 생략합니다. 
c.execute("CREATE TABLE  IF NOT EXISTS  stocks (date text, trans text, symbol text, qty real, price real)")

# stocks 테이블에 데이터를 하나 인서트합니다. 
c.execute("INSERT INTO stocks VALUES ('20200701', 'TEST', 'AIFFEL', 1, 10000)")

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

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

('20200701', 'TEST', 'AIFFEL', 1.0, 10000.0)

sqlite DB Browser¶

• sudo add-apt-repository -y ppa:linuxgndu/sqlitebrowser
• sudo apt-get update
• sudo apt-get install sqlitebrowser

In [5]:

conn.commit()   # commit()은 cursor의 메소드가 아니라 connection의 메소드입니다. 
print("뿅💛")

뿅💛

In [6]:

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

뿅💛

DDL문으로 테이블 생성하기¶

In [7]:

import sqlite3
import os
db_path = os.getenv('HOME')+'/aiffel/sql_to_db/sqlite/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("뿅💛")

뿅💛

In [8]:

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

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

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

쿼리의 기본 구조¶

• SELECT ~ : 조회할 컬럼명을 선택
• FROM ~ : 조회할 테이블명을 지정 (위치와 테이블명을 입력)
• WHERE ~ : 질의할 때 필요한 조건을 설정
• GROUP BY ~ : 특정 컬럼을 기준으로 그룹을 지어 출력
• ORDER BY ~ : SELECT 다음에 오는 컬럼 중 정렬이 필요한 부분을 정렬 (기본 설정 : 오름차순)
• LIMIT 숫자 : Display하고자 하는 행의 수를 설정
• DISTICT 컬럼명 : 중복 제거
• ORDER BY 컬럼 : 차순으로 정렬(기본 asc 오름차순)

In [9]:

#- c.execute() 괄호 안에 SQL문을 넣으시면 됩니다.

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

for row in c.execute('SELECT * FROM 도서대출내역'):
  print(row)

#- ! 출력 시 'NULL' 대신 'None'으로 출력될 수 있으나 동일하게 이해하시면 됩니다.

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

In [10]:

# pragma table_info로 데이터타입 확인 
import os
db_path = os.getenv('HOME')+'/aiffel/sql_to_db/sqlite/mydb.db'
conn = sqlite3.connect(db_path)  # mydb.db에 연결합니다.
c = conn.cursor()

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

(0, 'ID', 'varchar', 0, None, 0)
(1, '이름', 'varchar', 0, None, 0)
(2, '도서ID', 'varchar', 0, None, 0)
(3, '대출일', 'varchar', 0, None, 0)
(4, '반납일', 'varchar', 0, None, 0)

- substr을 통해 일을 뺀 나머지를 추출¶

SELECT *, SUBSTR(대출일수, 1, (length(대출일수)-1)) AS 대출일수_수정 FROM 도서대출내역2;

- 추출한 일수를 int값으로 데이터타입 변경¶

SELECT *, CAST(SUBSTR(대출일수, 1, (length(대출일수)-1)) AS INT) AS 대출일수_수정 FROM 도서대출내역2 ;

- 다양한 조건을 줄 수 있음¶

SELECT * FROM 도서대출내역2 WHERE 조건1 AND 조건2 AND 조건3 AND (조건 4 OR 조건5);

1. 특정 문자를 포함하는 row를 가져오고 싶을 때¶

SELECT * FROM 도서대출내역2 WHERE 이름 LIKE "문%" ;

2. 특정 기간 혹은 특정 날짜의 이전 또는 이후의 row를 가져오고 싶을 때¶

SELECT * FROM 도서대출내역 WHERE 대출일 >= "2020-06-01" AND 대출일 <= "2020-06-07" ;

SELECT * FROM 도서대출내역 WHERE 대출일 BETWEEN "2020-06-01" AND "2020-06-07" ;

SELECT * , CAST(SUBSTR(대출일수, 1, (length(대출일수)-1)) AS INT) AS 대출일수_수정 FROM 도서대출내역2 WHERE 대출일수_수정 > 5 ;

3. Null 조건 다루기¶

SELECT * FROM 도서대출내역 WHERE 반납일 IS NOT NULL;

JOIN¶

• INNER JOIN : A 테이블과 B 테이블의 교집합을 조회
• LEFT JOIN : (기준은 A 테이블) A 테이블을 기준으로 해서 B 테이블은 공통되는 부분만 조회
• RIGHT JOIN : (기준은 B 테이블) B 테이블을 기준으로 해서 A 테이블은 공통되는 부분만 조회
• FULL JOIN : A 테이블과 B 테이블 모두에서 빠트리는 부분 없이 모두 조회

JOIN 기본 구문¶

SELECT 컬럼1, 컬럼2, 컬럼3... FROM A테이블 AS A {INNER/LEFT/RIGHT/FULL OUTER} JOIN B테이블 AS B ON A.결합컬럼 = B.결합컬럼 WHERE ~

1) INNER JOIN
SELECT A.*, B.도서명 FROM 대출내역 AS A INNER JOIN 도서명 AS B ON A.도서ID = B.도서ID

2) LEFT JOIN
SELECT A.*, B.도서명 FROM 대출내역 AS A LEFT JOIN 도서명 AS B ON A.도서ID = B.도서ID

3) RIGHT JOIN
SELECT B.*, A.ID, A.이름 FROM 대출내역 AS A RIGHT JOIN 도서명 AS B ON A.도서ID = B.도서ID

4) FULL OUTER JOIN
SELECT A.*, B.도서명 FROM 대출내역 AS A FULL OUTER JOIN 도서명 AS B ON A.도서ID = B.도서ID ORDER BY 도서ID ;

5) 중첩 질의(Nested Query)
SELECT C.이름, COUNT() 대출건수
FROM ( SELECT A., B.도서명 FROM 대출내역 AS A LEFT JOIN 도서명 AS B ON A.도서ID = B.도서ID ) C GROUP BY C.이름

6) 쿼리의 조건절 IFNULL - 값1이 Null이면 값2
SELECT A.*, IFNULL(B.도서명, '도서명미상') AS 도서명 FROM 대출내역 AS A LEFT JOIN 도서명 AS B ON A.도서ID = B.도서ID

7) 쿼리의 조건절 CASE
SELECT 이름, CASE WHEN 대출일수_수정 > 5 THEN '기간초과' ELSE '기간내' END AS 대출기간
FROM (SELECT *, CAST(SUBSTR(대출일수, 1, (length(대출일수)-1)) AS INT) AS 대출일수_수정 FROM 도서대출내역2 )

Q5. '도서명' 테이블과 '도서대출내역' 테이블을 도서ID를 key로 하여 LEFT JOIN해서 |도서ID|도서명|대출건수|대출상태| 항목을 출력하는 쿼리를 작성¶

대출건수 : 도서별로 도서대출내역 테이블에 대출일자가 있으면 대출건수 1로 본다. 여러 번 대출되었으면 대출된 회수만큼 합산된다.

대출상태 : 대출일자는 NOT NULL인데 반납일자가 NULL인 도서대출내역이 있으면 '대출중', 그렇지 않고 모든 대출내역에 반납일자가 명시되어 있으면 '보관중'으로 본다.

SELECT C.도서ID, C.도서명, SUM(C.대출건수) AS 대출건수, CASE SUM(C.대출건수)-SUM(C.반납건수) WHEN 0 THEN '보관중' ELSE '대출중' END AS 대출상태
FROM ( SELECT A.도서ID, A.도서명, CASE WHEN B.대출일 IS NULL THEN 0 ELSE 1 END AS 대출건수, CASE WHEN B.반납일 IS NULL THEN 0 ELSE 1 END AS 반납건수 FROM 도서명 AS A LEFT JOIN 도서대출내역 AS B ON A.도서ID = B.도서ID ) AS C
GROUP BY C.도서ID, C.도서명 ;