import sys,json,collections
|
from pathlib import Path
|
P=Path(__file__).parent
|
sys.path.insert(0,str(P.parent/'iqc-import-20261012/deps'))
|
import pymysql
|
c=pymysql.connect(host='36.213.150.237',port=10000,user='root',password=sys.stdin.readline().strip(),database='mom-hm',charset='utf8mb4',cursorclass=pymysql.cursors.DictCursor)
|
s={}
|
with c.cursor() as q:
|
q.execute('SELECT DATABASE() db');assert q.fetchone()['db']=='mom-hm'
|
q.execute('SHOW CREATE TABLE mes_qc_indicator');s['schema']=q.fetchone()
|
q.execute('SELECT * FROM mes_qc_indicator ORDER BY id');s['indicators']=q.fetchall()
|
q.execute("SELECT table_name,column_name,column_type,column_comment FROM information_schema.columns WHERE table_schema=DATABASE() AND (column_name LIKE '%%indicator%%' OR column_comment LIKE '%%质检指标%%' OR column_comment LIKE '%%检测指标%%') ORDER BY table_name,ordinal_position");s['reference_columns']=q.fetchall()
|
groups=collections.defaultdict(list)
|
for r in s['indicators']:
|
if r['deleted'] in (0,False,b'\x00'):groups[(r.get('tenant_id',0),(r['name'] or '').strip())].append(r)
|
s['duplicates']=[{'name':k[1],'tenant_id':k[0],'rows':v} for k,v in groups.items() if len(v)>1]
|
ids=[r['id'] for g in s['duplicates'] for r in g['rows']]
|
s['references']={}
|
for col in s['reference_columns']:
|
t=col['TABLE_NAME'];field=col['COLUMN_NAME']
|
if field!='indicator_id' or not ids:continue
|
q.execute('SHOW CREATE TABLE `'+t+'`');schema=q.fetchone()
|
q.execute('SELECT * FROM `'+t+'` WHERE `'+field+'` IN ('+','.join('%s' for _ in ids)+') ORDER BY id',ids)
|
s['references'][t]={'schema':schema,'rows':q.fetchall()}
|
(P/'before-indicator-dedup.json').write_text(json.dumps(s,ensure_ascii=False,default=str,indent=2),encoding='utf8')
|
print(s['schema']['Create Table']);print('reference_columns',json.dumps(s['reference_columns'],ensure_ascii=False));print('duplicates',json.dumps(s['duplicates'],ensure_ascii=False,default=str));print('reference_counts',json.dumps({t:len(x['rows']) for t,x in s['references'].items()}))
|
c.close()
|
