後から修正や、追加なども想定しコードを書くときの考え方で コードレビュー

Question

現在、初めてpythonとmysqlを使った開発を行なっています。スクレイピング結果を登録する際の一部でどのように書くのが良いのかについてお聞きしたいです。pythonに限ったことではないと思いますが、ご意見を頂ければ幸いです。

まず、以下のようなitemsのlistを持っているとします。

---

items
|id|user|num|day|num2|
|:--:|:--:|:--:|:--:|
|1|A|1|2020-06-19|77|
|2|B|2|2020-06-14|37|
|3|C|3|2020-06-2|7|
|4|D|4|2020-06-9|1|
|5|E|3|2020-06-29|22|
|6|F|1|2020-06-19|71|

元々のテーブル A
|id|user|num|day|num2|
|:--:|:--:|:--:|:--:|
|1|||||
|2|||||
|3|||||

元々のテーブル B

id	user

---

↓　こうしたい
処理後のテーブル A
|id|user|num|day|num2|
|:--:|:--:|:--:|:--:|
|1|A|1|2020-06-19|77|
|2|B|3|2020-06-14||
|3|C|3|2020-06-2||
|4|D|4|2020-06-9|444|

処理後のテーブル B

id	user
5	E
6	F

---

この場合は必要なSQL文は三つあると思います。
0. テーブルAのINSERT文
0. テーブルAのUPDATE文
0. テーブルBのINSERT文

条件の詳細は今回すいません。

そこでpython側ではこのようにしています。
（ここで間違っていたらすいません。）

def get_date()
    #1. 1つのurlからスクレイピング
    #2. 用途別に格納
 　　return A_insert , A_update , B_insert


A_insert , A_update , B_insert = get_date()

Input_A_insert(A_insert)
Input_A_update(A_update)
Input_B_insert(B_insert)

それぞれの　Input_XXXXXX(XXXXXX)はこのようになっています。

def Input_XXXXXX(XXXXXX):
 　　conn = pymysql.connect(user='xxx',passwd='xxxx',host='xxxxx',db='xxx')
 　　# エラー処理（例外処理）
 　　try:
 　　 　　# カーソルを取得する
 　　 　　cur = conn.cursor()

 　　 　　sql = 'sql文'
 　　 　　cur.execute(sql)
 　　 　　conn.commit()

 　　except pymysql.Error as e:
 　　 　　print('MySQLdb.Error: ', e)
 　　cur.close
 　　conn.close

これからも、基準や項目の増減・変化が見込まれます。
そのため、同じような形式のデータでも、用途別にlistに格納してそれぞれ別の関数で実行を試みています。

そうすると、毎回SQLとの接続を切る形になっているのでPCの負担的にまずのでしょうか？sqlを使用するのはここだけでなく頻繁に使います。

＊ここでもう一度質問を繰り返します。
今後の修正や、追加なども考えた時にどのような構成にするべきなのでしょうか？

Answer 1

改善提案がご所望のようですので回答します。

源泉データは一定期間取っておく

スクレイピングに限りませんが、源泉となるウェブサイトやデータがあり、それを加工してデータベースに登録する場合、デバッグのために源泉データを一定期間保持するのが良いです。html等は圧縮して一定期間は捨てないようにします。急なウェブサイトの変更や503エラー等のエラー時に、なぜデータベースに登録されなかったのか判断するために使用します。またプログラムにバグがあった場合は、ローカルに保存されたhtmlを読み込んでデータベースに流しこむ等ができます。デバッグのためだけにウェブサイトに何度も接続するということがなくなります。

コネクション数を減らす

ご自分でも懸念されている通りInput_XXXXXX()の中でconnect()/close()を繰り返すのはあまり好ましくありません。負担が大きいのもそうですが、オーバーヘッドが大きくトランザクションが増えた場合に対応が難しくなります。

Papared Statementを使う

execute()文を使ってSQLを直接流し混んでいますが、できる限りprepared statementを使って下さい。その方が高速に処理できますし、単純なSQLインジェクションに対してはセキュリティ対策にもなります。

commit回数は減らす

トランザクションが可能な単位にもよりますが、BEGIN~COMMITの塊が大きいほど高速に動作します。例えば下記のような感じでしょうか。

Input_A_insert(conn, A_insert)
Input_A_update(conn, A_update)
Input_B_insert(conn, B_insert)
conn.commit()

テーブルには作成日、更新日を持つ

テーブルには作成日(INSERT文でデータを入れた日)と更新日(UPDATEでデータを更新した日)を格納する列を持つとメンテナンス性が上がります。ただしレコード数が増えると容量が必要になるため、要件によってこのような列を持たないという判断もありです。

ORMの導入を検討する

せっかくpythonを使っているのにORMを使わないのはちょっと勿体無い気がします。ウェブサイト側もpythonならDjangoのORMを、もしそうでないならSQLAlchemyを使うとデータベースやSQLを意識しなくて済みます。ただORMの導入はアンチも多いので周りの顔色を見て、でしょうか。初学者のうちに生のSQLにさわるのは良いと考えています。

Comments

[Yuiti628]

大変親切にありがとうございます。
自分1人ではPapared Statement、ORMの知識が全くないためこれから勉強してきます。

質問なのですが、コネクションを一々切るのと、付けっぱなしはどちらの方がダメですか？

今回開発しているものは全体を通して接続が多いです。アカウントごとの情報が欲しくてスクレイピングをしているところが長い時もあります（time.sleepをしています）。

1. 1個のurlからデータ取得
2. テーブルAを登録
3. 1.で取得したuser_idを元に別のurlにアクセス
4. テーブルBに条件を見て２パターンのどちらかで登録(例であげたように一部だけの追加か全部の追加)
5. 4.のうち条件を満たしているものだけ(全部の追加したもの)を3つ目のurlにアクセスしテーブルCに登録
6. テーブルAとテーブルCの一部を重複しないようにテーブルDに追加
7. 1.〜6.をループ

という流れになります。
今はそれぞれで登録しています。

複雑になりますが、1サイクル終わるまで用途別にそれぞれlistに格納していき最後にまとめてテーブル追加も可能だと思います。
そっちにする方が良いでしょうか？

[yymmt]

> 質問なのですが、コネクションを一々切るのと、付けっぱなしはどちらの方がダメですか？
この手の話は諸説あり、です。コネクションのタイミングやリソースを一々管理するのは面倒なので、DBにはコネクションプールというこれらを管理する機能があります。
「コネクション数を減らす」とセクションにデカデカと書いたわけですが、数年前に「コネクションプール不要論」や「MySQLのオーバーヘッドは小さいと主張する実測者」が現れたりしまして、どちらがベストプラクティスなのか私自身整理がついておりません。しかし誰かに聞かれたときには
- 1プロセス1コネクションを目処にする(付けっ放しにする)
- 可能であればコネクションプールを使え (DBがMySQL以外の場合もありますので)
と言っています。