そうだ、SAVEPOINTがあるじゃないか

　先日より、Pythonを勉強してWebサイトを巡回して必要なデータを収集するWebスクレイピングのプログラムを書き上げました。Webサイトを巡回して収集したデータはPythonのSQLite3ライブラリを通じてデータベースに記録しています。
　今、そのプログラムをブラッシュアップしているところです。ブラッシュアップしている中で気づいたことがあります。あるサイトでデータを収集している途中で、何かエラーが発生した場合のトランザクション処理を明確にしていませんでした。

　例えば、Aサイトを巡回し終わり、Bサイトを巡回している途中でエラーが発生した場合、途中まで収集したBサイトの今日のデータはキャンセル、つまりロールバックしてAサイトの有効なデータのみをコミットするということをしたいのです。
　これだけであれば簡単なのですが、頻繁にWebスクライピングしないようにするために、サイトごとに巡回した日時を記録しつつも、その記録した日時の情報はコミットする、というようなことも合わせてしたいと考えています。

　つまり、一番外枠に巡回した日時を記録するトランザクションが走り、その中に、Aサイト、Bサイトごとにトランザクションが走るというようなイメージになるかと思います。トランザクションのネストです。

　しかし、Python＋SQLite3ですぐに思いつく、BEGIN TRANSACTION〜COMMIT TRANSACTIONだけでは、そのようなトランザクションのネストは実現することができません。
　どうしようかと悩んでいた時に思い出したキーワードがあります。それが「SAVEPOINT」です。SAVEPOINTは、BEGIN TRANSACTION〜COMMIT TRANSACTIONと同じトランザクションを制御するのですが、ネストができるのです。

　Pythonで簡単なサンプルプログラムを作ってみました。

import sqlite3
import datetime

def a_site(cur):
   try:
       cur.execute('SAVEPOINT a_site')
       cur.execute('INSERT INTO テストテーブル (サイト, 番号, データ) VALUES (?, ?, ?)', ('サイトA', 1001, 'dummy data 1001'))
       cur.execute('INSERT INTO テストテーブル (サイト, 番号, データ) VALUES (?, ?, ?)', ('サイトA', 1002, 'dummy data 1002'))
       # 次のデータはプライマリーキーが重複しているためエラーとなる
       cur.execute('INSERT INTO テストテーブル (サイト, 番号, データ) VALUES (?, ?, ?)', ('サイトA', 1001, 'dummy data 1001'))
       cur.execute('RELEASE SAVEPOINT a_site')
   except Exception as e:
       cur.execute('ROLLBACK TO SAVEPOINT a_site')
       raise Exception

def b_site(cur):
   try:
       cur.execute('SAVEPOINT b_site')
       cur.execute('INSERT INTO テストテーブル (サイト, 番号, データ) VALUES (?, ?, ?)', ('サイトB', 2001, 'dummy data 2001'))
       cur.execute('INSERT INTO テストテーブル (サイト, 番号, データ) VALUES (?, ?, ?)', ('サイトB', 2002, 'dummy data 2002'))
       cur.execute('INSERT INTO テストテーブル (サイト, 番号, データ) VALUES (?, ?, ?)', ('サイトB', 2003, 'dummy data 2003'))
       cur.execute('RELEASE SAVEPOINT b_site')
   except Exception as e:
       cur.execute('ROLLBACK TO SAVEPOINT b_site')
       raise Exception

