さてさて、今日はOS関連のネタではなく、個人で運用しているデータベース関連のお話。

データベースを運用していると、稀に重複したデータが登録されていることがある、

業務での設計とは違い、個人で構築管理する場合、例外ケースを排除しきれない場合もある。

今回は、筆者が分析に利用するBitcoinのデータ取得DB上で重複データが見つかった。

そこで、pythonプログラムを用いて重複したデータの削除を行うことにした。

日付と時／分で重複データがあることがわかった。

（原因としてはサーバとのセッションが切れた際などの処理の可能性がある、そこまで考慮していない）

そこで、まずは重複したデータの検知の方法として、以下のSQL構文を用いた。

重複データを検出するSQL構文（group by + having句）

select date,hour,min from [テーブル名] where product_code='BTC_JPY' group by date,hour,min having count(*)>1;

このSQL構文をPythonに組み込むわけであるが、以下のようにした。

尚、pymysqlモジュールを別途インストールする必要があります。

$ python3 -m pip install pymysql
#!/usr/bin/python3
import pymysql.cursors
conn = pymysql.connect(user = 'user',
                        password = 'passpass',
                        host = '192.168.1.xxx',
                        db = 'db_name',
                        charset='utf8mb4')

def search_del_data(product_code):
        con = conn.cursor()
        sql= "select date,hour,min from bfonemin where product_code=%s group by date,hour,min having count(*)>1"
        parm= (product_code)
        con.execute(sql,parm)
        result = con.fetchall()
        print(result)
        con.close()

search_del_data("BTC_JPY")

上記実施の結果は以下のように表示される。

以下はサンプルであり、実際には約２０００レコードあった。

検出の結果データ

(('2021/10/30', '23', '41'), ('2021/10/31', '00', '31'), ('2021/10/31', '00', '34'), ('2021/10/31', '00', '39'), ('2021/10/31', '10', '24'), ('2021/10/31', '13', '16'), ('2021/10/31', '19', '43'), ('2021/10/31', '22', '51'), ('2021/10/31', '23', '25'), ('2021/11/01', '20', '14'), ('2021/11/01', '21', '14'), ('2021/11/01', '22', '07'), ('2021/11/01', '22', '33'), ('2021/11/03', '04', '59'), ('2021/11/03', '05', '56'), ('2021/11/03', '08', '01'), ('2021/11/03', '11', '13'), ('2021/11/03', '11', '37'), ('2021/11/03', '16', '36'), ('2021/11/03', '16', '43'), ('2021/11/03', '17', '32'), ('2021/11/03', '18', '46'), ('2021/11/03', '18', '48'), ('2021/11/03', '20', '02'), ('2021/11/03', '20', '30'), ('2021/11/03', '20', '40'), ('2021/11/03', '20', '42'), ('2021/11/03', '21', '20'), ('2021/11/03', '21', '49'), ('2021/11/03', '22', '15'), ('2021/11/03', '22', '27'), ('2021/11/03', '23', '06'))

２０００レコードも手動で削除するわけにはいかない。
そこで、上記のコードを修正した。

検出結果をリストに格納し、DBから削除する

検出する関数（search_del_data）の他に、削除する関数(del_data)を作成。

len(result)で検出結果の数を取得し、それを元にwhile文で削除関数へ。

削除関数は日付データを元にkey となるIDを出力し、IDを元に削除。

これを繰り返すことで一括で削除を実施することができた。

削除作業を手動で行うことを考えたら、かなりの工数削減である。

#!/usr/bin/python3
import pymysql.cursors
conn = pymysql.connect(user = 'user',
                        password = 'passpass',
                        host = '192.168.1.xxx',
                        db = 'db_name',
                        charset='utf8mb4')
#
def search_del_data(product_code):
        con = conn.cursor()
        sql= "select date,hour,min from bfonemin where product_code=%s group by date,hour,min having count(*)>1"
        parm= (product_code)
        con.execute(sql,parm)
        result = con.fetchall()
        print(len(result))
        i=0
        while i < len(result):
            del_date=result[i][0]
            del_hour=result[i][1]
            del_min=result[i][2]
            i+=1
            print(del_date,del_hour,del_min)
            del_data(product_code,del_date,del_hour,del_min)
        con.close()
#
def del_data(product_code,date,hour,min):
        con = conn.cursor()
        sql="select id from bfonemin where product_code=%s and date =%s and hour=%s and min = %s"
        parm=(product_code,date,hour,min)
        con.execute(sql,parm)
        result = con.fetchall()
        if len(result) > 1:
            delete_id=result[0][0]
            print(delete_id)
            sql = "delete from bfonemin where id =%s"
            con.execute(sql,delete_id)
            conn.commit()
       return result
        con.close()
#
search_del_data('BTC_JPY')
search_del_data('ETH_JPY')
#

尚、筆者の場合は重複を1分毎に発見、削除できる仕組みを作るので、

このプログラムは役目は終えたわけですが、何かの参考になれば幸いです。

Pythonでメール送信する方法
Programming