DB 연결을 위한 전화선인 sqlite3 모듈을 import
import sqlite3
이제 파이썬과 DB를 연결해 보겠습니다. conn에 DB 이름을 정하여 입력합니다. 저는 mydb로 지었는데요. [이름].[확장자명] 형태로 하면됩니다.
import os
db_path = os.getenv('HOME')+'/mydb.db'
conn = sqlite3.connect(db_path) # mydb.db에 연결합니다.
print(conn)
c = conn.cursor()
print(c)
# 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)
삽입, 갱신, 삭제 등의 SQL 질의가 끝났다면 conn.commit()를 호출해야 DB가 실제로 업데이트 됩니다 . commit()을 하기 전에는 DB에 데이터가 업데이트된 것 같아 보여도 임시로만 바뀐 것이니 주의해야 합니다.
conn.commit()
commit()을 통해 데이터베이스에 데이터 변경이 실제적으로 반영되었습니다.
commit()을 완료했다면 DB와 대화하는 것을 마무리 지어야 합니다.
c.close() # 먼저 커서를 닫은 후
conn.close() # DB 연결을 닫아 줍니다.
다양한 테이블을 조회해 보기 앞서, 실제 예제 테이블들을 한번 생성해 보도록 하겠습니다. 테이블명과 컬럼명을 한글로도 지정할 수 있습니다.
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()
conn = sqlite3.connect(db_path)
c = conn.cursor()
for row in c.execute('SELECT * FROM 도서명'):
print(row)
('aaa', '악몽을 먹고 자란 소년')
('bbb', '좀비아이')
('ccc', '공룡백과사전')
('ddd', '빨간구두')
('eee', '잠자는 숲속의 미녀')
데이터베이스(DB)에서 데이터를 조회하고자 할 때 필요한 컴퓨터 언어입니다. 테이블을 삽입하거나 삭제, 갱신, 조회하는 등 본인이 원하는 형태로 데이터를 만들어 가져올 수 있습니다.
#- 참고 : 실제 조회를 해보시려면 아래처럼 쓰시면 됩니다.
#- c.execute() 괄호 안에 SQL문을 넣으시면 됩니다.
import os
db_path = os.getenv('HOME')+'/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)
SELECT * FROM 도서대출내역;
for row in c.execute('SELECT * FROM 도서대출내역'):
print(row)
('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)
SELECT ID FROM 도서대출내역;
for row in c.execute('SELECT ID FROM 도서대출내역'):
print(row)
('101',)
('101',)
('102',)
('102',)
('103',)
('104',)
SELECT * FROM 도서대출내역 WHERE 이름 = "문강태";
for row in c.execute('SELECT * FROM 도서대출내역 WHERE 이름 = "문강태";'):
print(row)
('101', '문강태', 'aaa', '2020-06-01', '2020-06-05')
('101', '문강태', 'ccc', '2020-06-20', '2020-06-25')
SELECT 이름 FROM 도서대출내역 GROUP BY 이름;
for row in c.execute('SELECT 이름 FROM 도서대출내역 GROUP BY 이름;'):
print(row)
('강기둥',)
('고문영',)
('문강태',)
('문상태',)
SELECT DISTINCT 이름 FROM 도서대출내역;
for row in c.execute('SELECT DISTINCT 이름 FROM 도서대출내역;'):
print(row)
('문강태',)
('고문영',)
('문상태',)
('강기둥',)
SELECT * FROM 도서대출내역 ORDER BY ID ;
for row in c.execute('SELECT * FROM 도서대출내역 ORDER BY ID ;'):
print(row)
('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)
반대는 내림차순으로 DESC라고 씁니다.
for row in c.execute('SELECT * FROM 도서대출내역 ORDER BY ID DESC ;'):
print(row)
('104', '강기둥', None, None, None)
('103', '문상태', 'ccc', '2020-06-01', '2020-06-05')
('102', '고문영', 'bbb', '2020-06-01', None)
('102', '고문영', 'ddd', '2020-06-08', None)
('101', '문강태', 'aaa', '2020-06-01', '2020-06-05')
('101', '문강태', 'ccc', '2020-06-20', '2020-06-25')
SELECT * FROM 도서대출내역 LIMIT 5 ;
for row in c.execute('SELECT * FROM 도서대출내역 LIMIT 5 ;'):
print(row)
('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')
for row in c.execute('SELECT * FROM 도서대출내역2;'):
print(row)
('101', '문강태', '2020-06', '20일')
('102', '고문영', '2020-06', '10일')
('103', '문상태', '2020-06', '8일')
('104', '강기둥', '2020-06', '3일')
평균 대출일수를 구하기 위해서는 대출일수가 int로 변경할 필요가 있습니다.
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)
(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)
데이터 타입을 보니 모두 VARCHAR로 되어 있네요. 그럼 '문자형'입니다.
SUBSTRING 함수를 이용해서 일을 뻅니다.
for row in c.execute('SELECT *, SUBSTR(대출일수, 1, (length(대출일수)-1)) AS 대출일수_수정 FROM 도서대출내역2;'):
print(row)
('101', '문강태', '2020-06', '20일', '20')
('102', '고문영', '2020-06', '10일', '10')
('103', '문상태', '2020-06', '8일', '8')
('104', '강기둥', '2020-06', '3일', '3')
CAST 함수를 이용하여, 잘라낸 부분에 더하여 숫자로 변환해 보겠습니다.
for row in c.execute('SELECT *, CAST(SUBSTR(대출일수, 1, (length(대출일수)-1)) AS INT) AS 대출일수_수정 FROM 도서대출내역2 ;'):
print(row)
('101', '문강태', '2020-06', '20일', 20)
('102', '고문영', '2020-06', '10일', 10)
('103', '문상태', '2020-06', '8일', 8)
('104', '강기둥', '2020-06', '3일', 3)
대출일수_수정' 컬럼의 평균을 구해보겠습니다.
for row in c.execute('SELECT ID, 이름, 대출년월, AVG(CAST(SUBSTR(대출일수, 1, (length(대출일수)-1)) AS INT)) AS 대출일수_평균 FROM 도서대출내역2 GROUP BY 1,2;'):
print(row)
('101', '문강태', '2020-06', 20.0)
('102', '고문영', '2020-06', 10.0)
('103', '문상태', '2020-06', 8.0)
('104', '강기둥', '2020-06', 3.0)
SELECT * FROM 도서대출내역2 WHERE ~
SELECT * FROM 도서대출내역2 WHERE 조건1 AND 조건2 AND 조건3 AND (조건 4 OR 조건5);
특정 문자를 포함하는 row를 가져오고 싶을 때
특정 기간 혹은 특정 날짜의 전 또는 이후의 row를 가져오고 싶을 때
특정 숫자 이상 또는 이하의 row를 가져오고 싶을 때
for row in c.execute('SELECT * FROM 도서대출내역2 WHERE 이름 LIKE "문%" ;'):
print(row)
('101', '문강태', '2020-06', '20일')
('103', '문상태', '2020-06', '8일')
for row in c.execute('SELECT * FROM 도서대출내역 WHERE 대출일 >= "2020-06-01" AND 대출일 <= "2020-06-07" ;'):
print(row)
('101', '문강태', 'aaa', '2020-06-01', '2020-06-05')
('102', '고문영', 'bbb', '2020-06-01', None)
('103', '문상태', 'ccc', '2020-06-01', '2020-06-05')
BETWEEN이라는 함수를 활용할 수도 있습니다.
for row in c.execute('SELECT * FROM 도서대출내역 WHERE 대출일 BETWEEN "2020-06-01" AND "2020-06-07" ;'):
print(row)
('101', '문강태', 'aaa', '2020-06-01', '2020-06-05')
('102', '고문영', 'bbb', '2020-06-01', None)
('103', '문상태', 'ccc', '2020-06-01', '2020-06-05')
for row in c.execute('SELECT *, CAST(SUBSTR(대출일수, 1, (length(대출일수)-1)) AS INT) AS 대출일수_수정 FROM 도서대출내역2 WHERE 대출일수_수정 > 5 '):
print(row)
('101', '문강태', '2020-06', '20일', 20)
('102', '고문영', '2020-06', '10일', 10)
('103', '문상태', '2020-06', '8일', 8)
for row in c.execute('SELECT * FROM 도서대출내역 WHERE 반납일 IS NOT NULL;'):
print(row)
('101', '문강태', 'aaa', '2020-06-01', '2020-06-05')
('101', '문강태', 'ccc', '2020-06-20', '2020-06-25')
('103', '문상태', 'ccc', '2020-06-01', '2020-06-05')
반납일이 NULL이라는 것은 미반납 상태 또는 반납일에 대한 정보가 없다는 뜻일 테니, NULL이 아닌 상태는 반납을 한 사람들을 의미하겠네요.
for row in c.execute('SELECT * FROM 도서대출내역 WHERE 반납일 IS NULL;'):
print(row)
('102', '고문영', 'bbb', '2020-06-01', None)
('102', '고문영', 'ddd', '2020-06-08', None)
('104', '강기둥', None, None, None)
import os
db_path = os.getenv('HOME')+'/mydb.db'
conn = sqlite3.connect(db_path) # mydb.db에 연결합니다.
c = conn.cursor()
print('대출내역 테이블')
for row in c.execute('SELECT * FROM 대출내역;'):
print(row)
print('')
print('도서명 테이블')
for row in c.execute('SELECT * FROM 도서명;'):
print(row)
대출내역 테이블
('101', '문강태', 'aaa')
('102', '고문영', 'bbb')
('102', '고문영', 'fff')
('103', '문상태', 'ccc')
('104', '강기둥', None)
도서명 테이블
('aaa', '악몽을 먹고 자란 소년')
('bbb', '좀비아이')
('ccc', '공룡백과사전')
('ddd', '빨간구두')
('eee', '잠자는 숲속의 미녀')
SELECT 컬럼1, 컬럼2, 컬럼3... FROM A테이블 AS A {INNER/LEFT/RIGHT/FULL OUTER} JOIN B테이블 AS B ON A.결합컬럼 = B.결합컬럼 WHERE ~
query = '''
SELECT A.*, B.도서명
FROM 대출내역 AS A
INNER JOIN 도서명 AS B
ON A.도서ID = B.도서ID;
'''
for row in c.execute(query):
print(row)
('101', '문강태', 'aaa', '악몽을 먹고 자란 소년')
('102', '고문영', 'bbb', '좀비아이')
('103', '문상태', 'ccc', '공룡백과사전')
query = '''
SELECT A.*, B.도서명
FROM 대출내역 AS A
LEFT JOIN 도서명 AS B
ON A.도서ID = B.도서ID;
'''
for row in c.execute(query):
print(row)
('101', '문강태', 'aaa', '악몽을 먹고 자란 소년')
('102', '고문영', 'bbb', '좀비아이')
('102', '고문영', 'fff', None)
('103', '문상태', 'ccc', '공룡백과사전')
('104', '강기둥', None, None)
query = '''
SELECT C.이름, COUNT(*) 대출건수
FROM (
SELECT A.*, B.도서명
FROM 대출내역 AS A
LEFT JOIN 도서명 AS B
ON A.도서ID = B.도서ID ) C
GROUP BY C.이름;
'''
for row in c.execute(query):
print(row)
('강기둥', 1)
('고문영', 2)
('문강태', 1)
('문상태', 1)
query = '''
SELECT A.*, IFNULL(B.도서명, '도서명미상') AS 도서명
FROM 대출내역 AS A
LEFT JOIN 도서명 AS B
ON A.도서ID = B.도서ID;
'''
for row in c.execute(query):
print(row)
('101', '문강태', 'aaa', '악몽을 먹고 자란 소년')
('102', '고문영', 'bbb', '좀비아이')
('102', '고문영', 'fff', '도서명미상')
('103', '문상태', 'ccc', '공룡백과사전')
('104', '강기둥', None, '도서명미상')
query = '''
SELECT 이름,
CASE WHEN 대출일수_수정 > 5
THEN '기간초과'
ELSE '기간내'
END AS 대출기간
FROM (
SELECT *, CAST(SUBSTR(대출일수, 1, (length(대출일수)-1)) AS INT) AS 대출일수_수정
FROM 도서대출내역2
);
'''
for row in c.execute(query):
print(row)
('문강태', '기간초과')
('고문영', '기간초과')
('문상태', '기간초과')
('강기둥', '기간내')