結合演算を用いた問い合わせをPythonプログラミングで実現

今日は、『実践Pythonによるデータベース入門』の5.5―結合演算を用いた問い合わせ―で学んだことをアウトプットします。

結合演算を用いた問い合わせを行い、データを抽出するPythonプログラムを作成していきます。

【例題5.5】

4章で作成したデータベースjikanwaribaseについて、下記仕様要求を実現するPythonプログラムを作成せよ。

仕様要求
（1）MySQLサーバーのjikanwaribaseへの接続を行う
（2）例題4.3の問い合わせをタプルoperationsにセットする
  →例題4.3について、以前に投稿しました。

（3）operationsに対して、以下の処理を繰り返す：
　　　　（ア）問い合わせを実行する
　　　　（イ）（ア）の結果を画面に表示する
（4）接続を切断する。

（1）MySQLサーバーのjikanwaribaseへの接続を行う

コマンドライン上で、ユーザーsampleuserに対して、データベースjikanwaribaseに対するすべての権限を与えます。

mysql> grant all on jikanwaribase.* to 'sampleuser';

コネクターも変更する必要がありますね。

# コネクターを作成
config = {
   'user': 'sampleuser',
   'password': 'hogehoge',
   'host': 'localhost',
   'database': 'jikanwaribase',
}

（2）例題4.3の問い合わせをタプルoperationsにセットする

例題4.3で行った問い合わせは、以下の4つでした。

（1）学生証番号が等しいと条件の下で、テーブルgakuseiとテーブルrishuの等結合を求める。
（2）テーブルgakuseiとテーブルrishuの自然結合を求める。
（3）学生証番号が等しいの条件の下で、テーブルgakuseiとテーブルrishuの等結合を求めて、さらに科目番号が等しいの条件の下で、テーブルkamokuとの等結合を求める。ただし、以下の項目のみを表示するものとする。
学生の氏名漢字、科目の科目名、教室、曜日、時限
（4）テーブルgakuseiとテーブルrishuとテーブルkamokuの自然結合を求める。ただし、以下の項目のみを表示するものとする。
学生の氏名漢字、科目の科目名、教室、曜日、時限

それぞれのSQL命令文は以下のようになりますね。

（1）SELECT * FROM gakusei INNER JOIN rishu ON gakusei.gakuseino = rishu.gakuseino;

（2）SELECT * FROM gakusei NATURAL JOIN rishu;

（3）SELECT gakusei.namae, kamoku.kamokumei, kamoku.kyousitsu, kamoku.youbi, kamoku.jigen FROM gakusei INNER JOIN rishu ON gakusei.gakuseino = rishu.gakuseino INNER JOIN kamoku ON rishu.kamokuno = kamoku.kamokuno;

（4）SELECT gakusei.namae, kamoku.kamokumei, kamoku.kyousitsu, kamoku.youbi, kamoku.jigen FROM gakusei NATURAL INNER JOIN rishu NATURAL INNER JOIN kamoku;

これら4つの命令文をoperationsに代入すればいいわけですね。

operations = (
"SELECT * FROM gakusei INNER JOIN rishu ON gakusei.gakuseino = rishu.gakuseino;",
"SELECT * FROM gakusei NATURAL JOIN rishu;",
"SELECT gakusei.namae, kamoku.kamokumei, kamoku.kyousitsu, kamoku.youbi, \
kamoku.jigen FROM gakusei INNER JOIN rishu ON gakusei.gakuseino = rishu.gakuseino \
INNER JOIN kamoku ON rishu.kamokuno = kamoku.kamokuno;",
"SELECT gakusei.namae, kamoku.kamokumei, kamoku.kyousitsu, kamoku.youbi, kamoku.jigen \
FROM gakusei NATURAL INNER JOIN rishu NATURAL INNER JOIN kamoku;",
)

「\」で改行したのですが、エラーにならないかが不安…

