PyMySQLの使い方をおさらいする事にした。
事前準備
テストで使用するデータベースの準備をする。
CREATE DATABASE test_1642051460;
use test_1642051460;
テストで使用するテーブルを作成する。
CREATE TABLE dept (
deptno INT,
dname VARCHAR(14),
loc VARCHAR(13));
CREATE TABLE emp (
empno INT,
ename VARCHAR(10),
job VARCHAR(9),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT);
テーブルが空なのも寂しいので、データを投入する事にした。
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7369, 'SMITH' , 'CLERK' , 7902, STR_TO_DATE('17-DEC-2005', '%d-%b-%Y'), 800, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7499, 'ALLEN' , 'SALESMAN' , 7698, STR_TO_DATE('20-FEB-2006', '%d-%b-%Y'), 1600, 300 , 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7521, 'WARD' , 'SALESMAN' , 7698, STR_TO_DATE('22-FEB-2006', '%d-%b-%Y'), 1250, 500 , 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7566, 'JONES' , 'MANAGER' , 7839, STR_TO_DATE('02-APR-2006', '%d-%b-%Y'), 2975, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7654, 'MARTIN', 'SALESMAN' , 7698, STR_TO_DATE('28-SEP-2006', '%d-%b-%Y'), 1250, 1400, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7782, 'CLARK' , 'MANAGER' , 7839, STR_TO_DATE('09-JUN-2006', '%d-%b-%Y'), 2450, NULL, 10);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7698, 'BLAKE' , 'MANAGER' , 7839, STR_TO_DATE('01-MAY-2006', '%d-%b-%Y'), 2850, NULL, NULL);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7788, 'SCOTT' , 'ANALYST' , 7566, STR_TO_DATE('09-DEC-2007', '%d-%b-%Y'), 3000, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7839, 'KING' , 'PRESIDENT', NULL, STR_TO_DATE('17-NOV-2006', '%d-%b-%Y'), 5000, NULL, 10);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7844, 'TURNER', 'SALESMAN' , 7698, STR_TO_DATE('08-SEP-2006', '%d-%b-%Y'), 1500, 0 , 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7876, 'ADAMS' , 'CLERK' , 7788, STR_TO_DATE('12-JAN-2008', '%d-%b-%Y'), 1100, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7900, 'JAMES' , 'CLERK' , 7698, STR_TO_DATE('03-DEC-2006', '%d-%b-%Y'), 950, NULL, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7902, 'FORD' , 'ANALYST' , 7566, STR_TO_DATE('03-DEC-2006', '%d-%b-%Y'), 3000, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7934, 'MILLER', 'CLERK' , 7782, STR_TO_DATE('23-JAN-2007', '%d-%b-%Y'), 1300, NULL, 10);
これらのテーブル定義とテストデータは「SQLクックブック」のサポートページを参照した。
PyMySQLをインストールする
pipを使ってPyMySQLをインストールする。
pip install pymysql
MySQLに接続しクエリを実行する
import pymysql
conn = pymysql.connect(
host="localhost",
user="root",
port=3306,
passwd="",
db="test_1642051460",
charset="utf8",
use_unicode="true",
)
with conn.cursor() as cursor:
cursor.execute(
"SELECT empno, ename FROM emp LIMIT %s",
(10,),
)
row_list = cursor.fetchall()
((7369, 'SMITH'), (7499, 'ALLEN'), (7521, 'WARD'), (7566, 'JONES'), (7654, 'MARTIN'), (7698, 'BLAKE'), (7782, 'CLARK'), (7788, 'SCOTT'), (7839, 'KING'), (7844, 'TURNER'))
結果はタプルとして返される。
conn.close()
pymysql.cursors.DictCursorを使う
結果がタプルとして返される場合、SELECTで指定したカラムの順序が重要になる。ただ、指定したカラムの順序を間違えると、正しい値を取り扱えない。そして、そういった順序は間違えがちである。 pymysql.cursors.DictCursor
を使うと、タプルではなく、辞書のリストとして結果を返すようになる。
import pymysql
from pymysql.cursors import DictCursor
conn = pymysql.connect(
host="localhost",
user="root",
port=3306,
passwd="",
db="test_1642051460",
charset="utf8",
use_unicode="true",
cursorclass=DictCursor,
)
先ほどと同じようにクエリを実行する。
with conn.cursor() as cursor:
cursor.execute(
"SELECT empno, ename FROM emp LIMIT %s",
(10,),
)
row_list = cursor.fetchall()
[{'empno': 7369, 'ename': 'SMITH'}, {'empno': 7499, 'ename': 'ALLEN'}, {'empno': 7521, 'ename': 'WARD'}, {'empno': 7566, 'ename': 'JONES'}, {'empno': 7654, 'ename': 'MARTIN'}, {'empno': 7698, 'ename': 'BLAKE'}, {'empno': 7782, 'ename': 'CLARK'}, {'empno': 7788, 'ename': 'SCOTT'}, {'empno': 7839, 'ename': 'KING'}, {'empno': 7844, 'ename': 'TURNER'}]
結果は辞書のリストとして返される。こうする事でカラム名を指定して結果を取り扱う事ができる。
conn.close()
使い終わった接続は閉じよう。