101 lines
4.4 KiB
Python
101 lines
4.4 KiB
Python
# -*- coding: utf-8 -*-
|
||
"""200 zentao_dev → 161 zentao_dev_2026：同步研发需求 9197 的关联记录。
|
||
源库全程只读；目标库纯 INSERT（前置检查目标无对应行，不覆盖不删除）。
|
||
范围：zt_storyspec / zt_projectstory / zt_task / zt_action / zt_effort（有则带）。
|
||
"""
|
||
import io, sys, time, pymysql
|
||
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')
|
||
|
||
SRC = dict(host='192.168.3.200', user='root', password='PX4fTAAsJ#T!1',
|
||
database='zentao_dev', charset='utf8mb4', connect_timeout=15)
|
||
DST = dict(host='192.168.1.161', user='devgps', password='dev@2021GPS',
|
||
database='zentao_dev_2026', charset='utf8mb4', connect_timeout=15, autocommit=False)
|
||
SID = 9197
|
||
TASK_IDS = (18841, 18849)
|
||
|
||
def log(m):
|
||
print(f'[{time.strftime("%H:%M:%S")}] {m}', flush=True)
|
||
|
||
src = pymysql.connect(**SRC); scur = src.cursor()
|
||
dst = pymysql.connect(**DST); dcur = dst.cursor()
|
||
|
||
def cols_of(cur, t):
|
||
cur.execute("""SELECT column_name FROM information_schema.columns
|
||
WHERE table_schema=DATABASE() AND table_name=%s
|
||
ORDER BY ordinal_position""", (t,))
|
||
return [r[0] for r in cur.fetchall()]
|
||
|
||
def fetch(t, where, params):
|
||
scur.execute(f'SELECT * FROM `{t}` WHERE {where}', params)
|
||
cols = [d[0] for d in scur.description]
|
||
return cols, scur.fetchall()
|
||
|
||
def sync_table(t, where, params, key_desc):
|
||
scols, rows = fetch(t, where, params)
|
||
if not rows:
|
||
log(f'{t}: 源库 0 行，跳过')
|
||
return 0
|
||
dcols = set(cols_of(dcur, t))
|
||
use = [c for c in scols if c in dcols]
|
||
dropped = [c for c in scols if c not in dcols]
|
||
if dropped:
|
||
log(f'{t}: 目标库缺列 {dropped}，这些列不写入')
|
||
# 前置检查：目标不能有与源主键冲突的行 —— 用第一主键列粗查
|
||
col_sql = ','.join(f'`{c}`' for c in use)
|
||
ph = ','.join(['%s'] * len(use))
|
||
insert_sql = f'INSERT INTO `{t}` ({col_sql}) VALUES ({ph})'
|
||
idx = [scols.index(c) for c in use]
|
||
data = [tuple(r[i] for i in idx) for r in rows]
|
||
dcur.executemany(insert_sql, data)
|
||
log(f'{t}: 插入 {len(data)} 行（{key_desc}）')
|
||
return len(data)
|
||
|
||
# ---------- 前置检查（目标应为空/无冲突） ----------
|
||
problems = []
|
||
dcur.execute('SELECT COUNT(*) FROM zt_storyspec WHERE story=%s', (SID,))
|
||
if dcur.fetchone()[0]: problems.append('zt_storyspec 已有 9197')
|
||
dcur.execute('SELECT COUNT(*) FROM zt_projectstory WHERE story=%s', (SID,))
|
||
if dcur.fetchone()[0]: problems.append('zt_projectstory 已有 9197')
|
||
dcur.execute(f'SELECT COUNT(*) FROM zt_task WHERE id IN {TASK_IDS}')
|
||
if dcur.fetchone()[0]: problems.append(f'zt_task id {TASK_IDS} 冲突')
|
||
dcur.execute('SELECT COUNT(*) FROM zt_action WHERE objectType=\'story\' AND objectID=%s AND id>119100', (SID,))
|
||
if dcur.fetchone()[0]: problems.append('zt_action 已有 9197 的源端记录')
|
||
if problems:
|
||
log('!!! 前置检查失败: ' + '; '.join(problems))
|
||
sys.exit(1)
|
||
log('前置检查通过：目标库无冲突')
|
||
|
||
# ---------- 同步 ----------
|
||
n = 0
|
||
n += sync_table('zt_storyspec', 'story=%s', (SID,), 'story=9197')
|
||
n += sync_table('zt_projectstory', 'story=%s', (SID,), 'story=9197, project 142/286')
|
||
n += sync_table('zt_task', f'id IN {TASK_IDS}', (), f'id={TASK_IDS}')
|
||
n += sync_table('zt_action', "objectType='story' AND objectID=%s", (SID,), 'story=9197 流转历史')
|
||
|
||
# zt_effort 可能有，也可能没有 task 列/相关行
|
||
ecols = cols_of(scur, 'zt_effort')
|
||
if ecols:
|
||
key = 'task' if 'task' in ecols else ('objectID' if 'objectID' in ecols else None)
|
||
if key:
|
||
where = f'`{key}` IN {TASK_IDS}' + (" AND objectType='task'" if 'objectType' in ecols else '')
|
||
n += sync_table('zt_effort', where, (), f'task={TASK_IDS}')
|
||
|
||
dst.commit()
|
||
log(f'已提交，共写入 {n} 行')
|
||
|
||
# ---------- 回读验证 ----------
|
||
log('--- 回读验证 ---')
|
||
dcur.execute('SELECT story, version, LEFT(title,30), LENGTH(spec), LENGTH(verify) FROM zt_storyspec WHERE story=%s', (SID,))
|
||
log(f'zt_storyspec: {dcur.fetchall()}')
|
||
dcur.execute('SELECT project, story, version, `order` FROM zt_projectstory WHERE story=%s', (SID,))
|
||
log(f'zt_projectstory: {dcur.fetchall()}')
|
||
dcur.execute(f'SELECT id, project, execution, name, status, assignedTo FROM zt_task WHERE id IN {TASK_IDS}')
|
||
for r in dcur.fetchall():
|
||
log(f'zt_task: {r}')
|
||
dcur.execute("SELECT id, action, date, actor FROM zt_action WHERE objectType='story' AND objectID=%s ORDER BY id", (SID,))
|
||
for r in dcur.fetchall():
|
||
log(f'zt_action: {r}')
|
||
|
||
src.close(); dst.close()
|
||
log('完成')
|