1. MySQL DB 모듈

Python에서 MySQL 데이타베이스를 사용하기 위해 우선 Python DB API 표준을 따르는 MySQL DB 모듈을 다운받아 설치한다. MySQL DB 를 지원하는 Python 모듈은 여러 가지가 있는데, 여기서는 PyMySql 이라는 모듈을 사용해 본다. 참고로 앞 아티클에서 언급하였듯이 다른 MySQL 모듈을 사용하더라도 동일한 API를 사용하게 된다.

MySQL DB 모듈 중 하나인 PyMySql 모듈을 다음과 같이 설치한다.

$ pip install PyMySQL

2. MySql 사용 절차

Python에서 MySQL에 있는 데이타를 사용하는 일반적인 절차는 다음과 같다.

1. PyMySql 모듈을 import 한다
2. pymysql.connect() 메소드를 사용하여 MySQL에 Connect 한다. 호스트명, 로그인, 암호, 접속할 DB 등을 파라미터로 지정한다.
3. DB 접속이 성공하면, Connection 객체로부터 cursor() 메서드를 호출하여 Cursor 객체를 가져온다. DB 커서는 Fetch 동작을 관리하는데 사용되는데, 만약 DB 자체가 커서를 지원하지 않으면, Python DB API에서 이 커서 동작을 Emulation 하게 된다.
4. Cursor 객체의 execute() 메서드를 사용하여 SQL 문장을 DB 서버에 보낸다.
5. SQL 쿼리의 경우 Cursor 객체의 fetchall(), fetchone(), fetchmany() 등의 메서드를 사용하여 데이타를 서버로부터 가져온 후, Fetch 된 데이타를 사용한다.
6. 삽입, 갱신, 삭제 등의 DML(Data Manipulation Language) 문장을 실행하는 경우, INSERT/UPDATE/DELETE 후 Connection 객체의 commit() 메서드를 사용하여 데이타를 확정 갱신한다.
7. Connection 객체의 close() 메서드를 사용하여 DB 연결을 닫는다.

아래 예제들의 기본 샘플 데이타로 아래와 같은 customer 테이블이 있다고 가정하자.

3. MySql 쿼리

SQL 쿼리는 SELECT 문을 작성하여 execute() 메서드를 실행하면 된다. 아래 예제는 위에서 설명한 일반적인 MySql 사용 절차를 따른 것으로, 간단한 SELECT 문을 실행한 후, 전체 ROW 데이타를 출력하는 예이다.

import pymysql

# MySQL Connection 연결
conn = pymysql.connect(host='localhost', user='tester', password='',
                       db='testdb', charset='utf8')

# Connection 으로부터 Cursor 생성
curs = conn.cursor()

# SQL문 실행
sql = "select * from customer"
curs.execute(sql)

# 데이타 Fetch
rows = curs.fetchall()
print(rows)     # 전체 rows
# print(rows[0])  # 첫번째 row: (1, '김정수', 1, '서울')
# print(rows[1])  # 두번째 row: (2, '강수정', 2, '서울')

# Connection 닫기
conn.close()

위의 예제에서 몇가지 주목할 점은
(1) connect() 메서드에서 charset을 명시한 점이다. 종종 클라이언트의 charset이 제대로 설정되지 않으면 한글이 깨지는 경우가 발생할 수 있다.
(2) 커서의 fetchall() 메서드는 모든 데이타를 한꺼번에 클라이언트로 가져올 때 사용된다. 또다른 fetch 메서드로서 fetchone()은 한번 호출에 하나의 Row 만을 가져올 때 사용된다. fetchone()을 여러 번 호출하면, 호출 때 마다 한 Row 씩 데이타를 가져오게 된다. 그리고 fetchmany(n) 메서드는 n개 만큼의 데이타를 한꺼번에 가져올 때 사용된다.
(3) print(rows) 문은 전체 row들을 Tuple의 Tuple로서 출력하게 되고, row[0], row[1]와 같이 인덱스를 지정하면, 첫번째, 두번째 row 등을 가리키게 된다. 각 row는 Tuple로 리턴되며, 컬럼 순서대로 데이타가 표시된다 (주: 이러한 디폴트 Row 데이타 형식을 흔히 Array based cursor라 부르고, 이는 connect() 혹은 cursor() 메서드에서 옵션으로 변경할 수 있다. 아래 참조.).

4. Parameter Placeholder