if __name__ == '__main__':

   con = sqlite3.connect('test.db')
   cur = con.cursor()

   cur.execute('CREATE TABLE IF NOT EXISTS 最終アクセス日時' + \
               ' (サイト NOT NULL PRIMARY KEY, 最終アクセス開始日時 NOT NULL, 最終アクセス終了日時, アクセス結果)'
   )
   cur.execute('CREATE TABLE IF NOT EXISTS テストテーブル' + \
               ' (サイト NOT NULL, 番号 NOT NULL, データ NOT NULL, PRIMARY KEY (サイト, 番号))'
   )

   # トランザクション"zentai" 開始
   cur.execute('BEGIN TRANSACTION zentai')
   # テストのために一度テストテーブルからデータを削除する
   cur.execute('DELETE FROM テストテーブル')

   # サイトA
   try:
       cur.execute('INSERT OR REPLACE INTO 最終アクセス日時 VALUES (?, ?, NULL, NULL)',
           ('サイトA', f'{datetime.datetime.now():%Y-%m-%d %H:%M:%S}')
       )
       a_site(cur)
       cur.execute('UPDATE 最終アクセス日時 SET 最終アクセス終了日時 = ?, アクセス結果 = ? WHERE サイト = ?',
           (f'{datetime.datetime.now():%Y-%m-%d %H:%M:%S}', 'SUCCESS', 'サイトA')
       )
   except Exception as e:
       cur.execute('UPDATE 最終アクセス日時 SET 最終アクセス終了日時 = ?, アクセス結果 = ? WHERE サイト = ?',
           (f'{datetime.datetime.now():%Y-%m-%d %H:%M:%S}', 'FAILURE', 'サイトA')
       )

   # サイトB
   try:
       cur.execute('INSERT OR REPLACE INTO 最終アクセス日時 VALUES (?, ?, NULL, NULL)',
           ('サイトB', f'{datetime.datetime.now():%Y-%m-%d %H:%M:%S}')
       )
       b_site(cur)
       cur.execute('UPDATE 最終アクセス日時 SET 最終アクセス終了日時 = ?, アクセス結果 = ? WHERE サイト = ?',
           (f'{datetime.datetime.now():%Y-%m-%d %H:%M:%S}', 'SUCCESS', 'サイトB')
       )
   except Exception as e:
       cur.execute('UPDATE 最終アクセス日時 SET 最終アクセス終了日時 = ?, アクセス結果 = ? WHERE サイト = ?',
           (f'{datetime.datetime.now():%Y-%m-%d %H:%M:%S}', 'FAILURE', 'サイトB')
       )

   # トランザクション"zentai" 終了
   cur.execute('COMMIT TRANSACTION zentai')

   cur.close()
   con.close()

　横に長くてみづらくなってしまって、すみません。

　簡単にフローを説明すると、

１．test.dbというデータベースを作成し、最終アクセス日時というテーブルと、テストテーブルというテーブルを作成します。最終アクセス日時というテーブルは、サイトを巡回したときの日時を記録します。テストテーブルは、サイトを巡回して収集するデータを記録するテーブルとご理解ください。
２．Aサイトを巡回する前に、巡回する日時を記録します。
３．Aサイトを巡回し、データを順調に収集するのですが、わざとエラーが発生するようにしています。エラーが発生した場合、Aサイトで収集したデータは全てキャンセル（ロールバック）して、エラーが発生したことをメインのモジュールへ伝えます。メインのモジュールでは、Aサイトの巡回に失敗したことを記録します。
４．次にBサイトを巡回します。Bサイトのデータ収集は問題なく収集できたため、エラーなくメインのモジュールへ戻ります。メインのモジュールでは、Bサイトの巡回に成功したことを記録します。
５．全体を通じてトランザクションのコミットを実行します。

　全体のトランザクションを制御するために、BEGIN TRANSACTIONをメインのモジュールの最初に入れます。次に、サイトごとに巡回したことを記録するための開始日時を記録します。
　次に、サイトごとに関数で巡回する処理を行う、ということをするのですが、巡回する際に最初にSAVEPOINTを実行することでネストしたトランザクションを開始してあげるわけです。もし、エラーが発生しなければ、RELEASE SAVEPOINTでSAVEPOINTの開始位置を削除して、そこには戻れないようにします。
　もし、エラーが発生すれば、ROLLBACK TO SAVEPOINTでSAVEPOINTの開始位置まで処理した内容をキャンセル（ロールバック）して戻ります。
　関数でサイトごとの巡回が完了すれば、メインのモジュールへ戻るのですが、そこで関数内でエラーが発生していなければ、巡回が正常に終了したことを終了日時とSUCCESSという情報を記録します。
　もし、関数内でエラーが発生すれば、巡回が異常終了したことを終了日時とFAILUREという情報を記録します。

　このようにすると、全体のトランザクションの制御はいつも通り、BEGIN TRANSACTION〜COMMIT TRANSACTIONで制御しつつも、その中の細かな制御はSAVEPOINTをそれぞれ設置することで細かく制御することができるということです。

　SAVEPOINTをすぐに思い出せたからよかったものの、思い出せなければ、BEGIN TRANSACTION〜COMMIT TRANSACTIONを何度も繰り返しながら、細かくトランザクションを細分化するという不細工なプログラムを書いてしまうところでした。

　この記事が、Python＋SQLite3をもう少し上手く制御したいと思われている方の参考になれば幸いです（もっと上手い制御方法があるというコメントでの突っ込みもお待ちしております）。