pythonでテーブルを周期的にバックアップ

概要

今日は毎日分単位でデータを登録しているテーブルを毎月1日～15日と16日～月末日までのデータを分けてバックアップするテーブル作成とレコードを登録するバッチを作成します。

事前に必要なSQLコマンド

下記のSQLコマンドをプログラムで使用します。

①作成するテーブルが存在する場合、削除します。
「datatbl_xxxxxxxx」が存在する場合、テーブルを削除します。
コマンドの例）drop table if exists datatbl_xxxxxxxx

②コピー元のテーブルからテーブルの構成をコピーします。
old_tblの構成をnew_tbl名にコピーします。
この場合、old_tblに登録されているレコードはコピーされないです。
コマンドの例）create table new_tbl like old_tbl

③コピー元のテーブルから特定条件でレコードを抽出してコピー先のテーブルに登録します。
old_tblから1日から15日までのレコードを抽出してコピー先のテーブルに登録します。
コマンドの例）insert into new_tbl_20210215 select * from old_tbl where timestamp >= '2021-02-01' and timestamp <='2021-02-15';

④テーブルのすべてのレコードを削除します。
「datatbl」テーブルのすべてのレコードを削除します。
コマンドの例）delete from datatbl;

※テーブル名を変更したい場合は下記のコマンドでテーブル変更ができます。
既存のテーブル名である「new_tbl_20210217」を「new_tbl_20210215」に変更します。
コマンドの例）rename table new_tbl_20210217 to new_tbl_20210215;

ソース

毎月の開始日、終了日を取得します。
取得したその期間のデータをバックアップする前にバックアップするテーブルを作成します。
既に存在するテーブル名の場合は、テーブルを削除してから作成します。
作成したテーブルにデータをバックアップした後、既存のテーブルからすべてのレコードを削除します。
こうすると既存の画面のソースは同じテーブルを参照しているので、修正は不要です。
ただ、バックアップしたテーブルから照会する画面を作成する場合、期間を指定してデータを抽出するようにパラメータを指定とパラメータからテーブルの指定ができるように工夫する必要があります。
例）バックアップする対象データは1日から15日または16日から月末日のデータです。

#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb
import calendar
import datetime
from datetime import datetime

###############
#### const ####
###############
host = "localhost"
user = "db user id"
passwd = "db user pw"
db = "db name"
const_day = 15
###############

def getStartDate(today):
    global const_day
    if(today==const_day):
        start_date = datetime.today().strftime("%Y-%m-01") 
        print("start_date=" + str(start_date))
        return start_date 
    elif(today>const_day):
        start_date = datetime.today().strftime("%Y-%m-16") 
        print("start_date=" + str(start_date))
        return start_date 
    else:
        print("start_date=")
        return ""


def getEndDate(today):
    global const_day
    if(today==const_day):
        end_date = datetime.today().strftime("%Y-%m-15")
        print("end_date=" + str(end_date))
        return end_date 
    elif(today>const_day):
        year = datetime.today().strftime("%Y")
        month = datetime.today().strftime("%m")
        day = datetime.today().strftime("%d")
        end_date = datetime(int(year),int(month),int(day)).date().strftime("%Y-%m-%d")
        print("end_date=" + str(end_date))
        return end_date 
    else:
        print("end_date=")
        return ""


def create_tbl(tbl):
    global const_day
    try:
        year = datetime.today().year
        month = datetime.today().month
        last_day = calendar.monthrange(year,month)[1]
        conn = MySQLdb.connect (host=host, user=user, passwd=passwd, db=db, charset="utf8")
        cursor = conn.cursor()
        today_day = datetime.today().day
        tbl_date = datetime.today().strftime("%Y%m%d") 
        start_date = getStartDate(today_day)
        end_date = getEndDate(today_day)
        if(start_date != "" and end_date != ""):
            if(const_day == today_day or last_day == today_day): 
                query = "drop table if exists " + tbl + "_" + str(tbl_date) + ";"
                print(query)
                cursor.execute(query)
                conn.commit()
                query = "create table " + tbl + "_" + str(tbl_date) + " like " + tbl + ";"
                print(query)
                cursor.execute(query)
                conn.commit()
                query = "insert into " + tbl + "_" + str(tbl_date) + " select * from " + tbl + " where timestamp >= '" + start_date + "' and timestamp <='" + end_date +"';"
                print(query)
                cursor.execute(query)
                query = "delete from " + tbl + ";"
                print(query)
                cursor.execute(query)
                conn.commit()
            else:
               print("no table create.")
        else:
            print("no table create.")

    except Exception as e:
        print("#######################")
        print("# Error >>>>>>>>>>>>>>>")
        print(e)
        print("#######################")
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()


def main():
    tbl_list = ['datatbl'] 
    # 複数のテーブルをバックアップしたい場合は、この配列に指定すればできると思います。

    for tbl in tbl_list:
        create_tbl(tbl)

if __name__ == '__main__':
    main()

実行結果

テーブルが作成できる場合と作成できない場合の実行結果は下記のように表示されます。

#テーブルが作成できる場合
pi@raspberry:/home/pi# python create_tbl.py                                          
start_date=2021-02-01
end_date=2021-02-15
drop table if exists datatbl_20210215;
create table datatbl_20210215;
insert into datatbl_20210215 select * from datatbl where timestamp >= '2021-02-01' and timest
amp <='2021-02-16';
delete from datatbl;

pi@raspberry:/home/pi# python create_tbl.py                                          
start_date=2021-02-16
end_date=2021-02-28
drop table if exists datatbl_20210228;
create table datatbl_20210228;
insert into datatbl_20210228 select * from datatbl where timestamp >= '2021-02-16' and timest
amp <='2021-02-28';
delete from datatbl;

#テーブルが作成できない場合
pi@raspberry:/home/pi# python create_tbl.py
start_date=2021-02-16
end_date=2021-02-18
no table create.

#テーブルが作成できた場合、mysqlでテーブルを確認します。
mysql> show tables;
+----------------------+
| Tables_in_data       |
+----------------------+
| datatbl              |
| datatbl_20210215     |
+----------------------+
2 rows in set (0.00 sec)

mysql>

周期的に実行するため、cronに登録

cronに登録するためには下記のコマンドから登録ができます。

#実行するコマンド
crontab -e

#エディタ画面で下記のcronを登録します。
59 23 * * * python /home/pi/create_tbl.py

終わりに

WEB画面で参照しているテーブルにレコード数が20万件以上登録されているとWEBのシステムが重くなって画面のレスポンスが遅くなりますので、テーブルを周期的にバックアップするため、コードを作成しました。
実はSQLをまとめて実行したかったですが、テーブルを作成するSQLとデータを登録するSQLを同時に入れるとエラーが発生しましたので、SQLを分けて実行するようにしました。
もっとよい方法があると思いますが、今の練習コードはここまでです。
ありがとうございます。