« ^ »

PyMySQLを使う

所要時間: 約 3分

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クックブック」のサポートページを参照した。

https://oreilly-japan.github.io/sql-cookbook2-ja/

PyMySQLをインストールする

pipを使ってPyMySQLをインストールする。

pip install pymysql
PyMySQLをインストールする。

MySQLに接続しクエリを実行する

import pymysql

conn = pymysql.connect(
    host="localhost",
    user="root",
    port=3306,
    passwd="",
    db="test_1642051460",
    charset="utf8",
    use_unicode="true",
)
MySQLに接続する。
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,
)
cursorclassにpymysql.cursors.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()
接続を閉じる。

使い終わった接続は閉じよう。