파이썬 - MySQLdb 기본 예제 코드

파이썬에서 mysql을 접근하는 것은, 지난 글에서 다룬 sqlite3에 비해 더 쉽습니다. 왜냐하면 파일 기반이 아닌, 전반적인 관리를 모두 mysqld 서버 측에서 처리를 하기 때문에 클라이언트는 그저 전통적인 SQL 쿼리에 기반을 둬 DB 명령어에만 집중하면 되기 때문입니다.

전체적인 도움말은 다음의 문서에 잘 나오는데요,

MySQLdb User’s Guide
; https://mysqlclient.readthedocs.io/user_guide.html

간략히 정리해 보면, 우선 pip install을 해주고,

// $ sudo apt install default-libmysqlclient-dev pkg-config -y

c:\temp> pip install mysql-python

// 또는,

c:\temp> pip install mysqlclient

연결 개체를 다음과 같은 식으로 얻을 수 있습니다.

from MySQLdb import _mysql

con = _mysql.connect("localhost", "testusr", "...", "test")

# 또는 이렇게,

con = _mysql.connect(host="localhost", user="testusr", passwd="...", db="test")

이후, 쿼리와 resultset을 다루는 것은 다음과 같은 식으로 간단하게 정리할 수 있습니다.

con.query("INSERT INTO test(name, age, enable) VALUES('테스터', 23, 1)")

con.query("SELECT * FROM test;")

r = con.store_result()

# 또는, 
# r = con.use_result()

while True:
    record = r.fetch_row()
    if not record:
        break

    print(record)

con.query("DELETE FROM test WHERE enable=1 AND age=23")

con.close()

---

그런데, 위의 예제 코드는 공식 문서에서도 나오듯이,



MySQLdb._mysql은 C로 작성한 MySQL API에 대응하는 것으로, 가능한 "MySQLdb._mysql"이 아닌 MySQLdb을 사용하라고 합니다. 이렇게 되면 또 사용법이 달라지는데요, 하지만 Python에서 제공한 DB 인터페이스인 dbapi2를 따르도록 MySQLdb을 구현하고 있으므로 sqlite3의 예제 코드에서와 동일한 경험으로 사용할 수 있습니다.

import MySQLdb

con = MySQLdb.connect("localhost", "testusr", "...", "test", connect_timeout=3) # connect_timeout 단위: 초

cursor = con.cursor()

query = "DELETE FROM test"
cursor.execute(query)

for idx in range(1, 5):
    query = "INSERT INTO test(name, age, enable) VALUES('tester{0}', {1}, {2});".format(idx, idx, idx * 10)
    cursor.execute(query)

con.commit()

query = "SELECT * FROM test"
cursor.execute(query)

record_text = ""

all_rows = cursor.fetchall()
for row in all_rows:
    record_text += str(row)
    # field_name = row[0]
    # field_age = row[1]
    # field_enable = row[2]

# 또는 이렇게,
#
# while True:
#     record = cursor.fetchone()
#     if not record:
#         break
#
#     record_text += str(record)

cursor.close()

con.close()  # 이 코드가 없으면 이후 누적돼 connect 시에 MySQLdb._exceptions.OperationalError: (1040, 'Too many connections') 오류 발생

사실상 위의 코드는 sqlite3에서 사용한 코드와 connect 함수의 인자 설정 방식만 다를 뿐 완전히 같습니다. 참고로 with를 이용한 자원 해제도 가능합니다.

with MySQLdb.connect(...[생략]...) as conn:
    conn.encoding = 'utf8'

    with conn.cursor() as cursor:

    query = "DO SLEEP(10); SELECT * FROM mytable;"
    cursor.execute(query)

---

그나저나, 닷넷 개발자라면 row에 대해 이름으로 값을 얻고 싶을 텐데,

all_rows = cursor.fetchall()
for row in all_rows:
    field_name = row['name']
    field_age = row['age']
    field_enable = row['enable']

# 예외 발생 TypeError at ...
# tuple indices must be integers or slices, not str

실제로 해보면 TypeError 오류가 발생합니다. 왜냐하면, 이것을 위해서는 애당초 cursor에 dictionary 형식으로 row를 유지하도록 다음과 같이 내부 타입을 명시해야 하기 때문입니다.

cursor = con.cursor(MySQLdb.cursors.DictCursor)

# ... query 수행

all_rows = cursor.fetchall()
for row in all_rows:
    field_name = row['name']
    field_age = row['age']
    field_enable = row['enable']

주의해야 할 것은, 이렇게 바꾼 다음부터는 숫자 인덱스 값을 전달할 수 없다는 것입니다.

cursor = con.cursor(MySQLdb.cursors.DictCursor)

# ... query 수행

all_rows = cursor.fetchall()
for row in all_rows:
    field_name = row[0] # 예외 발생 KeyError at ...
    field_age = row[1]
    field_enable = row[2]

---

또 하나 재미있는 것은 한글 처리입니다. MySQL 측의 encoding 설정이 utf8mb4로 되어 있고,

