Python+SQLの基本演習｜非技術者のための業務効率化 #2
当シリーズでは非技術者向けに知っておくと役に立つ技術系の知識についてまとめていきます。
#1では業務効率化にあたっての基本的な考え方を抑えたのちに、PythonやSQLの導入について取り扱いました。
#2ではPythonとSQLの基本的な内容の演習を行います。（双方を連結して使用することも想定し、PyMySQLも使用します。）
以下目次になります。
1. 演習問題の出題
2. 解答＆解説
3. まとめ
1. 演習問題の出題
ex01. FizzBuzz問題： 1~100までの数字を出力するにあたって、3の倍数はFizz、5の倍数はBuzz、3の倍数かつ5の倍数はFizzBuzz、それ以外は数字を出力するようなプログラムを作成せよ。
ex02. SELECT文： iris_dataとiris_masterの双方のテーブルからデータを取得し、Pandasのデータフレーム形式で変数を格納せよ。
ex03. Pandas： ex02で作成したPandasのインスタンスに対し、type(obj)やobj.shapeでオブジェクトの形式やデータフレームのサイズを求めよ。またiris_dataから取得したデータに対し、obj.head(行数)を用いてヘッダー何行かを出力せよ。
ex04. WHERE, LIMIT： iris_dataのテーブルからclassが1のレコードを取得して件数が50件あることをPandasを用いて確かめよ。また、10件に限定した取得も行い、それもPandasを用いて確かめよ。
ex05. INNER JOIN： iris_dataとiris_masterをclassのカラムで内部結合を行いPandasのデータフレーム形式で保存せよ。また、データの行数と列数をobj.shapeを用いて確認せよ。
ex06. Pandasの検索： ex05で保存したデータにおいてpt_lenが5より大きいデータを出力せよ。
問題は上記の7題になります。どれも基本的な問題になりますが、プログラミングは基本的な要素の組み合わせで表現していくことから基本を抑えるだけでできる幅が一気に広がります。2節ではそれぞれの解答と解説についてまとめていきます。
2. 解答＆解説
・ex01. FizzBuzz問題
Q. 1~100までの数字を出力するにあたって、3の倍数はFizz、5の倍数はBuzz、3の倍数かつ5の倍数はFizzBuzz、それ以外は数字を出力するようなプログラムを作成せよ。
A. 基本的に繰り返し文(for文)と条件分岐(if文)の組み合わせで解くことができます。
上記記事に詳しい解答と解説が載っています。解説が必要ない方向けに解答だけ抜粋します。
for i in range(100):
idx = i+1
if (idx%5==0 and idx%3==0):
print("FizzBuzz")
elif idx%3 == 0:
print("Fizz")
elif idx%5 == 0:
print("Buzz")
else:
print(i+1)
>>> 1, 2, Fizz, 4, Buzz, Fizz,......, Fizz, Buzz
・ex02. SELECT文
Q. iris_dataとiris_masterの双方のテーブルからデータを取得し、Pandasのデータフレーム形式で変数を格納せよ。
A. データの取得にはMySQL(MariaDB)ではSELECT文を用います。PyMySQL部分が複雑に見えますが一旦ブラックボックス的に解釈してしまって良いです。
# Connect to the database
connection = pymysql.connect(host='localhost',
user='root',
password='',
db='sample01',
cursorclass=pymysql.cursors.DictCursor)with connection.cursor() as cursor:
# Get from iris_master
sql = "SELECT * from iris_master;"
cursor.execute(sql)
res_master = pd.DataFrame(cursor.fetchall())
# Get from iris_data
sql = "SELECT * from iris_data;"
cursor.execute(sql)
res_data = pd.DataFrame(cursor.fetchall())
connection.close()
上記を実行することで、res_masterにiris_masterテーブルのデータ、res_dataにiris_dataのデータが格納されます。
・ex03. Pandas
Q. ex02で作成したPandasのインスタンスに対し、type(obj)やobj.shapeでオブジェクトの形式やデータフレームのサイズを求めよ。またiris_dataから取得したデータに対し、obj.head(行数)を用いてヘッダー何行かを出力せよ。
A. Pythonのインスタンス(変数に格納されているもの)の仕様について知りたい際はtype関数を用いるのが非常に便利です。また、PandasやNumPyなどにはobj.shapeというメソッドがありこちらが非常に便利です。解答は下記になります。
print(type(res_master))
print(type(res_data))
print(res_master.shape)
print(res_data.shape)
print(res_data.head(3))
上記を実行することで、下記のような結果を得ることができます。
・ex04. WHERE, LIMIT
Q. iris_dataのテーブルからclassが1のレコードを取得して件数が50件あることをPandasを用いて確かめよ。また、10件に限定した取得も行い、それもPandasを用いて確かめよ。
A. こちらもSQLでのデータ取得の処理とPandasの処理の複合で結果を得ることができます。ここでのポイントとしては、SQL文において条件を指定する際はWHERE句、件数を指定する際はLIMIT句を用いることです。解答例は下記になります。
# Connect to the database
connection = pymysql.connect(host='localhost',
user='root',
password='',
db='sample01',
cursorclass=pymysql.cursors.DictCursor)with connection.cursor() as cursor:
# Get from iris_data
sql = "SELECT * from iris_data where class=1;"
cursor.execute(sql)
res_data_class1 = pd.DataFrame(cursor.fetchall())
sql = "SELECT * from iris_data where class=1 limit 10;"
cursor.execute(sql)
res_data_class1_10 = pd.DataFrame(cursor.fetchall())
connection.close()print(res_data_class1.shape)
print(res_data_class1_10.shape)
print(res_data_class1.head(3))
上記を実行することで下記のような結果を得ることができます。
・ex05. INNER JOIN
Q. iris_dataとiris_masterをclassのカラムで内部結合を行いPandasのデータフレーム形式で保存せよ。また、データの行数と列数をobj.shapeを用いて確認せよ。
A. INNER JOINを使用することでテーブルデータを連結して用いることが可能です。
# Connect to the database
connection = pymysql.connect(host='localhost',
user='root',
password='',
db='sample01',
cursorclass=pymysql.cursors.DictCursor)with connection.cursor() as cursor:
# Get from iris_data
sql = "SELECT * from iris_data as d inner join iris_master as m on d.class=m.class;"
cursor.execute(sql)
res_data = pd.DataFrame(cursor.fetchall())
connection.close()print(res_data.shape)
print(res_data.head(3))
上記を実行することで下記のような結果を得ることができます。
・ex06. Pandasの検索
Q. Pandasの検索： ex05で保存したデータにおいてpt_lenが5より大きいデータを出力せよ。
A. Pandasではオブジェクトに対して条件指定をすることで検索に近い形でデータを抽出することが可能です。
res_data[res_data["pt_len"]>5]
上記を実行することで下記のような結果を得ることができます。
・ex07. CSVへの書き込み
Q. ex06のデータを"out_iris.csv"というファイルでcsvに出力せよ。
A. csvファイルに書き込むにはto_csvメソッドを用います。
上記を実行することでout_iris.csvという名称でcsvファイルを保存することができます。
3. まとめ
上記で触れた内容はプログラミングでよく出てくると思うので、何度か見直して理解しておくと色々なところで役に立つかと思います。
#3ではPythonの実装における工夫について取り扱います。
