파이썬 MySQL 데이터베이스 접근: 표준 DB-API 사용법

데이터베이스 인터페이스를 위한 파이썬 표준은 Python DB-API입니다. 대부분의 파이썬 데이터베이스 인터페이스는 이 표준을 준수합니다.

애플리케이션에 적합한 데이터베이스를 선택할 수 있습니다. Python Database API는 다음과 같은 다양한 데이터베이스 서버를 지원합니다.

GadFly
mSQL
MySQL
PostgreSQL
Microsoft SQL Server 2000
Informix
Interbase
Oracle
Sybase

사용 가능한 파이썬 데이터베이스 인터페이스 목록: Python Database Interfaces and APIs. 접근해야 하는 각 데이터베이스에 대해 별도의 DB API 모듈을 다운로드해야 합니다. 예를 들어, Oracle 데이터베이스와 MySQL 데이터베이스에 모두 접근해야 하는 경우 Oracle과 MySQL 데이터베이스 모듈을 모두 다운로드해야 합니다.

DB API는 가능한 한 파이썬 구조와 구문을 사용하여 데이터베이스 작업을 위한 최소한의 표준을 제공합니다. 이 API에는 다음이 포함됩니다.

API 모듈 임포트
데이터베이스와의 연결 획득
SQL 문 및 저장 프로시저 실행
연결 종료

MySQL을 사용하여 모든 개념을 학습하므로 MySQLdb 모듈에 대해 이야기해 보겠습니다.

MySQLdb란?

MySQLdb는 파이썬에서 MySQL 데이터베이스 서버에 연결하기 위한 인터페이스입니다. Python Database API v2.0을 구현하며 MySQL C API 위에 구축되어 있습니다.

MySQLdb 설치 방법

진행하기 전에 머신에 MySQLdb가 설치되어 있는지 확인하세요. 파이썬 스크립트에 다음을 입력하고 실행하세요.

#!/usr/bin/python
import MySQLdb

다음과 같은 결과가 나오면 MySQLdb 모듈이 설치되지 않은 것입니다.

Traceback (most recent call last):
  File "test.py", line 3, in 
    import MySQLdb
ImportError: No module named MySQLdb

MySQLdb 모듈을 설치하려면 다음 명령을 사용하세요.

Ubuntu의 경우: $ sudo apt-get install python-pip python-dev libmysqlclient-dev
Fedora의 경우: $ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
파이썬 명령 프롬프트: pip install MySQL-python

참고 - 위 모듈 설치 시 루트 권한이 필요합니다.

데이터베이스 연결

MySQL 데이터베이스에 연결하기 전에 다음 사항을 확인하세요.

TESTDB 데이터베이스를 생성했습니다.
TESTDB에 EMPLOYEE 테이블을 생성했습니다.
이 테이블에는 FIRST_NAME, LAST_NAME, AGE, SEX, INCOME 필드가 있습니다.
TESTDB에 접근하기 위한 사용자 ID "testuser"와 비밀번호 "test123"이 설정되어 있습니다.
파이썬 모듈 MySQLdb가 머신에 제대로 설치되어 있습니다.
MySQL 튜토리얼을 통해 MySQL 기초를 이해했습니다.

예제

다음은 MySQL 데이터베이스 "TESTDB"에 연결하는 예제입니다.

#!/usr/bin/python
import MySQLdb
# 데이터베이스 연결 열기
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
# cursor() 메서드를 사용하여 커서 객체 준비
cursor = db.cursor()
# execute() 메서드를 사용하여 SQL 쿼리 실행
cursor.execute("SELECT VERSION()")
# fetchone() 메서드를 사용하여 단일 행 가져오기
data = cursor.fetchone()
print("Database version : %s " % data)
# 서버에서 연결 해제
db.close()

이 스크립트를 실행하면 내 리눅스 머신에서 다음과 같은 결과가 나옵니다.

Database version : 5.0.45

데이터 소스와 연결이 설정되면 Connection 객체가 반환되어 db에 저장되어 추가 사용을 위해 보관되고, 그렇지 않으면 db는 None으로 설정됩니다. 다음으로 db 객체를 사용하여 cursor 객체를 생성하고, 이는 SQL 쿼리를 실행하는 데 사용됩니다. 마지막으로, 데이터베이스 연결이 닫히고 리소스가 해제되도록 합니다.

데이터베이스 테이블 생성

데이터베이스 연결이 설정되면 생성된 커서의 execute 메서드를 사용하여 데이터베이스 테이블에 테이블이나 레코드를 생성할 준비가 되었습니다.

예제

데이터베이스 테이블 EMPLOYEE를 생성해 보겠습니다.

#!/usr/bin/python
import MySQLdb
# 데이터베이스 연결 열기
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
# cursor() 메서드를 사용하여 커서 객체 준비
cursor = db.cursor()
# execute() 메서드를 사용하여 이미 존재하는 경우 테이블 삭제
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 요구사항에 따라 테이블 생성
sql = """CREATE TABLE EMPLOYEE (
    FIRST_NAME CHAR(20) NOT NULL,
    LAST_NAME CHAR(20),
    AGE INT,
    SEX CHAR(1),
    INCOME FLOAT
)"""
cursor.execute(sql)
# 서버에서 연결 해제
db.close()

INSERT 작업

데이터베이스 테이블에 레코드를 생성하려 할 때 필요합니다.

예제

다음 예제는 SQL INSERT 문을 실행하여 EMPLOYEE 테이블에 레코드를 생성합니다.

#!/usr/bin/python
import MySQLdb
# 데이터베이스 연결 열기
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
# cursor() 메서드를 사용하여 커서 객체 준비
cursor = db.cursor()
# 데이터베이스에 레코드를 INSERT하기 위한 SQL 쿼리 준비
sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
    # SQL 명령 실행
    cursor.execute(sql)
    # 데이터베이스에서 변경사항 커밋
    db.commit()
except:
    # 오류 발생 시 롤백
    db.rollback()
# 서버에서 연결 해제
db.close()

위 예제는 동적으로 SQL 쿼리를 생성하기 위해 다음과 같이 작성할 수도 있습니다.

#!/usr/bin/python
import MySQLdb
# 데이터베이스 연결 열기
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
# cursor() 메서드를 사용하여 커서 객체 준비
cursor = db.cursor()
# 데이터베이스에 레코드를 INSERT하기 위한 SQL 쿼리 준비
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
    # SQL 명령 실행
    cursor.execute(sql)
    # 데이터베이스에서 변경사항 커밋
    db.commit()
except:
    # 오류 발생 시 롤백
    db.rollback()
# 서버에서 연결 해제
db.close()

예제

다음 코드 세그먼트는 매개변수를 직접 전달하는 또 다른 실행 형태입니다.

..................................
user_id = "test123"
password = "password"
con.execute('insert into Login values("%s", "%s")' % \
            (user_id, password))
..................................

READ 작업

데이터베이스에 대한 READ 작업은 데이터베이스에서 유용한 정보를 가져오는 것을 의미합니다.

데이터베이스 연결이 설정되면 이 데이터베이스에 쿼리를 날릴 준비가 되었습니다. fetchone() 메서드로 단일 레코드를 가져오거나 fetchall() 메서드로 데이터베이스 테이블에서 여러 값을 가져올 수 있습니다.

fetchone() - 쿼리 결과 세트의 다음 행을 가져옵니다. 결과 세트는 커서 객체를 사용하여 테이블을 쿼리할 때 반환되는 객체입니다.
fetchall() - 결과 세트의 모든 행을 가져옵니다. 일부 행이 이미 결과 세트에서 추출된 경우 나머지 행을 가져옵니다.
rowcount - 읽기 전용 속성으로 execute() 메서드에 의해 영향을 받은 행 수를 반환합니다.

예제

다음 프로시저는 급여가 1000보다 큰 EMPLOYEE 테이블의 모든 레코드를 조회합니다.

#!/usr/bin/python
import MySQLdb
# 데이터베이스 연결 열기
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
# cursor() 메서드를 사용하여 커서 객체 준비
cursor = db.cursor()
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (1000)
try:
    # SQL 명령 실행
    cursor.execute(sql)
    # 모든 행을 리스트의 리스트로 가져오기
    results = cursor.fetchall()
    for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        # 가져온 결과 출력
        print("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
              (fname, lname, age, sex, income))
except:
    print("Error: unable to fetch data")
# 서버에서 연결 해제
db.close()

이 코드는 다음과 같은 결과를 출력합니다.

fname=Mac, lname=Mohan, age=20, sex=M, income=2000

UPDATE 작업

데이터베이스에 대한 UPDATE 작업은 이미 데이터베이스에 있는 하나 이상의 레코드를 업데이트하는 것을 의미합니다.

다음 프로시저는 SEX가 'M'인 모든 레코드를 업데이트합니다. 여기서는 모든 남성의 AGE를 1년 증가시킵니다.

예제

#!/usr/bin/python
import MySQLdb
# 데이터베이스 연결 열기
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
# cursor() 메서드를 사용하여 커서 객체 준비
cursor = db.cursor()
# 업데이트할 레코드에 대한 SQL 쿼리 준비
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
    # SQL 명령 실행
    cursor.execute(sql)
    # 데이터베이스에서 변경사항 커밋
    db.commit()
except:
    # 오류 발생 시 롤백
    db.rollback()
# 서버에서 연결 해제
db.close()

DELETE 작업

데이터베이스에서 일부 레코드를 삭제하려 할 때 DELETE 작업이 필요합니다. AGE가 20보다 큰 EMPLOYEE의 모든 레코드를 삭제하는 프로시저는 다음과 같습니다.

예제

#!/usr/bin/python
import MySQLdb
# 데이터베이스 연결 열기
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
# cursor() 메서드를 사용하여 커서 객체 준비
cursor = db.cursor()
# 삭제할 레코드에 대한 SQL 쿼리 준비
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
    # SQL 명령 실행
    cursor.execute(sql)
    # 데이터베이스에서 변경사항 커밋
    db.commit()
except:
    # 오류 발생 시 롤백
    db.rollback()
# 서버에서 연결 해제
db.close()

트랜잭션 수행

트랜잭션은 데이터 일관성을 보장하는 메커니즘입니다. 트랜잭션에는 다음 네 가지 속성이 있습니다.

원자성(Atomicity) - 트랜잭션이 완전히 완료되거나 아예 아무 일도 일어나지 않습니다.
일관성(Consistency) - 트랜잭션은 일관된 상태에서 시작하여 시스템을 일관된 상태로 남겨야 합니다.
격리성(Isolation) - 트랜잭션의 중간 결과는 현재 트랜잭션 외부에서 보이지 않습니다.
지속성(Durability) - 트랜잭션이 커밋되면 시스템 장애 후에도 효과가 지속됩니다.

Python DB API 2.0은 트랜잭션을 commit하거나 rollback하는 두 가지 메서드를 제공합니다.

예제

트랜잭션 구현 방법을 이미 알고 있습니다. 다시 비슷한 예제입니다.

# 삭제할 레코드에 대한 SQL 쿼리 준비
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
    # SQL 명령 실행
    cursor.execute(sql)
    # 데이터베이스에서 변경사항 커밋
    db.commit()
except:
    # 오류 발생 시 롤백
    db.rollback()

COMMIT 작업

커밋은 데이터베이스에 변경사항을 확정하는 녹색 신호를 주는 작업이며, 이 작업 이후에는 어떤 변경도 되돌릴 수 없습니다.

commit 메서드 호출의 간단한 예제입니다.

db.commit()

ROLLBACK 작업

하나 이상의 변경사항에 만족하지 못하고 해당 변경사항을 완전히 되돌리려면 rollback() 메서드를 사용하세요.

rollback() 메서드 호출의 간단한 예제입니다.

db.rollback()

데이터베이스 연결 해제

데이터베이스 연결을 해제하려면 close() 메서드를 사용하세요.

db.close()

사용자가 close() 메서드로 데이터베이스 연결을 닫으면 미결 트랜잭션은 DB에 의해 롤백됩니다. 그러나 DB의 하위 구현 세부 사항에 의존하는 대신, 애플리케이션에서 명시적으로 commit 또는 rollback을 호출하는 것이 더 좋습니다.

오류 처리

오류의 소스는 많습니다. 몇 가지 예로는 실행된 SQL 문의 구문 오류, 연결 실패, 이미 취소되거나 완료된 문 핸들에 대한 fetch 메서드 호출 등이 있습니다.

DB API는 각 데이터베이스 모듈에 존재해야 하는 여러 오류를 정의합니다. 다음 표는 이러한 예외를 나열합니다.

번호	예외 및 설명
1	Warning 치명적이지 않은 문제에 사용. StandardError를 서브클래스해야 함.
2	Error 오류의 기본 클래스. StandardError를 서브클래스해야 함.
3	InterfaceError 데이터베이스 자체가 아닌 데이터베이스 모듈의 오류에 사용. Error를 서브클래스해야 함.
4	DatabaseError 데이터베이스의 오류에 사용. Error를 서브클래스해야 함.
5	DataError 데이터의 오류를 나타내는 DatabaseError의 서브클래스.
6	OperationalError 데이터베이스 연결 손실 등의 오류를 나타내는 DatabaseError의 서브클래스. 이 오류는 일반적으로 파이썬 스크립터의 제어 밖에 있습니다.
7	IntegrityError 유일성 제약이나 외래 키 등 관계 무결성을 손상시킬 상황에 대한 DatabaseError의 서브클래스.
8	InternalError 커서가 더 이상 활성화되지 않는 등 데이터베이스 모듈 내부 오류를 나타내는 DatabaseError의 서브클래스.
9	ProgrammingError 잘못된 테이블 이름 등 사용자에게 책임이 있는 오류를 나타내는 DatabaseError의 서브클래스.
10	NotSupportedError 지원되지 않는 기능을 호출하려 할 때의 DatabaseError 서브클래스.

파이썬 스크립트는 이러한 오류를 처리해야 하지만, 위 예외를 사용하기 전에 MySQLdb가 해당 예외를 지원하는지 확인하세요. DB API 2.0 명세를 읽으면 더 많은 정보를 얻을 수 있습니다.

파이썬 정규표현식 마스터하기: 실전 가이드 파이썬 네트워크 프로그래밍: 소켓과 고급 프로토콜 마스터하기

python