Pythonで MariaDBから古いデータを削除し CSVにエクスポートする

Ubuntu Desktop 22.04 LTS ノウハウ
Python + MariaDB
古いデータを削除し CSVにエクスポート

Ubuntu 22.04 の環境で、Pythonを使用してMariaDBのテーブルから1カ月以上経過した古いデータを削除し、そのデータをCSVファイルにエクスポートする。

以下、MariaDBから古いデータをクリーンアップした記録。

　目　次

1.　必要なライブラリ
2.　Pythonスクリプト
3.　スクリプトの説明
4.　定期的なクリーニング

1. 必要なライブラリ

この作業を行うためには、Pythonにmysql-connector-pythonというライブラリがインストールされている必要がある。

インストールコマンド。

pip install mysql-connector-python

2. Pythonスクリプト

必要なライブラリをインポートし、MariaDBに接続する。

# -*- coding: utf-8 -*-

import mysql.connector
from mysql.connector import Error
import csv
from datetime import datetime, timedelta

# MariaDBの接続設定
config = {
    'user': 'ユーザー名',
    'password': 'パスワード',
    'host': 'localhost',
    'database': 'データベース名',
    'raise_on_warnings': True,
}

try:
    # データベース接続
    connection = mysql.connector.connect(**config)
    if connection.is_connected():
        print('MariaDBに接続しました。')
        cursor = connection.cursor()

        # 現在日より1カ月以上前の日付を計算
        one_month_ago = datetime.now() - timedelta(days=30)

        # 古いデータを選択するクエリ
        select_query = f"""
        SELECT * FROM テーブル名
        WHERE 日付カラム < '{one_month_ago.strftime('%Y-%m-%d')}';
        """

        # 選択したデータをCSVに書き出す
        cursor.execute(select_query)
        result = cursor.fetchall()
        with open('expired_data.csv', 'w', newline='') as f:
            writer = csv.writer(f)
            # CSVヘッダーを書き込む
            writer.writerow([i[0] for i in cursor.description])
            # データを書き込む
            writer.writerows(result)
        
        print('CSVファイルに書き出しました。')

        # 古いデータを削除するクエリ
        delete_query = f"""
        DELETE FROM テーブル名
        WHERE 日付カラム < '{one_month_ago.strftime('%Y-%m-%d')}';
        """
        
        # データを削除
        cursor.execute(delete_query)
        connection.commit()
        print(f"{cursor.rowcount}行のデータを削除しました。")

except Error as e:
    print(f"エラーが発生しました: {e}")
finally:
    if (connection.is_connected()):
        cursor.close()
        connection.close()
        print('MariaDBの接続を閉じました。')

実行結果：削除された行数が表示される。

CSVファイルにエクスポートされた古いデータ。

CSVファイルのヘッダー行として「列の名前」が最初の行に書き込まれている。

3. スクリプトの説明

上記スクリプトは、指定したテーブルから1カ月以上前のデータを選択し、それをCSVファイルに保存した後、そのデータをテーブルから削除します。

現在日より1カ月以上前の日付を計算：timedeltaを使う。

        # 現在日より1カ月以上前の日付を計算
        one_month_ago = datetime.now() - timedelta(days=30)

古いデータを選択するクエリ：SELECT。

        # 古いデータを選択するクエリ
        select_query = f"""
        SELECT * FROM meas_value
        WHERE nitiji < '{one_month_ago.strftime('%Y-%m-%d')}';
        """

選択した古いデータを、CSVに書き出す箇所。
・open()の第一引数には、CSVファイルへのパスを指定する。
　第二引数は「mode」で、書き込みモードを'w'とすると上書きとなり、元の内容は削除される。
・cursor.description から各列の名前 (name属性) を抽出し、それをCSVファイルのヘッダーとして書き込むための行で、列の名前がCSVファイルのヘッダー行として最初の行に書き込まれる。

        # 選択したデータをCSVに書き出す
        cursor.execute(select_query)
        result = cursor.fetchall()
        with open('work/expired_data_01.csv', 'w', newline='') as f:
            writer = csv.writer(f)
            # CSVヘッダーを書き込む
            writer.writerow([i[0] for i in cursor.description])
            # データを書き込む
            writer.writerows(result)

2回目以降、既存のCSVファイルに追記したい場合は、 open()の追記モード‘a’ でファイルを開く。

        # 選択したデータをCSVに書き出す
        cursor.execute(select_query)
        result = cursor.fetchall()
        with open('work/expired_data_01.csv', 'a', newline='') as f:
            writer = csv.writer(f)
            # データを書き込む
            writer.writerows(result)

古いデータを削除するクエリ：DELETE 。

        # 古いデータを削除するクエリ
        delete_query = f"""
        DELETE FROM meas_value
        WHERE nitiji < '{one_month_ago.strftime('%Y-%m-%d')}';
        """

データを削除：executeメソッドでSQL文の実行。
「cursor.rowcount」で、削除された行数をカウント。

        # データを削除
        cursor.execute(delete_query)
        connection.commit()
        print(f"{cursor.rowcount}行のデータを削除しました。")

4. 定期的なクリーニング

この作業を「cron」で定期的に実行することで、データベースを最適な状態に保つことができる。

以上。
(2024.02.28)

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル