実験管理用 MySQL server

[matsuken92]

参考： http://kageura.hatenadiary.jp/entry/2018/02/21/GCP%EF%BC%88Google_Cloud_Platform%EF%BC%89%E3%81%A7_MySQL%E3%82%92%E6%A7%8B%E6%88%90%E3%81%97%E3%81%A6%E3%81%BF%E3%82%8B

python用 driverなどインストール

!sudo apt-get -y install libmysqlclient-dev
!pip install mysql-connector-python-rf
!pip install mysqlclient

[matsuken92]

実験結果DBチェック&格納クラス

import MySQLdb
class ExperimentManagement():
    def __init__(self):
        # 接続する
        self.conn = MySQLdb.connect(
                    user='kenichi.matsui',
                    passwd='qwer1234',
                    host='35.230.5.217',
                    db='experiment_mgmt')
        self.cursor = self.conn.cursor()

    def __del__(self):
        self.conn.close

    def insert_experiment(self, data_version, trial_no, seed, type_, verbose=False):
        select_table_sql = f"""select * from experiment
                            WHERE 
                            data_version='{data_version}' and 
                            trial_no='{trial_no}' and
                            seed={seed} and
                            type={type_}
        """
        n_select = self.cursor.execute(select_table_sql)
        if n_select > 0:
            if verbose:
                print(f"duplicated:", data_version, trial_no, seed, type_ )
            return

        insert_record_sql = f"""INSERT INTO experiment values (
                            '{data_version}', 
                            '{trial_no}',
                            {seed},
                            {type_},
                             NULL,
                             NULL
                            )""".replace("\n", " ")

        self.cursor.execute(insert_record_sql)
        self.conn.commit()

    def delete_experiment(self, data_version, trial_no, seed, type_):
        delete_record_sql = f"""DELETE FROM experiment
                            WHERE 
                            data_version='{data_version}' and 
                            trial_no='{trial_no}' and
                            seed={seed} and
                            type={type_}
                            """.replace("\n", " ")

        self.cursor.execute(delete_record_sql)
        self.conn.commit()

    def add_start_time(self, data_version, trial_no, seed, type_, start_date):
        if isinstance(start_date, datetime.datetime):
            start_date = start_date.strftime("%Y-%m-%d %H:%M:%S")
        elif not isinstance(start_date, str):
            Exception(f"start_date should be datetime.datetime or str: {start_date}")

        update_sql = f"""UPDATE experiment
        SET start_date = cast('{start_date}' as datetime)
        WHERE 
        data_version='{data_version}' and 
        trial_no='{trial_no}' and
        seed={seed} and
        type={type_}
        """
        self.cursor.execute(update_sql)
        self.conn.commit()

    def add_end_time(self, data_version, trial_no, seed, type_, end_date):
        if isinstance(end_date, datetime.datetime):
            end_date = end_date.strftime("%Y-%m-%d %H:%M:%S")
        elif not isinstance(end_date, str):
            Exception(f"end_date should be datetime.datetime or str: {end_date}")

        update_sql = f"""UPDATE experiment
        SET end_date = cast('{end_date}' as datetime)
        WHERE 
        data_version='{data_version}' and 
        trial_no='{trial_no}' and
        seed={seed} and
        type={type_} and
        start_date is not NULL
        """
        self.cursor.execute(update_sql)
        self.conn.commit()

    def select_all(self):
        select_table_sql = """select * from experiment"""
        df_read = pd.read_sql(select_table_sql, self.conn)
        return df_read

    def select_new_experiment(self):
        # まだ実行していない実験を取得
        select_table_sql = f"""select * from experiment
                            WHERE 
                            start_date is NULL and
                            end_date is NULL
        """
        df_read = pd.read_sql(select_table_sql, self.conn)
        # assert False, "not implemented"
        return df_read

    def select_expired_experiment(self, hours=3):
        # 現在時間からhours時間経過した実験を取得
        select_table_sql = f"""select * from experiment
                            WHERE 
                            start_date is not NULL and
                            end_date is NULL
        """
        df_read = pd.read_sql(select_table_sql, self.conn)
        if len(df_read)==0:
            print("there is no expired experiment.")
            return None
        df_cut = (dt.now() - df_read.start_date).dt.seconds > hours*60 # hours時間以上
        return df_read[df_cut]

[matsuken92]

python mysql tips https://www.w3schools.com/python/python_mysql_getstarted.asp

dbeaver: https://dbeaver.io/download/

[matsuken92]

デモンストレーション Notebook： https://github.com/matsuken92/molecular/blob/master/notebook/mysql_experiments_mgmt_demo.ipynb

mysql_experiments_mgmt_demo.pdf

[matsuken92]

import socket
# ホスト名を取得、表示
host = socket.gethostname()
print(host)

# ipアドレスを取得、表示
ip = socket.gethostbyname(host)
print(ip) # 192.168.○○○.○○○

pe-kaggle-03
10.138.0.14

[matsuken92]

#!/bin/bash

script_name="train_v003_052.py"
ps aux | grep ${script_name} | awk 'NR==1' | grep "python -u ${script_name}"

[matsuken92]

Daemon化するといいかも。

[/etc/systemd/system/mol_train.service]

[Unit]
Description = train_v003_057

[Service]
WorkingDirectory=/home/kenichi.matsui/work/molecular/src
ExecStart=/home/kenichi.matsui/work/molecular/src/train_v003_057.py 
User=kenichi.matsui
Restart = always
Type = simple

[Install]
WantedBy = multi-user.target

sudo systemctl enable mol_train.service
sudo systemctl start mol_train.service
# 確認は以下のコマンドで
sudo systemctl status mol_train.service

log: cat /tmp/mol_train.log