import pymysql import pandas as pd from datetime import datetime ########################################################################################## ### 関数 ########################################################################################## def get_sisetu_cd(facility_name): match = institution_df[institution_df["institution_name"] == facility_name] if not match.empty: return match.iloc[0]["SISETU_CD"] match_like = institution_df[institution_df["institution_name"].str.contains(facility_name, na=False)] if not match_like.empty: return match_like.iloc[0]["SISETU_CD"] return "700000" def reset_tables(cursor): ordered_tables = [ 'T_IJI_MENEKI_YOKUSEI_R_LIV', 'T_GAPPEI_R_LIV', 'T_KANSEN_R_LIV', 'T_KENSA_R_LIV', 'T_LIVING_D_LIV', 'T_DONOR_KIDNEY_LIV', 'T_DONOR_LUNG_LIV', 'T_GAPPEI_D_LIV', 'T_DONOR_LIV', 'T_ISHOKU_KIHON_KIDNEY_LIV', 'T_ISHOKU_KIHON_LIV' ] # optional: 外部キーを一時的に無効化してもよい（開発時のみ） cursor.execute("SET FOREIGN_KEY_CHECKS=0;") for table in ordered_tables: cursor.execute(f"DELETE FROM {table};") cursor.execute(f"ALTER TABLE {table} AUTO_INCREMENT = 1;") cursor.execute("SET FOREIGN_KEY_CHECKS=1;") print("✅ 外部キー制約を考慮したテーブル初期化完了") def render_sql(sql_template, values): # 単純な置換（%sを各値に） parts = sql_template.split("%s") rendered = "" for i, part in enumerate(parts[:-1]): rendered += part + repr(values[i]) rendered += parts[-1] return rendered ########################################################################################## ### メイン処理 ########################################################################################## ## CSVファイルとエクセルファイルの読み込み institution_df = pd.read_csv("/csv/liver/shisetsu.csv", sep="\t", dtype=str).fillna("") institution_df = institution_df[institution_df['移植登録ID'].astype(int).between(1, 100)] donor_df = pd.read_csv("/csv/kan/d-followups.csv", encoding="cp932", dtype=str).fillna("") recipient_df = pd.read_csv("/csv/kan/r-followups.csv", encoding="cp932", dtype=str).fillna("") transplants_df = pd.read_csv("/csv/kan/transplants.csv", encoding="cp932", dtype=str).fillna("") transplants_df['ISYOKU_ISYOKUSISETU_CD'] = transplants_df['施設名'].apply(get_sisetu_cd) mapping_dict = pd.read_excel("/csv/kan/移行データ対応表.xlsx", sheet_name=None, header=1) df_mapping = mapping_dict["腎臓　生体"] ## 登録対象テーブル target_tables = [ 'T_ISHOKU_KIHON_LIV', 'T_ISHOKU_KIHON_KIDNEY_LIV', 'T_DONOR_LIV', 'T_DONOR_KIDNEY_LIV', 'T_LIVING_D_LIV', 'T_KENSA_R_LIV', 'T_KANSEN_R_LIV', 'T_GAPPEI_R_LIV', 'T_IJI_MENEKI_YOKUSEI_R_LIV', ] db_config = { "host": "db", # コンテナ名 "user": "root", "password": "123456", "database": "dev_tracer_db2", "charset": "utf8mb4", "cursorclass": pymysql.cursors.DictCursor } connection = pymysql.connect(**db_config) with connection.cursor() as cursor: reset_tables(cursor) for transplant_index, transplant_raw in transplants_df.iterrows(): for target_table in target_tables: filtered_mapping = df_mapping[df_mapping["テーブル物理名"] == target_table] if target_table == "T_ISHOKU_KIHON_LIV": insert_columns = ["ISYOKU_ISYOKUSISETU_CD", "DEL_FLG", "INS_USER_ID", "INS_PROGRAM_ID"] insert_values = [ transplant_raw["ISYOKU_ISYOKUSISETU_CD"], # 例：施設コード（上で変換済） 0, # DEL_FLG（削除フラグ） 1, # INS_USER_ID（登録ユーザ） 1 # INS_PROGRAM_ID（登録プログラム） ] sql = f""" INSERT INTO T_ISHOKU_KIHON_LIV ({",".join(insert_columns)}) VALUES ({",".join(["%s"] * len(insert_columns))}) """ print("▶ 実行SQL:", render_sql(sql, insert_values)) cursor.execute(sql, insert_values) connection.commit() seitai_ishoku_id = cursor.lastrowid print(f"✅ 登録完了：SEITAI_ISYOKU_ID = {seitai_ishoku_id}") # TRACER_ID生成 organ_code = "3" # 肝臓 transplant_type_code = "1" # 生体間移植 # 移植日 → 年取得（8桁: YYYYMMDD → YYYY） year = transplant_raw["移植日"][:4] if "移植日" in transplant_raw and transplant_raw["移植日"] else "2024" # 同年・同TRACERプレフィックスの最大連番取得 prefix = f"{organ_code}{transplant_type_code}{year}" cursor.execute( "SELECT MAX(TRACER_ID) AS max_id FROM T_ISHOKU_KIHON_LIV WHERE TRACER_ID LIKE %s", (f"{prefix}____",) ) result = cursor.fetchone() if result["max_id"]: last_seq = int(result["max_id"][-4:]) next_seq = last_seq + 1 else: next_seq = 1 tracer_id = f"{prefix}{next_seq:04d}" # TRACER_ID更新 cursor.execute( "UPDATE T_ISHOKU_KIHON_LIV SET TRACER_ID = %s WHERE SEITAI_ISYOKU_ID = %s", (tracer_id, seitai_ishoku_id) ) elif table_name == 'T_ISHOKU_KIHON_LIVER_LIV': # 必須カラムだけでINSERT required_columns = ["SEITAI_ISYOKU_ID", "INS_USER_ID", "INS_PROGRAM_ID"] insert_values = [seitai_ishoku_id, 1, 1] try: sql = f""" INSERT INTO T_ISHOKU_KIHON_LIVER_LIV ({','.join(required_columns)}) VALUES ({','.join(['%s'] * len(required_columns))}) """ cursor.execute(sql, insert_values) #print(f"✅ T_ISHOKU_KIHON_LIVER_LIV に登録完了: SEITAI_ISYOKU_ID={seitai_ishoku_id}") except Exception as e: print(f"❌ INSERT失敗: T_ISHOKU_KIHON_LIVER_LIV, エラー: {e}") error_logs.append({ "テーブル名": "T_ISHOKU_KIHON_LIVER_LIV", "SQL": sql, "入力値": insert_values, "エラー内容": str(e) }) connection.rollback() continue # 処理を次に進める（任意） update_table( cursor=cursor, connection=connection, table_name="T_ISHOKU_KIHON_LIVER_LIV", column_series=column_series, data_row=row, id_columns="SEITAI_ISYOKU_ID", id_values=seitai_ishoku_id, ishoku_toroku_id=ishoku_toroku_id, recipient_rows=recipient_rows, donor_rows=donor_rows ) elif table_name == 'T_DONOR_LIV': # 仮登録せずに、先に ID を生成する cursor.execute("SELECT MAX(DONOR_ID) AS max_id FROM T_DONOR_LIV") result = cursor.fetchone() next_donor_id = int(result['max_id']) + 1 if result['max_id'] else 1 donor_id_str = str(next_donor_id).zfill(7) try: insert_columns = ["DONOR_ID", "SEITAI_ISYOKU_ID", "INS_USER_ID", "INS_PROGRAM_ID"] insert_values = [donor_id_str, seitai_ishoku_id, 1, 1] sql = f""" INSERT INTO T_DONOR_LIV ({','.join(insert_columns)}) VALUES ({','.join(['%s'] * len(insert_columns))}) """ cursor.execute(sql, insert_values) connection.commit() donor_a_id = cursor.lastrowid #print(f"✅ T_DONOR_LIV 登録完了: DONOR_ID={donor_id_str}, DONOR_A_ID={donor_a_id}") except Exception as e: print(f"❌ INSERT失敗: T_DONOR_LIV, エラー: {e}") error_logs.append({ "テーブル名": "T_DONOR_LIV", "SQL": sql, "入力値": insert_values, "エラー内容": str(e) }) connection.rollback() continue # 処理を次に進める（任意） update_table( cursor=cursor, connection=connection, table_name="T_DONOR_LIV", column_series=column_series, data_row=row, id_columns="DONOR_A_ID", id_values=donor_a_id, ishoku_toroku_id=ishoku_toroku_id, recipient_rows=recipient_rows, donor_rows=donor_rows ) elif table_name == 'T_DONOR_LIVER_LIV': try: insert_columns = ["SEITAI_ISYOKU_ID", "DONOR_A_ID", "INS_USER_ID", "INS_PROGRAM_ID"] insert_values = [seitai_ishoku_id, donor_a_id, 1, 1] sql = f""" INSERT INTO T_DONOR_LIVER_LIV ({','.join(insert_columns)}) VALUES ({','.join(['%s'] * len(insert_columns))}) """ cursor.execute(sql, insert_values) connection.commit() #print(f"✅ T_DONOR_LIVER_LIV 登録完了: SEITAI_ISYOKU_ID={seitai_ishoku_id}, DONOR_A_ID={donor_a_id}") except Exception as e: print(f"❌ INSERT失敗: T_DONOR_LIVER_LIV, エラー: {e}") error_logs.append({ "テーブル名": "T_DONOR_LIVER_LIV", "SQL": sql, "入力値": insert_values, "エラー内容": str(e) }) connection.rollback() continue # 処理を次に進める（任意） # 更新処理も続けて update_table( cursor=cursor, connection=connection, table_name="T_DONOR_LIVER_LIV", column_series=column_series, data_row=row, id_columns=["SEITAI_ISYOKU_ID", "DONOR_A_ID"], id_values=[seitai_ishoku_id, donor_a_id], ishoku_toroku_id=ishoku_toroku_id, recipient_rows=recipient_rows, donor_rows=donor_rows ) nyuryoku_rows = [ { "KANJA_KBN": "0", # 1:recipient "KANJA_ID": seitai_ishoku_id, # 生体移植ID "KIROKU_TIMING": "0", # 0:新規 "NYURYOKUJOKYO": "0", # 0:未入力 }, { "KANJA_KBN": "1", # ドナー:ドナーID.A "KANJA_ID": donor_a_id, "KIROKU_TIMING": "0", # 1:移植 "NYURYOKUJOKYO": "0", # 1:完了 } ] for nyuryoku in nyuryoku_rows: sql = """ INSERT INTO T_NYURYOKUJOKYO_LIV ( SEITAI_ISYOKU_ID, KANJA_KBN, KANJA_ID, KIROKU_TIMING, NYURYOKUJOKYO, INS_USER_ID, INS_PROGRAM_ID, INS_DATE, UPD_USER_ID, UPD_PROGRAM_ID, UPD_DATE ) VALUES (%s, %s, %s, %s, %s, %s, %s, NOW(), %s, %s, NOW()) """ values = ( seitai_ishoku_id, nyuryoku["KANJA_KBN"], nyuryoku["KANJA_ID"], nyuryoku["KIROKU_TIMING"], nyuryoku["NYURYOKUJOKYO"], "1", # INS_USER_ID "1", # INS_PROGRAM_ID "1", # UPD_USER_ID "1" # UPD_PROGRAM_ID ) cursor.execute(sql, values)