mysql> status;
--------------
mysql  Ver 8.0.26 for Win64 on x86_64 (MySQL Community Server - GPL)

Connection id:          30
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Using delimiter:        ;
Server version:         8.0.26 MySQL Community Server - GPL
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Binary data as:         Hexadecimal
Uptime:                 2 hours 4 min 16 sec

Threads: 4  Questions: 108  Slow queries: 0  Opens: 185  Flush tables: 3  Open tables: 104  Queries per second avg: 0.014
--------------

MySQL Workbench를 이용해 "name" 필드에 직접 한글을 입력한 후 SELECT를 했더니 한글이 깨져 나옵니다.

(218, '??5', 3, '32')

혹은 Workbench를 이용하지 않고 쿼리에 한글을 포함하면,

for idx in range(1, 5):
    query = "INSERT INTO test(name, age, enable) VALUES('테스터{0}', {1}, {2});".format(idx, idx, idx * 10)
    cursor.execute(query)

# 예외 발생
# UnicodeEncodeError at /bbs/mysqlclient_wrapper
# 'charmap' codec can't encode characters in position 44-46: character maps to <undefined>

UnicodeEncodeError 오류가 발생합니다. 파이썬의 기본 인코딩이 Unicode이고 MySQL은 utf-8로 설정되어 있으므로 이럴 때는 어느 한쪽의 인코딩을 맞춰주면 됩니다. 물론 MySQL 데이터베이스 측을 바꿔도 되겠지만 대개의 경우 현업에서 그런 요구는 무리죠. ^^ 따라서 남은 방법은, 연결 문자열에 charset을 설정하는 식으로 해결할 수 있습니다.

con = MySQLdb.connect("localhost", "testusr", "...", "test", charset='utf8')

제 경우에 MySQL의 encoding이 utf8mb4로 되어 있기 때문에 utf8mb4로 바꿔도 무방합니다. 둘 간의 차이를 검색해 보면,

[MariaDB] Setting utf8mb4 Character Set
; https://medium.com/oldbeedev/mysql-utf8mb4-character-set-%EC%84%A4%EC%A0%95%ED%95%98%EA%B8%B0-da7624958624

utf-8은 원래 모든 유니코드 문자를 인코딩할 수 있지만 MySQL의 경우에는 특별히 3바이트 이내로 제한을 했다고 합니다. 즉, MySQL에서 utf8은 제한적인 utf-8 형식에 해당하는 것으로 원래는 이름을 다른 걸로 썼어야 했을 것입니다. 어쨌든 나중에는 이러한 제한을 4바이트까지 풀어야 하는 요구 사항이 나왔을 것이고 이로 인해 원래의 utf-8에 해당하는 utf8mb4가 나온 것입니다. 정리하면, 만약 예전의 utf8을 utf8mb3으로 작명했었다면 새로운 utf8mb4를 그냥 utf8로 통일할 수 있었을 것입니다.

실제로 다음의 문서를 보면 utf8mb3라는 이름을 사용하고 있습니다. ^^

10.9.1 The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding)
; https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8mb4.html

---

설치 시 이렇게 오류가 발생한다면?

$ pip install mysqlclient==2.0.3
Collecting mysqlclient==2.0.3
  Downloading mysqlclient-2.0.3.tar.gz (88 kB)
     |████████████████████████████████| 88 kB 2.2 MB/s
    ERROR: Command errored out with exit status 1:
     command: /usr/bin/python3 -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-install-zltsyles/mysqlclient/setup.py'"'"'; __file__='"'"'/tmp/pip-install-zltsyles/mysqlclient/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' egg_info --egg-base /tmp/pip-install-zltsyles/mysqlclient/pip-egg-info
         cwd: /tmp/pip-install-zltsyles/mysqlclient/
    Complete output (15 lines):
    /bin/sh: 1: mysql_config: not found
    /bin/sh: 1: mariadb_config: not found
    /bin/sh: 1: mysql_config: not found
    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "/tmp/pip-install-zltsyles/mysqlclient/setup.py", line 15, in <module>
        metadata, options = get_config()
      File "/tmp/pip-install-zltsyles/mysqlclient/setup_posix.py", line 70, in get_config
        libs = mysql_config("libs")
      File "/tmp/pip-install-zltsyles/mysqlclient/setup_posix.py", line 31, in mysql_config
        raise OSError("{} not found".format(_mysql_config_path))
    OSError: mysql_config not found
    mysql_config --version
    mariadb_config --version
    mysql_config --libs
    ----------------------------------------
ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.

"default-libmysqlclient-dev" 구성요소를 설치하면 됩니다.

$ sudo apt install default-libmysqlclient-dev -y

---

[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]

[최초 등록일: 11/1/2021]
[최종 수정일: 11/16/2023]


이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-변경금지 2.0 대한민국 라이센스에 따라 이용하실 수 있습니다.

by SeongTae Jeong, mailto:techsharer at outlook.com