# -*- coding: utf-8 -*- """ サンプル環境データ投入スクリプト - 226(client_id=1)のデータ(2026-07-20〜2026-08-16)を、店名・エリアを架空名に変換し、 機密情報をダミー化して、カゴヤのサンプルDB(lerisa_ftrecsample)に投入する。 - 実行(226のTera Term): C:\\apps\\ranking_system\\venv\\Scripts\\python.exe C:\\apps\\ranking_system\\build_sample.py """ import mysql.connector PERIOD_START = '2026-07-20' PERIOD_END = '2026-08-16' CID = 1 VPS_DB = dict(host='127.0.0.1', port=3306, user='root', password='stance0209P0531#', database='rankradar_data', charset='utf8mb4') KAGOYA_DB = dict(host='mysql57s-32.kagoya.net', port=3306, user='lerisa', password='stance0209P0531#', database='lerisa_rankingsystem', charset='utf8mb4') SAMPLE_DB = dict(host='mysql57s-32.kagoya.net', port=3306, user='lerisa', password='stance0209P0531#', database='lerisa_ftrecsample', charset='utf8mb4', connection_timeout=60) NAME_MAP = { '人妻ステーション鹿児島': 'アンヴィーテ', 'こすらぶ鹿児島店': 'ルミナージュ', 'フェアリー【鹿児島店】': 'クレアーレ', 'フェアリー鹿児島店': 'クレアーレ', '薩摩♂風俗の神様 鹿児島店': 'ゼフィール', 'ギャルサー': 'モーヴ', 'キャンディ': 'ボンボニエール', 'ミセス人妻【鹿児島店】': 'マダムリュクス', 'ミセス人妻[鹿児島店]': 'マダムリュクス', 'Hills Premier': 'オルタンシア', 'ソフトクリーム': 'ネージュ', '人妻宅急便': 'エクスプレスマダム', 'メンズエステ ホワイト': 'ブランエステ', 'Sチャンネル': 'エスカーナ', 'EXECUTIVE[霧島店]': 'エグゼリア', 'private salon～リラスパ～': 'リラスパ プライベート', '摩天楼～MATENROW～鹿児島': 'スカイテラス', 'ラブリング': 'アニェロ', '楊貴妃': 'コハク', 'わっしょい☆元祖廃男コース鹿児島店(鹿児島デリヘル)': '祭ばやし', 'M♡rosa霧島': 'メロウローズ', 'アロマアンヌ': 'アロマ・エレーヌ', '鹿児島ちゃんこ 薩摩川内店': 'ぽちゃまる 川越店', '鹿児島ちゃんこ 霧島店': 'ぽちゃまる 立川店', '鹿児島ちゃんこ 天文館店': 'ぽちゃまる 銀座店', 'Lime Spa': 'シトロンスパ', 'キュン・キュン': 'トキメキ', 'Chou♡Chou': 'シュエット', 'ヴェルファーレ-VELFARRE-鹿児島': 'ヴェントゥーノ', 'CLUB A': 'クラブ アヴァ', 'NEWビビアン＆ガールズコレクション': 'ビビッド コレクション', 'ラブ♡エル': 'ラブレア', 'ラブ♡エル霧島店': 'ラブレア 立川店', 'CANDY BELOVED': 'スイートビーナス', '千姫': '彩姫', 'WITH YOU': 'ウィズミー', 'サボテン': 'カクタス', '艶女': 'アデルナ', 'momo♡霧島店': 'ももはな 立川店', '悪魔のリンパ': 'デヴィルタッチ', 'Aroma Bijou': 'アロマ ビジュー', '室楽園': 'らくえん', 'Eternal Beauty': 'エターナ', 'ましゅまろエステ スライム': 'マシュマロ・ラボ', 'ルージュ': 'ルビス', 'ROYAL HONEY': 'ロイヤルネクター', '石庭': 'イシュタル', 'ポラリス 鹿児島': 'ステラート', 'バニラ': 'ヴァニタ', 'HANABI': 'ハナミ', '7800円': 'コスパリア', 'M.s Fortuna': 'フォルトゥーナ', '人妻エステ': 'マダムスパ', 'エアポート': 'スカイポート', '-NEO-皇帝別館': 'ネオ・パレス', '人の妻': 'ヒトヅマ', 'secret space': 'シークレットルーム', 'NILS（ニルス）': 'ニクス', } AREA_MAP = { '鹿児島求人': '東京求人', '鹿児島風俗求人': '新宿風俗求人', '国分風俗求人': '渋谷風俗求人', '国分求人': '渋谷求人', '鹿児島県その他風俗求人': '東京都その他風俗求人', '奄美大島風俗求人': '八王子風俗求人', } unmapped_names = set() def cn(name): if name is None or name == '': return name if name not in NAME_MAP: unmapped_names.add(name) return '（その他店舗）' return NAME_MAP[name] def ca(area): if area is None: return None return AREA_MAP.get(area, area) def main(): vps = mysql.connector.connect(**VPS_DB) kag = mysql.connector.connect(**KAGOYA_DB) smp = mysql.connector.connect(**SAMPLE_DB) vc = vps.cursor(dictionary=True) kc = kag.cursor(dictionary=True) sc = smp.cursor() for t in ['scraping_results','analysis_results','analysis_suggestions', 'competitor_update_patterns','error_logs','update_histories', 'clients','media_settings','monitor_settings','tool_settings', 'update_timing_settings','excluded_times','fixed_update_times', 'notices','notice_recipients','client_memos','faqs']: sc.execute(f"DELETE FROM `{t}` WHERE 1=1") smp.commit() print("既存サンプルデータをクリアしました") # 1. scraping_results (バッチ分割投入) vc.execute("SELECT client_id,media_name,fetched_at,shop_name,sub_area,`rank`,page_no " "FROM scraping_results WHERE client_id=%s AND DATE(fetched_at) BETWEEN %s AND %s", (CID, PERIOD_START, PERIOD_END)) batch = [(r['client_id'], r['media_name'], r['fetched_at'], cn(r['shop_name']), ca(r['sub_area']), r['rank'], r['page_no']) for r in vc.fetchall()] CHUNK = 1000 total_sr = 0 for i in range(0, len(batch), CHUNK): chunk = batch[i:i+CHUNK] sc.executemany("INSERT INTO scraping_results " "(client_id,media_name,fetched_at,shop_name,sub_area,`rank`,page_no) " "VALUES (%s,%s,%s,%s,%s,%s,%s)", chunk) smp.commit() total_sr += len(chunk) print(f" scraping_results 投入中... {total_sr}/{len(batch)}") print(f"scraping_results: {total_sr}件") # 2. competitor_update_patterns vc.execute("SELECT client_id,media_name,shop_name,sub_area,window_days,is_tool," "pattern_type,confidence,update_count,sample_days,bump_times_json,computed_at " "FROM competitor_update_patterns WHERE client_id=%s", (CID,)) batch = [(r['client_id'], r['media_name'], cn(r['shop_name']), ca(r['sub_area']) if r['sub_area'] else r['sub_area'], r['window_days'], r['is_tool'], r['pattern_type'], r['confidence'], r['update_count'], r['sample_days'], r['bump_times_json'], r['computed_at']) for r in vc.fetchall()] for i in range(0, len(batch), 1000): sc.executemany("INSERT INTO competitor_update_patterns " "(client_id,media_name,shop_name,sub_area,window_days,is_tool,pattern_type," "confidence,update_count,sample_days,bump_times_json,computed_at) " "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", batch[i:i+1000]) smp.commit() print(f"competitor_update_patterns: {len(batch)}件") # 3. analysis_results vc.execute("SELECT client_id,media_name,analyzed_date,best_times,summary_comment,update_mode " "FROM analysis_results WHERE client_id=%s AND analyzed_date BETWEEN %s AND %s", (CID, PERIOD_START, PERIOD_END)) batch = [(r['client_id'], r['media_name'], r['analyzed_date'], r['best_times'], r['summary_comment'], r['update_mode']) for r in vc.fetchall()] sc.executemany("INSERT INTO analysis_results " "(client_id,media_name,analyzed_date,best_times,summary_comment,update_mode) " "VALUES (%s,%s,%s,%s,%s,%s)", batch) smp.commit() print(f"analysis_results: {len(batch)}件") # 4. analysis_suggestions (reason_text 空) vc.execute("SELECT client_id,media_name,suggested_at,suggest_times,is_approved,approved_at," "slot_scores,suggest_time,slot_score " "FROM analysis_suggestions WHERE client_id=%s AND DATE(suggested_at) BETWEEN %s AND %s", (CID, PERIOD_START, PERIOD_END)) batch = [(r['client_id'], r['media_name'], r['suggested_at'], r['suggest_times'], r['is_approved'], r['approved_at'], r['slot_scores'], '', r['suggest_time'], r['slot_score']) for r in vc.fetchall()] for i in range(0, len(batch), 1000): sc.executemany("INSERT INTO analysis_suggestions " "(client_id,media_name,suggested_at,suggest_times,is_approved,approved_at," "slot_scores,reason_text,suggest_time,slot_score) " "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", batch[i:i+1000]) smp.commit() print(f"analysis_suggestions: {len(batch)}件 (reason_text空)") # 5. clients (機密ダミー化) kc.execute("SELECT * FROM clients WHERE id=%s", (CID,)) c = kc.fetchone() if c: sc.execute("INSERT INTO clients " "(id,client_name,server_name,client_url,shop_name,prefecture,area_name," "email,password_hash,password_plain,contract_start,contract_end,update_mode,is_active) " "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", (CID, 'サンプル店舗', None, None, cn(c.get('shop_name')), '東京都', '東京', 'sample@example.com', '', None, c.get('contract_start'), c.get('contract_end'), c.get('update_mode', 1), 1)) smp.commit() print("clients: 1件 (機密ダミー化)") # 6. media_settings (機密ダミー化) kc.execute("SELECT * FROM media_settings WHERE client_id=%s", (CID,)) for m in kc.fetchall(): sc.execute("INSERT INTO media_settings " "(client_id,media_name,login_id,login_pass,monitor_url,target_rank,gh_award_count," "target_shop_name,is_active,max_update_count,shop_name) " "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", (m['client_id'], m['media_name'], None, None, None, m.get('target_rank'), m.get('gh_award_count', 0), cn(m.get('target_shop_name')) if m.get('target_shop_name') else None, m.get('is_active', 1), m.get('max_update_count'), cn(m.get('shop_name')) if m.get('shop_name') else None)) smp.commit() print("media_settings: 投入 (機密ダミー化)") # 7-10. 設定系(そのまま) kc.execute("SELECT * FROM monitor_settings WHERE client_id=%s", (CID,)) for m in kc.fetchall(): sc.execute("INSERT INTO monitor_settings (client_id,start_time,end_time,interval_min) " "VALUES (%s,%s,%s,%s)", (m['client_id'], m['start_time'], m['end_time'], m['interval_min'])) kc.execute("SELECT * FROM update_timing_settings WHERE client_id=%s", (CID,)) for m in kc.fetchall(): sc.execute("INSERT INTO update_timing_settings (client_id,media_name,update_mode) " "VALUES (%s,%s,%s)", (m['client_id'], m['media_name'], m['update_mode'])) kc.execute("SELECT * FROM excluded_times WHERE client_id=%s", (CID,)) for m in kc.fetchall(): sc.execute("INSERT INTO excluded_times (client_id,media_name,start_time,end_time) " "VALUES (%s,%s,%s,%s)", (m['client_id'], m['media_name'], m['start_time'], m['end_time'])) kc.execute("SELECT * FROM fixed_update_times WHERE client_id=%s", (CID,)) for m in kc.fetchall(): sc.execute("INSERT INTO fixed_update_times (client_id,media_name,update_time) " "VALUES (%s,%s,%s)", (m['client_id'], m['media_name'], m['update_time'])) smp.commit() print("monitor/update_timing/excluded/fixed: 投入") # 11. tool_settings (機密ダミー化) kc.execute("SELECT * FROM tool_settings WHERE client_id=%s", (CID,)) for m in kc.fetchall(): sc.execute("INSERT INTO tool_settings (client_id,tool_name,login_id,login_pass,tool_id,is_active) " "VALUES (%s,%s,%s,%s,%s,%s)", (m['client_id'], m.get('tool_name', 'tool'), None, None, 'sample', m.get('is_active', 1))) smp.commit() print("tool_settings: 投入 (機密ダミー化)") # 12. notices / notice_recipients kc.execute("SELECT * FROM notices") for n in kc.fetchall(): sc.execute("INSERT INTO notices (id,title,body,target_type,publish_at,is_published) " "VALUES (%s,%s,%s,%s,%s,%s)", (n['id'], n['title'], n['body'], n['target_type'], n['publish_at'], n['is_published'])) kc.execute("SELECT * FROM notice_recipients WHERE client_id=%s", (CID,)) for n in kc.fetchall(): sc.execute("INSERT INTO notice_recipients (notice_id,client_id,is_read,read_at) " "VALUES (%s,%s,%s,%s)", (n['notice_id'], n['client_id'], n.get('is_read', 0), n.get('read_at'))) smp.commit() print("notices/notice_recipients: 投入") # 13. faqs kc.execute("SELECT * FROM faqs") for f in kc.fetchall(): sc.execute("INSERT INTO faqs (category,question,answer,sort_order,is_published) " "VALUES (%s,%s,%s,%s,%s)", (f['category'], f['question'], f['answer'], f.get('sort_order', 0), f.get('is_published', 1))) smp.commit() print("faqs: 投入") # 15. error_logs / update_histories vc.execute("SELECT * FROM error_logs WHERE client_id=%s AND DATE(occurred_at) BETWEEN %s AND %s", (CID, PERIOD_START, PERIOD_END)) for e in vc.fetchall(): sc.execute("INSERT INTO error_logs (client_id,media_name,error_type,error_message,occurred_at) " "VALUES (%s,%s,%s,%s,%s)", (e['client_id'], e['media_name'], e['error_type'], e['error_message'], e['occurred_at'])) vc.execute("SELECT * FROM update_histories WHERE client_id=%s AND DATE(executed_at) BETWEEN %s AND %s", (CID, PERIOD_START, PERIOD_END)) for u in vc.fetchall(): sc.execute("INSERT INTO update_histories (client_id,media_name,executed_at,status,error_message) " "VALUES (%s,%s,%s,%s,%s)", (u['client_id'], u['media_name'], u['executed_at'], u.get('status', 1), u.get('error_message'))) smp.commit() print("error_logs/update_histories: 投入") vps.close(); kag.close(); smp.close() print("=" * 50) if unmapped_names: print("【警告】対応表にない店名(（その他店舗）に置換):") for n in sorted(unmapped_names): print(" ", n) else: print("OK: 全店名が対応表で変換されました(実名の取りこぼしなし)") print("=" * 50) print("サンプルデータ投入 完了") if __name__ == '__main__': main()