1. sqlite3 소개
SQLite는 별도의 DB 서버가 필요없이 DB 파일에 기초하여 데이타베이스 처리를 구현한 Embedded SQL DB 엔진이다. SQLite는 별도의 복잡한 서버 설치가 필요 없고, 쉽고 편리하게 사용할 수 있다는 점에서 널리 사용되고 있다. 오늘날 대부분의 Mac OS X 나 리눅스에서는 SQLite을 기본적으로 내장하고 있지만, 만약 시스템에 내장되어 있지 않는 경우는 http://www.sqlite.org 에서 다운받아 설치할 수 있다. SQLite는 기본적으로 SQLite DB 엔진과 "sqlite3" 라는 Command line tool 을 갖고 있다.
다음은 sqlite3 커맨드 라인 툴을 사용하여 샘플 DB (test.db)를 만들고 customer 테이블을 생성하고, 데이타를 INSERT/SELECT 해 본 예이다. "sqlite3 test.db" 명령문은 만약 test.db 파일이 있으면 해당 파일을 오픈하고, 파일이 없으면 새 파일을 생성한다.
2. pysqlite
Python에서 SQLite 을 사용하기 위해서는 보통 pysqlite 으로 불리우는 Language binding (주: SQLite 라이브러리를 내부에서 사용)을 사용하는데, pysqlite 은 Python 2.5 이상에서 기본적으로 내장되어 있다.
pysqlite 를 import 하기 위해서는 아래와 같이 "import sqlite3" 를 사용한다.
$ python Python 3.4.4 (v3.4.4:737efcadf5a6, Dec 19 2015, 20:19:30) [GCC 4.2.1 (Apple Inc. build 5577)] on darwin Type "help", "copyright", "credits" or "license" for more information. >>> import sqlite3 >>> sqlite3.version '2.6.0' >>>
3. SQLite 쿼리
SQLite를 사용하기 위해서는 우선 사용할 db 파일 (예: test.db)을 sqlite3.connect() 메서드로 오픈하고, SQL 쿼리를 실행하여 데이타를 사용한 후, 마지막에 Connection을 닫으면 된다. DB Connection이 연결되면 Connection 객체가 리턴되는데, 이 객체로부터 커서를 생성하고 커서 객체의 execute() 명령을 실행하여 SQL 쿼리를 실행한다. 아래 예제는 간단한 SELECT 문을 실행한 후, 전체 ROW 데이타를 출력하는 예이다.
import sqlite3
# SQLite DB 연결
conn = sqlite3.connect("test.db")
# Connection 으로부터 Cursor 생성
cur = conn.cursor()
# SQL 쿼리 실행
cur.execute("select * from customer")
# 데이타 Fetch
rows = cur.fetchall()
for row in rows:
print(row)
# Connection 닫기
conn.close()
4. Parameterized Query
실제 업무에서 대부분의 SQL 문장에는 동적으로 컬럼 데이타값을 집어 넣어야 하는 경우가 많은데, 이때 Parameterized Query를 사용한다. SQLite에서는 두 가지 방식으로 Parameterized Query를 만들 수 있다. 즉, ? (물음표)를 Placeholder로 사용하는 방식와 ":Id" 처럼 파라미터 이름을 명식하는 Named Placeholder 방식이 있다. 동적 SQL문을 구성하기 위해선 파라미터값이 들어가는 위치에 Parameter Placeholder를 넣고, execute() 메서드의 두번째 파라미터에 실제 파라미터값들의 Tuple을 넣어 주면 된다. Parameterized Query는 문자열 결합이나 문자열 Interpolation에서 발생할 수 있는 SQL Injection 문제를 해결하는 방식으로 알려져 있다.
Parameter Placeholder에서는 문자열이건 숫자이건 모두 ? (혹은 Named Placeholder) 를 사용하며, 문자열이라도 Placeholder를 인용부호로 둘러싸지 않는다. 또한 한가지 주의할 점은 Placeholder는 컬럼값을 대치할 때만 사용될 수 있다. 즉 테이블이나 기타 문장에 Placeholder 를 사용할 수 없다.
? Placeholder
아래 예제에서 SELECT 문의 WHERE 절을 보면 category와 region 값이 들어가는 곳에 각각 ? 마크를 넣어 주었다. 그리고 execute() 메서드에서 첫번째 파라미터에는 SQL문을, 두번째 파라미터에는 (1, 'SEA') 이라는 파라미터값 튜플을 넣어 주었다. 튜플 안의 데이타는 SQL문에서 순서대로 적용되게 되는데, 특히 문자열의 경우 단일인용부호(Single Quote)와 같은 특수 문자들이 자동으로 Escape 되어 처리된다.
import sqlite3
conn = sqlite3.connect("test.db")
cur = conn.cursor()
sql = "select * from customer where category=? and region=?"
cur.execute(sql, (1, 'SEA'))
rows = cur.fetchall()
for row in rows:
print(row)
conn.close()
Named Placeholder
아래 예제는 Named Placeholder를 사용한 예로서, where 절의 :Id 가 Named Placeholder이다. 이 Id의 값은 execute() 메서드의 두번째 파라미터에 지정되는데, 이 값은 Tuple이 아닌 Dictionary 형태로 Id 값이 지정된다.
sql = "select * from customer where id = :Id"
cur.execute(sql, {"Id": 1})
5. DML (INSERT, UPDATE, DELETE)
SQL에서 데이타 조작하는 INSERT, UPDATE, DELETE 문을 DML(Data Manipulation Language)이라 한다. DML문을 사용하는 방식은 위이 쿼리를 사용하는 방식과 거의 비슷하다. INSERT, UPDATE, DELETE 문에서도 Parameterized Query를 사용한다. 단, DML은 리턴되는 데이타가 없으므로 fetch 를 사용하지 않는다. 또한 디폴트로 Autocommit 이 아니기 때문에, execute() 로 DML 문장을 실행한 후에, Connection 객체의 commit() 메서드를 호출해야 한다. Autocommit 을 사용할 경우에는 아래 connect() 에서 예시하였듯이, isolation_level 을 None 으로 설정한다.
import sqlite3
conn = sqlite3.connect("test.db")
# Autocommit 사용시:
# conn = sqlite3.connect("test.db", isolation_level=None)
cur = conn.cursor()
sql = "insert into customer(name,category,region) values (?, ?, ?)"
cur.execute(sql, ('홍길동', 1, '서울'))
conn.commit()
conn.close()
DML에서 사용하면 유용한 메서드로 executemany() 메소드가 있다. executemany() 메소드는 복수 개의 Row 데이타를 한꺼번에 처리하는데 유용하다. 아래 예제는 executemany() 메서드를 사용하여 3개의 Row들을 INSERT 하는 효과를 갖는다.
import sqlite3
conn = sqlite3.connect("test.db")
cur = conn.cursor()
data = (
('홍진우', 1, '서울'),
('강지수', 2, '부산'),
('김청진', 1, '서울'),
)
sql = "insert into customer(name,category,region) values (?, ?, ?)"
cur.executemany(sql, data)
conn.commit()
conn.close()
6. try와 with 문의 사용
Connection, Cursor 와 같은 리소스들이 Leak 되는 것을 방지하기 위하여 try...finally 혹은 with 문을 사용할 수 있다. try...finally와 with 문은 블럭 내 에러가 발생하더라도 마지막 항상 리소스를 해제하는 역활을 하기 때문에, 데이타베이스 코딩에서 자주 사용된다. 아래 예제는 Connection 리소스를 닫기 위해 with 문을 사용하는 예이다.
import sqlite3
conn = sqlite3.connect("test.db")
with conn:
cur = conn.cursor()
cur.execute("select * from customer")
rows = cur.fetchall()
for row in rows:
print(row)
