파이썬 - 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()
그런데, 위의 예제 코드는 공식 문서에서도 나오듯이,
If you want to write applications which are portable across databases, use MySQLdb, and avoid using this module directly. MySQLdb._mysql provides an interface which mostly implements the MySQL C API
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
혹은 이런 오류가 발생한다면?
gcc -pthread -B /home/testusr/miniconda3/envs/py311build/compiler_compat -DNDEBUG -fwrapv -O2 -Wall -fPIC -O2 -isystem /home/testusr/miniconda3/envs/py311build/include -fPIC -O2 -isystem /home/testusr/miniconda3/envs/py311build/include -fPIC "-Dversion_info=(2, 2, 7, 'final', 0)" -D__version__=2.2.7 -I/home/testusr/miniconda3/envs/py311build/include/python3.11 -c src/MySQLdb/_mysql.c -o build/temp.linux-x86_64-cpython-311/src/MySQLdb/_mysql.o -I/usr/include/mysql -std=c99
error: command 'gcc' failed: No such file or directory
[end of output]
gcc 구성 요소를 설치하셔야 합니다.
$ sudo apt-get install gcc -y
[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]