실제 업무에서 대부분의 SQL 문장에는 동적으로 컬럼 데이타값을 집어 넣어야 하는 경우가 상당히 많다. 동적 SQL문을 구성하기 위해서 파라미터값이 들어가는 위치에 Parameter Placeholder인 %s (MySql의 경우)를 넣고, execute() 메서드의 두번째 파라미터에 실제 파라미터값들의 Tuple을 넣어 주면 된다. 예를 들어, 아래 예제에서 SELECT 문의 WHERE 절을 보면 category와 region 값이 들어가는 곳에 각각 %s 를 넣어 주었다. 그리고 execute() 메서드에서 첫번째 파라미터에는 SQL문을, 두번째 파라미터에는 (1, '서울') 이라는 파라미터값 튜플을 넣어 주었다. 튜플 안의 데이타는 SQL문에서 순서대로 적용되게 되는데, 특히 문자열의 경우 단일인용부호(Single Quote)와 같은 특수 문자들이 자동으로 Escape 되어 처리된다.

Parameter Placeholder로 %s 를 사용하는데, 이 %s 는 일반 문자열 포맷팅에 사용하는 %s, %d 등과는 다른 것이다. Parameter Placeholder에서는 문자열이건 숫자이건 모두 %s 를 사용하며, 문자열이라도 %s를 인용부호로 둘러싸지 않는다. 또한 한가지 주의할 점은 Placeholder는 컬럼값을 대치할 때만 사용될 수 있다. 즉 테이블이나 기타 문장에 Placeholder 를 사용할 수 없다.

import pymysql

# MySQL Connection 연결
conn = pymysql.connect(host='localhost', user='tester', password='7890',
                       db='testdb', charset='utf8')

# Connection 으로부터 Dictoionary Cursor 생성
curs = conn.cursor(pymysql.cursors.DictCursor)

# SQL문 실행
sql = "select * from customer where category=%s and region=%s"
curs.execute(sql, (1, '서울'))

# 데이타 Fetch
rows = curs.fetchall()
for row in rows:
    print(row)
    # 출력 : {'category': 1, 'id': 1, 'region': '서울', 'name': '김정수'}
    print(row['id'], row['name'], row['region'])
    # 1 김정수 서울

# Connection 닫기
conn.close()

만약 Python 문자열에서 사용하는 기본 String Interpolation을 사용하면 데이타에 특수 문자가 있는 경우 SQL문 문법 오류를 발생시킬 수 있다. 예를 들어, 아래는 기본 String Interpolation을 사용한 예인데, 변수 data 안에 단일 인용부호가 있는 경우 SQL Syntax 에러를 유발시키게 된다. 또한 이러한 String Interpolation 혹은 문자열 결합(Concatenation)을 통해 동적 SQL 문을 만드는 방법은 SQL Injection 공격에 쉽게 노출되는 문제점이 있다.

# 잘못된 표현
data = '서\'울'
sql = "select * from customer where category=%s and region=%s" % (1, data)
curs.execute(sql)

5. Dictionary 커서

위의 예제에서 또 한가지 주목할 점은 라인 8에 있는 cursor() 메서드이다. 이 cursor() 메서드 안에 DictCursor를 파라미터로 지정하였는데, 이는 디폴트인 Array based cursor가 아닌 Dictionary based cursor를 사용하겠다는 것을 의미한다. 디폴트 Array based cursor는 Row의 결과값을 배열로 (PyMyMsql에서 정확히는 튜플) 리턴하는데, cursor 생성시 DictCursor 옵션을 주면, Row 결과를 Dictionary 형태로 리턴한다.

curs = conn.cursor(pymysql.cursors.DictCursor)
# ...생략...
rows = curs.fetchall()
for row in rows:
  print(row)
  # 출력 : {'category': 1, 'id': 1, 'region': '서울', 'name': '김정수'}
  print(row['id'], row['name'], row['region'])
  # 1 김정수 서울

위의 예에서 커서가 DictCursor이므로 for 루프 안의 print(row)는 Dictionary 형태로 Row 데이타를 출력하게 된다. 또한 Row 데이타가 Dictionary이므로 row["id"], row["name"] 과 같이 컬럼명을 써서 컬럼값을 구할 수 있다. 이렇게 컬럼인덱스 대신 컬럼명으로 컬럼을 찾게 되면 코드 가독성을 높일 수 있고, 특히 컬럼수가 많을 경우 유용하게 사용될 수 있다.