（3）operationsに対して、以下の処理を繰り返す：
　　　　（ア）問い合わせを実行する
　　　　（イ）（ア）の結果を画面に表示する

for no, operation in enumerate(operations):
   # 問い合わせの実行
   cursor.execute(operation)
   # 問い合わせ結果のすべてのデータを取得、表示
   print("問い合わせ" + str(no+1) + ":")
   print(operation)
   print("問い合わせ" + str(no+1) + "の結果:")
   tuples = cursor.fetchall()
   for tpl in tuples:
       print(tpl)

（4）接続を切断する。

# 接続を切断
dbconnector.close()

実行結果は以下のようになりました。
「\」で改行しても大丈夫なようですね。

$ python3 5_5.py 
データベースへの接続が成功しました。
問い合わせ1:
SELECT * FROM gakusei INNER JOIN rishu ON gakusei.gakuseino = rishu.gakuseino;
問い合わせ1の結果:
('80AB0001', '田中花子', 'たなかはなこ', '80AB0001', 'C110')
('80AB0001', '田中花子', 'たなかはなこ', '80AB0001', 'C212')
('80AB0001', '田中花子', 'たなかはなこ', '80AB0001', 'T1022')
('80AB0001', '田中花子', 'たなかはなこ', '80AB0001', 'C321')
('80AB0001', '田中花子', 'たなかはなこ', '80AB0001', 'T1211')
('80AB0002', '山田聡', 'やまださとし', '80AB0002', 'C211')
('80AB0002', '山田聡', 'やまださとし', '80AB0002', 'C110')
('80AB0002', '山田聡', 'やまださとし', '80AB0002', 'C321')
('80AB0002', '山田聡', 'やまださとし', '80AB0002', 'T1211')
('80AB0002', '山田聡', 'やまださとし', '80AB0002', 'T1023')
('80AB0003', '佐藤美那子', 'さとうみなこ', '80AB0003', 'C211')
('80AB0003', '佐藤美那子', 'さとうみなこ', '80AB0003', 'C110')
('80AB0003', '佐藤美那子', 'さとうみなこ', '80AB0003', 'T1212')
('80AB0003', '佐藤美那子', 'さとうみなこ', '80AB0003', 'C321')
('80AB0003', '佐藤美那子', 'さとうみなこ', '80AB0003', 'T1023')
('80AB0004', '鈴木博', 'すずきひろし', '80AB0004', 'C211')
('80AB0004', '鈴木博', 'すずきひろし', '80AB0004', 'C111')
('80AB0004', '鈴木博', 'すずきひろし', '80AB0004', 'C322')
('80AB0004', '鈴木博', 'すずきひろし', '80AB0004', 'T1212')
('80AB0004', '鈴木博', 'すずきひろし', '80AB0004', 'T1023')
問い合わせ2:
SELECT * FROM gakusei NATURAL JOIN rishu;
問い合わせ2の結果:
('80AB0001', '田中花子', 'たなかはなこ', 'C110')
('80AB0001', '田中花子', 'たなかはなこ', 'C212')
('80AB0001', '田中花子', 'たなかはなこ', 'T1022')
('80AB0001', '田中花子', 'たなかはなこ', 'C321')
('80AB0001', '田中花子', 'たなかはなこ', 'T1211')
('80AB0002', '山田聡', 'やまださとし', 'C211')
('80AB0002', '山田聡', 'やまださとし', 'C110')
('80AB0002', '山田聡', 'やまださとし', 'C321')
('80AB0002', '山田聡', 'やまださとし', 'T1211')
('80AB0002', '山田聡', 'やまださとし', 'T1023')
('80AB0003', '佐藤美那子', 'さとうみなこ', 'C211')
('80AB0003', '佐藤美那子', 'さとうみなこ', 'C110')
('80AB0003', '佐藤美那子', 'さとうみなこ', 'T1212')
('80AB0003', '佐藤美那子', 'さとうみなこ', 'C321')
('80AB0003', '佐藤美那子', 'さとうみなこ', 'T1023')
('80AB0004', '鈴木博', 'すずきひろし', 'C211')
('80AB0004', '鈴木博', 'すずきひろし', 'C111')
('80AB0004', '鈴木博', 'すずきひろし', 'C322')
('80AB0004', '鈴木博', 'すずきひろし', 'T1212')
('80AB0004', '鈴木博', 'すずきひろし', 'T1023')
問い合わせ3:
SELECT gakusei.namae, kamoku.kamokumei, kamoku.kyoushitsu, kamoku.youbi, kamoku.jigen FROM gakusei INNER JOIN rishu ON gakusei.gakuseino = rishu.gakuseino INNER JOIN kamoku ON rishu.kamokuno = kamoku.kamokuno;
問い合わせ3の結果:
('佐藤美那子', 'ネットワーク基礎', '2111', '火', '1')
('山田聡', 'ネットワーク基礎', '2111', '火', '1')
('田中花子', 'ネットワーク基礎', '2111', '火', '1')
('鈴木博', 'プログラミング入門', '2111', '火', '2')
('鈴木博', 'オペレーティングシステム', '1203', '月', '3')
('佐藤美那子', 'オペレーティングシステム', '1203', '月', '3')
('山田聡', 'オペレーティングシステム', '1203', '月', '3')
('田中花子', 'データベース', '1203', '月', '2')
('佐藤美那子', 'プロジェクト1', '1211', '水', '3')
('山田聡', 'プロジェクト1', '1211', '水', '3')
('田中花子', 'プロジェクト1', '1211', '水', '3')
('鈴木博', 'プロジェクト2', '1212', '水', '4')
('田中花子', '英語1', '3221', '火', '2')
('鈴木博', '英語2', '3221', '金', '2')
('佐藤美那子', '英語2', '3221', '金', '2')
('山田聡', '英語2', '3221', '金', '2')
('山田聡', '体育1', '体育館', '木', '3')
('田中花子', '体育1', '体育館', '木', '3')
('鈴木博', '体育2', 'グランド', '火', '2')
('佐藤美那子', '体育2', 'グランド', '火', '2')
問い合わせ4:
SELECT gakusei.namae, kamoku.kamokumei, kamoku.kyoushitsu, kamoku.youbi, kamoku.jigen FROM gakusei NATURAL INNER JOIN rishu NATURAL INNER JOIN kamoku;
問い合わせ4の結果:
('佐藤美那子', 'ネットワーク基礎', '2111', '火', '1')
('山田聡', 'ネットワーク基礎', '2111', '火', '1')
('田中花子', 'ネットワーク基礎', '2111', '火', '1')
('鈴木博', 'プログラミング入門', '2111', '火', '2')
('鈴木博', 'オペレーティングシステム', '1203', '月', '3')
('佐藤美那子', 'オペレーティングシステム', '1203', '月', '3')
('山田聡', 'オペレーティングシステム', '1203', '月', '3')
('田中花子', 'データベース', '1203', '月', '2')
('佐藤美那子', 'プロジェクト1', '1211', '水', '3')
('山田聡', 'プロジェクト1', '1211', '水', '3')
('田中花子', 'プロジェクト1', '1211', '水', '3')
('鈴木博', 'プロジェクト2', '1212', '水', '4')
('田中花子', '英語1', '3221', '火', '2')
('鈴木博', '英語2', '3221', '金', '2')
('佐藤美那子', '英語2', '3221', '金', '2')
('山田聡', '英語2', '3221', '金', '2')
('山田聡', '体育1', '体育館', '木', '3')
('田中花子', '体育1', '体育館', '木', '3')
('鈴木博', '体育2', 'グランド', '火', '2')
('佐藤美那子', '体育2', 'グランド', '火', '2')