파이썬으로 DB 다루기 - Python Database API

학습 전, 잘 모르는 용어 정리

API란?

API(Application Programming Interface 애플리케이션 프로그래밍 인터페이스, 응용 프로그램 프로그래밍 인터페이스)는 응용 프로그램에서 사용할 수 있도록, 운영 체제나 프로그래밍 언어가 제공하는 기능을 제어할 수 있게 만든 인터페이스를 뜻한다. 주로 파일 제어, 창 제어, 화상 처리, 문자 제어 등을 위한 인터페이스를 제공한다.

위키백과 API

인터페이스란?

인터페이스(interface)는 서로 다른 두 개의 시스템, 장치 사이에서 정보나 신호를 주고받는 경우의 접점이나 경계면이다. 즉, 사용자가 기기를 쉽게 동작시키는데 도움을 주는 시스템을 의미한다.

위키백과 인터페이스

Python DB-API

Python DB-API는 여러 DB에 접근할 수 있는 표준 API입니다. 표준 API의 작업은

1. DB를 연결한다.
2. SQL 문을 실행한다.
3. DB 연결을 닫는다.

파이썬 DB-API는 기본적으로 PEP249 인터페이스를 따르도록 권장됩니다. 기본적으로 connet(), close(), commit(), rollback() 등 여러 가지 함수가 선언되어 있습니다. 이 함수를 이용하면 거의 모든 DB에 대해 동일한 함수를 사용해 조작 가능합니다.

파이썬에서 지원하는 DB는 매우 다양하기 때문에 각 DB에 상응하는 별도의 모듈이 필요합니다. 파이썬은 MySQL, PostgreSQL, MSSQL, Sqlite, Oracle, Sybase, informix, mSQL 등 대표적인 DB를 모두 Python DB-API를 통해 지원합니다.

[출처 : http://jtg2078.github.io/relational-db/python-db-api.html]

SQLite

SQLite는 서버의 필요 없이 DB의 파일에 기초하여 DB 처리를 구현한 임베디드 SQL DB엔진입니다.

Python DB API

파이썬에서 sqlite 모듈을 import하겠습니다.

import sqlite3

sqlite3 모듈은 표준 라이브러리로 SQLite에 대한 인터페이스를 기본적으로 제공합니다.

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

>>> <sqlite3.Connection object at 0x7fb5d29b1e30>

conn에 DB 이름을 정하여 입력합니다. [이름].[확장자명]의 형식으로 저장하면 됩니다. 저는 mydb로 저장하겠습니다.

conn 객체에는 SQL 연결과 관련된 셋팅이 포함되어 있습니다. 이번엔 Connect() 함수의 연결을 사용하는 새로운 cursor 객체를 만듭니다.

c = conn.cursor()
print(c)

>>> <sqlite3.Cursor object at 0x7fb5d06805e0>

Cursor는 SQL 질의(Query)를 수행하고 결과를 얻는데 사용하는 객체입니다. INSERT처럼 DB에만 적용되는 명령어를 사용한다면 Cursor를 안 사용할 수 있지만 SELECT와 같이 데이터를 불러올 때는 SQL 질의 수행 결과에 접근하기 위한 Cursor가 반드시 필요합니다.

이러한 이유로 습관적으로 conn.cursor()를 사용하는 것을 권해 드립니다. 참고로, 파이썬 공식 개발 가이드에서도 Cursor를 사용하는 것을 표준으로 안내하고 있습니다.

SQL INSERT문: 테이블에 데이터를 삽입(추가)하는 질의입니다.
SQL SELECT문: 테이블에 데이터를 조건에 따라 조회하는 질의입니다.

c.execute("CREATE TABLE IF NOT EXISTS stocks (date text, trans text, symbol test, qty real, price real)")

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)

방금 sqlite3 모듈을 이용해 데이터베이스에 테이블을 하나 만들고 데이터를 인서트 한 후, 그 데이터를 조회해 보는 아주 기본적인 DB 핸들링 시나리오를 수행해 보았습니다.

Commit

삽입, 갱신, 삭제 등의 SQL 질의가 끝났다면 conn.commit()를 호출해야 DB가 실제로 업데이트 됩니다. commit()을 하기 전에는 DB에 데이터가 업데이트된 것이 아닌 임시로만 바뀐 것입니다.

sqlite3를 이용해 데이터베이스에 connection을 통해 인서트 된 데이터는 conn.commit()을 호출하기 전까지는 그 connection 안에서만 유효합니다.

원본 데이터에 실제로 적용하려면 commit() 명령어를 이용하면 좋고, select처럼 데이터를 가져오기만 하는 질의문에는 commit()가 필요 없습니다.

conn.commit()

commit()을 통해 데이터베이스에 데이터 변경이 실제적으로 반영되었습니다. 이런 것을 데이터베이스에서는 트랜잭션(transaction) 관리라고 합니다.

commit()을 완료했다면 DB와 대화하는 것을 마무리는 DB와의 연결을 끊는 것으로 이루어집니다. 바로 close()을 이용하면 됩니다.

c.close()
conn.close()

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

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)

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

데이터의 생성/삭제/갱신 등을 처리할 때 필요한 DML문의 호출 방법에 대해 위의 코드를 중심으로 보겠습니다.

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

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

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

1번의 CREATE TABLE IF NOT EXISTS 도서명 (도서ID varchar, 도서명 varchar) 코드를 봅시다. 이 부분은 만약 테이블이 존재하지 않으면 테이블을 생성합니다. 그리고 변수명을 설정하는 코드입니다.

2번 코드는 각 변수명에 맞게 데이터를 실제로 생성합니다.

3번 INSERT INTO 도서명 VALUES (?, ?) 부분은 각 테이블의 변수 (도서ID, 도서명)에 2번에서 입력한 데이터 (data)를 넣겠다는 뜻입니다. 이렇게 '?'를 이용해 데이터를 쿼리에 바인딩합니다.

c.executemany() 메소드는 한꺼번에 여러 개의 데이터 처리를 가능하게 합니다.

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

for row in c.execute('SELECT * FROM 도서명'):
	print(row)
    
>>> ('aaa', '악몽을 먹고 자란 소년')
('bbb', '좀비아이')
('ccc', '공룡백과사전')
('ddd', '빨간구두')
('eee', '잠자는 숲속의 미녀')

조회 결과가 잘 나옵니다!