ylrz_saas_his_scrm/scripts/migrate_job_to_template.py

import re
import pymysql
import time

conn = pymysql.connect(host='cq-cdb-8fjmemkb.sql.tencentcdb.com', port=27220, user='root', password='Ylrz_1q2w3e4r5t6y', database='ylrz_saas')
cur = conn.cursor()

with open(r'F:\project\Saas\ylrz_saas_his_scrm\sql\fs_task_sys_job_seed.sql', 'r', encoding='utf-8') as f:
    lines = f.readlines()

col_names = ['job_id', 'job_name', 'job_group', 'invoke_target', 'cron_expression', 'misfire_policy', 'concurrent', 'status', 'create_by', 'create_time', 'update_by', 'update_time', 'remark']

count = 0
skipped = 0

for line in lines:
    line = line.strip()
    if not line or not line.startswith('INSERT'):
        continue

    # Extract VALUES part: find the last (...) before the trailing ;
    idx = line.rfind('VALUES (')
    if idx == -1:
        continue
    vals_part = line[idx + 8:]  # after "VALUES ("
    # Remove trailing ); or just )
    vals_part = vals_part.rstrip(';').rstrip(')')

    # Parse values with state machine (handles quotes and nested parens)
    vals = []
    current = ''
    depth = 0      # nested paren depth
    in_quote = False
    for ch in vals_part:
        if ch == "'" and not in_quote:
            in_quote = True
            current += ch
        elif ch == "'" and in_quote:
            # look for escaped quote ''
            in_quote = False
            current += ch
        elif ch == '(' and not in_quote:
            depth += 1
            current += ch
        elif ch == ')' and not in_quote:
            depth -= 1
            current += ch
        elif ch == ',' and not in_quote and depth == 0:
            vals.append(current.strip())
            current = ''
        else:
            current += ch
    if current.strip():
        vals.append(current.strip())

    # Build data dict
    data = {}
    for i, col in enumerate(col_names):
        if i < len(vals):
            v = vals[i].strip()
            if v.startswith("'") and v.endswith("'"):
                v = v[1:-1]
            if v.upper() == 'NULL':
                v = ''
            data[col] = v

    if 'job_name' not in data or not data.get('invoke_target'):
        skipped += 1
        continue

    target = data.get('invoke_target', '')
    # Check if already exists
    cur.execute('SELECT COUNT(*) FROM sys_job_template WHERE invoke_target=%s', (target,))
    if cur.fetchone()[0] > 0:
        print(f'  SKIP (exists): {data.get("job_name", "")}')
        skipped += 1
        continue

    # Generate template_code
    code = data.get('invoke_target', '').replace('()', '').replace('.', '_')
    code = re.sub(r"[\(\)']", '', code)
    code = re.sub(r'[^\w]', '_', code)
    code = re.sub(r'_+', '_', code).strip('_')
    if len(code) > 60:
        code = code[:60]

    module_tag = data.get('job_group', 'DEFAULT')

    cur.execute(
        '''INSERT INTO sys_job_template
        (template_code, job_name, job_group, invoke_target, cron_expression, misfire_policy, concurrent, scope, module_tag, default_status, status, create_by, create_time, remark)
        VALUES (%s,%s,%s,%s,%s,%s,%s,'TENANT',%s,'1',%s,%s,%s,%s)''',
        (code,
         data.get('job_name', ''),
         module_tag,
         target,
         data.get('cron_expression', ''),
         data.get('misfire_policy', '1'),
         data.get('concurrent', '1'),
         module_tag,
         data.get('status', '0'),
         data.get('create_by', 'admin'),
         data.get('create_time', time.strftime('%Y-%m-%d %H:%M:%S')),
         data.get('remark', ''))
    )
    count += 1
    print(f'  OK [{code}]: {data.get("job_name", "")}')

conn.commit()
cur.close()
conn.close()
print(f'\nInserted: {count}, Skipped: {skipped}')