1. MySQL DML

SQL에서 데이타 조작하는 INSERT, UPDATE, DELETE 문을 DML(Data Manipulation Language)이라 한다. MySQL에서 INSERT, UPDATE, DELETE를 사용하는 일반적인 절차는 앞 아티클 (MySQL 쿼리)과 유사하다.

MySQL에 DML을 사용하는 일반적인 절차는 다음과 같다.

1. PyMySql 모듈을 import 한다
2. pymysql.connect() 메소드를 사용하여 MySQL에 Connect 한다. 호스트명, 로그인, 암호, 접속할 DB 등을 파라미터로 지정한다.
3. DB 접속이 성공하면, Connection 객체로부터 cursor() 메서드를 호출하여 Cursor 객체를 가져온다.
4. Cursor 객체의 execute() 메서드를 사용하여 INSERT, UPDATE 혹은 DELETE 문장을 DB 서버에 보낸다.
5. 삽입, 갱신, 삭제 등이 모두 끝났으면 Connection 객체의 commit() 메서드를 사용하여 데이타를 Commit 한다. 디폴트로 Autocommit 이 아니므로 commit()을 명시적으로 호출한다. 자동커밋을 원하는 경우, connect() 메서드 호출시 autocommit=True 를 지정한다.
6. Connection 객체의 close() 메서드를 사용하여 DB 연결을 닫는다.

2. INSERT 문

데이타를 테이블에 추가하기 위해 INSERT문을 사용한다. INSERT문에 사용하는 각 컬럼값들은 직접 값을 지정(Hard code)하거나 Parameter Placeholder를 사용할 수 있다.

아래 예제는 두 개의 데이타를 INSERT 하고 Commit 하는 예이다. 여기서 한가지 주의할 점은 데이타 INSERT만 실행하고 Commit하지 않으면, 테이블의 데이타는 변경되지 않는다는 점이다.

import pymysql

conn = pymysql.connect(host='localhost', user='tester', password='7890',
                       db='testdb', charset='utf8')

curs = conn.cursor()
sql = """insert into customer(name,category,region)
         values (%s, %s, %s)"""
curs.execute(sql, ('홍길동', 1, '서울'))
curs.execute(sql, ('이연수', 2, '서울'))
conn.commit()

conn.close()

execute() 메서드는 하나의 Row (하나의 Tuple 데이타)를 치환하여 실행하는 반면, executemany() 메소드는 복수개의 Tuple 데이타 (즉, Tuple of Tuples)를 하나의 DML 문에 적용하게 된다. 아래 예제에서 executemany()는 3개의 INSERT 문을 수행하는 것과 동일한 효과를 갖는다.

data = (
    ('홍진우', 1, '서울'),
    ('강지수', 2, '부산'),
    ('김청진', 1, '서울'),
)
sql = """insert into customer(name,category,region)
         values (%s, %s, %s)"""
curs.executemany(sql, data)
conn.commit()

3. UPDATE, DELETE 문

기존의 데이타를 수정하기 위해 UPDATE 문을 사용하고, 삭제하기 위해서 DELETE 문을 사용한다. UPDATE, DELETE 문에 사용하는 각 컬럼값들은 직접 값을 지정(Hard code)하거나 Parameter Placeholder를 사용할 수 있다.

아래 예제는 하나의 UPDATE 문과 하나의 DELETE 문을 실행하고, Commit하는 예이다. UPDATE 문은 Region이 "서울"인 데이타를 모두 "서울특별시"로 변경하는 것이고, DELETE 문은 id가 6인 customer 데이타를 삭제하는 것이다.

import pymysql

conn = pymysql.connect(host='localhost', user='tester', password='7890',
                       db='testdb', charset='utf8')

curs = conn.cursor()
sql = """update customer
         set region = '서울특별시'
         where region = '서울'"""
curs.execute(sql)

sql = "delete from customer where id=%s"
curs.execute(sql, 6)

conn.commit()
conn.close()

4. try와 with 문의 사용

SQL Connection을 열고 프로그램 중간에서 에러가 발생하면, Connection은 그대로 열려 있는 상태로 있을 수 있다. 이렇게 오픈되어 있는 Connection이 증가하면, 나중에 새로운 Connection을 오픈할 수 없게 되는데, 이를 Connection Leak 이라 부른다. 이러한 Connection Leak을 막기 위하여 아래 예제와 같이 try...finally 블력을 사용하여 finally에서 항상 Conneciton을 Close해 주는 것이 좋다.

import pymysql

conn = pymysql.connect(host='localhost', user='tester', password='7890',
                       db='testdb', charset='utf8')

try:
    # INSERT
    with conn.cursor() as curs:
        sql = "insert into customer(name,category,region) values (%s, %s, %s)"
        curs.execute(sql, ('이광수', 1, '서울'))

    conn.commit()

    # SELECT
    with conn.cursor() as curs:
        sql = "select * FROM customer"
        curs.execute(sql)
        rs = curs.fetchall()
        for row in rs:
            print(row)

finally:
    conn.close()

위 예제의 try 블럭을 보면, INSERT와 SELECT 문을 각기 다른 커서에서 사용하고 있다. 첫번째 INSERT 실행 시 with 문으로 커서를 만들어 자동으로 커서 리소스가 해제되도록 하였고, 두번째 SELECT 시에도 with 문으로 해당 커서가 자동 해제되도록 하였다. 이러한 예제에서 보듯이, SQL 객체들을 다룰 때 try...finally 나 with 문을 적절히 사용하여 리소스를 해제해 주는 것이 좋다.