| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318 |
- #!/usr/bin/env python3
- # -*- coding: utf-8 -*-
- """
- 用户115(李志清 / family_members.id=75 / family_id=119 / user_id=115)高尿酸健康管理数据操作
- Part A: food_recommend_idx 高尿酸食材推荐指数 -50(痛风禁令口径 26 种)+ 变更日志
- Part B: health_plans 健康方案(报告解读 + 初步方案)+ 12 条 tasks(购买任务为服药任务前置)
- 操作对象:生产库 zxyj(JD Cloud RDS)。执行前已建立快照备份表(可回滚)。
- """
- import pymysql, json, datetime, sys
- DB = dict(host='mysql-internet-cn-north-1-23feae22680e4dfa.rds.jdcloud.com',
- port=3306, user='cfc', password='cfc@1314', database='zxyj', charset='utf8mb4')
- USER_ID, MEMBER_ID, FAMILY_ID, REPORT_ID = 115, 75, 119, 64
- TODAY = '2026-11-15'
- STAMP = TODAY.replace('-', '')
- # 痛风禁令口径(disease_intake_limits 痛风规则):动物内脏 + 部分海鲜 + 浓肉汤 + 含糖饮料(果汁)
- HIGH_UA_FOOD_IDS = [
- 7526, 7522, 7525, 7536, 7532, 7535, # 动物内脏
- 7449, 7444, 7445, 7437, 7447, 7452, 7446, 7465, 7436, 7440, 7443, 7450,
- 7433, 7463, 7432, # 部分海鲜
- 7517, 7533, # 浓肉汤
- 7491, 7480, 7482, # 含糖饮料(果汁)
- ]
- CHANGE_REASON = ('高尿酸健康管理调整:痛风禁令口径高尿酸食材'
- '(动物内脏/部分海鲜/浓肉汤/含糖饮料),推荐指数-50')
- APPLY = '--apply' in sys.argv
- VERIFY = '--verify' in sys.argv
- conn = pymysql.connect(**DB)
- cur = conn.cursor()
- def q(sql, args=None):
- cur.execute(sql, args)
- return cur.fetchall()
- def run(sql, args=None):
- cur.execute(sql, args)
- return cur.rowcount
- # ---------------------------------------------------------------- 快照备份
- def backup():
- for tbl, where in [
- ('food_recommend_idx', 'user_id=%d' % USER_ID),
- ('food_recommend_idx_log', 'user_id=%d' % USER_ID),
- ('health_plans', 'family_id=%d' % FAMILY_ID),
- ('tasks', 'family_id=%d' % FAMILY_ID),
- ]:
- bak = '%s_bak_%s' % (tbl, STAMP)
- run('DROP TABLE IF EXISTS %s' % bak)
- run('CREATE TABLE %s AS SELECT * FROM %s WHERE %s' % (bak, tbl, where))
- n = q('SELECT COUNT(*) FROM %s' % bak)[0][0]
- print('[backup] %s -> %s (%d rows)' % (tbl, bak, n))
- # ---------------------------------------------------------------- Part A
- def part_a():
- in_clause = ','.join(str(int(x)) for x in HIGH_UA_FOOD_IDS) # 仅整数,安全内联
- rows = q('SELECT id, food_id, food_name, index_score FROM food_recommend_idx '
- 'WHERE user_id=%s AND food_id IN (' + in_clause + ')',
- (USER_ID,))
- got = {r[1]: r for r in rows}
- missing = [x for x in HIGH_UA_FOOD_IDS if x not in got]
- print('[A] 命中 %d/%d 种;缺失: %s' % (len(rows), len(HIGH_UA_FOOD_IDS), missing or '无'))
- if missing:
- # 缺失说明 ID 漂移,安全起见中止
- print('[A] ABORT: 存在未命中的食材ID,请核对后重跑')
- return False
- changed = []
- for r in rows:
- idx_id, food_id, food_name, old = r
- new = old - 50
- changed.append((idx_id, food_id, food_name, old, new))
- print('[A] %s: %d -> %d' % (food_name, old, new))
- if APPLY or VERIFY:
- for idx_id, food_id, food_name, old, new in changed:
- run('UPDATE food_recommend_idx SET index_score=%s WHERE id=%s', (new, idx_id))
- run('INSERT INTO food_recommend_idx_log '
- '(user_id, food_id, food_name, previous_index, new_index, health_report_id, change_reason, changed_at) '
- 'VALUES (%s,%s,%s,%s,%s,%s,%s,%s)',
- (USER_ID, food_id, food_name, old, new, REPORT_ID, CHANGE_REASON, datetime.datetime.now()))
- print('[A] 已更新 %d 条 index_score 并写 %d 条变更日志' % (len(changed), len(changed)))
- else:
- print('[A] DRY-RUN 未落库')
- return True
- # ---------------------------------------------------------------- Part B
- def plan_content_md():
- return """# 李志清|肠道菌群报告解读与高尿酸初步干预方案
- > 依据:肠道菌群检测报告(报告编号 555711538 / 报告ID 64 / 2026-11-13)
- > 成员:李志清(家庭成员ID 75)|已知血液学情况:**血尿酸偏高**(血检结果,非本报告内容)
- ## 一、报告解读
- ### 1.1 报告评分总览
- | 维度 | 得分 | 判读 |
- |---|---|---|
- | 整体评分 | 51 | 中等偏低 |
- | 肠健康 | 51 | 需干预 |
- | 慢性病风险 | 46 | 偏高 |
- | 营养 | 73 | 相对良好 |
- | 菌群平衡 | 35 | 偏低 |
- | 菌群多样性 | 37 | 偏低 |
- | 有益菌 | 22 | 明显不足 |
- | 有害菌 | 65 | 偏高 |
- | 核心菌属 | 87 | 良好 |
- ### 1.2 解读要点
- 1. **菌群失衡**:有益菌 22 分(明显不足)、有害菌 65 分(偏高),平衡分仅 35 → 肠道定植能力弱,有害代谢产物(炎症、代谢负担)易累积。
- 2. **多样性不足**:多样性 37 分,菌株类型偏单一,饮食结构单一进一步限制菌群可塑性 → 需要"喂菌 + 补菌"两条腿同时走。
- 3. **核心菌属基础尚可**:核心菌属 87 分 → 干预重点不是重建菌群,而是补上"养料(益生元)+ 菌株(益生菌)",把多样性与有益菌占比拉起来。
- 4. **慢性病风险偏高**:慢性病风险 46 分,与有害菌偏高、营养代谢负担相关 → 炎症与代谢管理需同步进行。
- 5. **尿酸偏高(外部已知)**:血尿酸偏高压缩了"低嘌呤"饮食空间,且与肠道菌群代谢相互影响 → 本方案饮食端已按痛风禁令口径下调高尿酸食材推荐指数(-50),并安排血尿酸 + 肾功能复查以确认安全边界。
- ### 1.3 干预主线
- **喂菌(PHGG 瓜尔豆益生元)→ 补菌(AKK + 乳杆菌)→ 抗炎(Enagic 姜黄)→ 优杆菌(EPA + DHA)→ 生活方式(还原水 2.5–3 L/日 + 体检排查)**
- ## 二、初步方案
- ### 2.1 马上要做(第 1 周内完成)
- | # | 事项 | 类型 | 说明 |
- |---|---|---|---|
- | 1 | 牙科洗牙 + 牙周检查 | 体检 | 排查口腔慢性炎症源(全身炎症与代谢负担的常见来源) |
- | 2 | 空腹血糖 + HbA1c | 体检 | 建立血糖基线(菌群失衡与胰岛素敏感性相关) |
- | 3 | 血尿酸 + 肾功能(肌酐/eGFR) | 体检 | 量化尿酸水平并评估肾功能,作为饮食与补充剂的安全边界 |
- | 4 | 购买 PHGG 瓜尔豆益生元 | 购买 | 喂菌:为肠道菌群提供可利用的益生元底物 |
- | 5 | 购买 AKK + 乳杆菌 | 购买 | 补菌:补充有益菌株(平台暂无含 AKK 菌株商品,先按补菌方向备货) |
- | 6 | 购买 Enagic 姜黄 | 购买 | 抗炎:针对有害菌偏高带来的炎症负担 |
- | 7 | 购买 EPA + DHA | 购买 | 优杆菌:支持核心菌属与肠膜完整性 |
- | 8 | 每天 2.5–3 升还原水 | 每日 | 分次饮用,增加饮水与排泄,帮助冲刷嘌呤代谢产物 |
- ### 2.2 购买完成后的服药任务
- 每个购买任务完成后,自动开始对应的每日服药任务(PHGG 益生元 / AKK + 乳杆菌 / Enagic 姜黄 / EPA + DHA),具体用法按产品说明执行。
- """
- def plan_json_obj():
- tasks = [
- dict(action_type='checkin', title='牙科洗牙 + 牙周检查', dimension='body', frequency='once',
- notes='排查口腔慢性炎症源'),
- dict(action_type='checkin', title='空腹血糖 + HbA1c', dimension='body', frequency='once',
- notes='建立血糖基线'),
- dict(action_type='checkin', title='血尿酸 + 肾功能(肌酐/eGFR)', dimension='body', frequency='once',
- notes='量化尿酸水平并评估肾功能'),
- dict(action_type='buy', title='购买:PHGG瓜尔豆益生元', dimension='wealth', frequency='once',
- notes='喂菌:为肠道菌群提供可利用益生元底物'),
- dict(action_type='buy', title='购买:AKK + 乳杆菌', dimension='wealth', frequency='once',
- notes='补菌:补充有益菌株'),
- dict(action_type='buy', title='购买:Enagic 姜黄', dimension='wealth', frequency='once',
- notes='抗炎:针对有害菌偏高带来的炎症负担'),
- dict(action_type='buy', title='购买:EPA + DHA', dimension='wealth', frequency='once',
- notes='优杆菌:支持核心菌属与肠膜完整性'),
- dict(action_type='diet', title='每天饮用2.5-3升还原水', dimension='body', frequency='daily',
- notes='分次饮用,帮助冲刷嘌呤代谢产物'),
- dict(action_type='checkin', title='服用:PHGG瓜尔豆益生元(每日)', dimension='body', frequency='daily',
- notes='按产品说明每日服用'),
- dict(action_type='checkin', title='服用:AKK + 乳杆菌(每日)', dimension='body', frequency='daily',
- notes='按产品说明每日服用'),
- dict(action_type='checkin', title='服用:Enagic 姜黄(每日)', dimension='body', frequency='daily',
- notes='按产品说明每日服用'),
- dict(action_type='checkin', title='服用:EPA + DHA(每日)', dimension='body', frequency='daily',
- notes='按产品说明每日服用'),
- ]
- return {
- 'overview': ('依据 2026-11-13 肠道菌群检测报告(报告ID 64):整体 51 / 有益菌 22(不足)/ '
- '有害菌 65(偏高)/ 多样性 37 / 核心菌属 87;已知血尿酸偏高。'
- '干预主线:喂菌 + 补菌 + 抗炎 + 优杆菌 + 还原水生活方式。'),
- 'sections': [
- {'key': 'diet', 'title': '报告解读与初步干预方案',
- 'content': '有益菌不足、有害菌偏高、多样性不足,需喂菌/补菌;已知尿酸偏高,饮食端已下调高尿酸食材推荐指数。',
- 'items': [], 'tasks': tasks},
- ],
- 'abnormal_indicators': [
- {'member': '李志清', 'indicator': '有益菌', 'value': '22', 'unit': '分'},
- {'member': '李志清', 'indicator': '有害菌', 'value': '65', 'unit': '分'},
- {'member': '李志清', 'indicator': '菌群多样性', 'value': '37', 'unit': '分'},
- {'member': '李志清', 'indicator': '血尿酸', 'value': '偏高', 'unit': ''},
- ],
- 'knowledge_sources': [],
- }
- def part_b():
- # 幂等:family 119 已有同名方案则中止
- dup = q('SELECT id FROM health_plans WHERE family_id=%s AND goal LIKE %s',
- (FAMILY_ID, '%李志清%肠道菌群%'))
- if dup:
- print('[B] ABORT: 已存在方案 id=%s,中止以防重复' % dup[0][0])
- return
- now = datetime.datetime.now()
- today_end = now.replace(hour=23, minute=59, second=59, microsecond=0)
- plan_end = (now + datetime.timedelta(days=14)).replace(hour=23, minute=59, second=59, microsecond=0)
- content = plan_content_md()
- pj = json.dumps(plan_json_obj(), ensure_ascii=False)
- # 1) health_plans
- run('INSERT INTO health_plans '
- '(family_id, member_ids, dimensions, goal, plan_content, member_name, created_at, updated_at, plan_json, status, total_days, plan_type) '
- 'VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
- (FAMILY_ID, str(MEMBER_ID), 'body,action',
- '基于2026-11-13肠道菌群检测报告的解读与高尿酸初步干预方案',
- content, '李志清', now, now, pj, 'published', 14, 'health_plan'))
- plan_id = q('SELECT LAST_INSERT_ID()')[0][0]
- print('[B] health_plans id=%s' % plan_id)
- # 2) 任务行字段
- def base_row(**kw):
- row = dict(family_id=FAMILY_ID, family_member_id=MEMBER_ID, child_id=MEMBER_ID,
- creator_id=USER_ID, executor_type='child', executor_id=MEMBER_ID,
- source_type='health_plan', source_id=plan_id,
- need_review=0, review_by_category=0, status='pending',
- member_only=0, is_flow_task=1, dimension_code='body',
- is_daily_progress=0, duration=0, is_template=0,
- task_type=None, repeat_type='none', frequency=None,
- deadline=None, prerequisite_task_id=None,
- action_type=None, action_config=None, complete_types=None,
- created_at=now, updated_at=now, points=10)
- row.update(kw)
- return row
- def insert_task(row):
- cols = list(row.keys())
- ph = ','.join(['%s'] * len(cols))
- run('INSERT INTO tasks (%s) VALUES (%s)' % (','.join(cols), ph),
- tuple(row[c] for c in cols))
- return q('SELECT LAST_INSERT_ID()')[0][0]
- # 2a) 体检 3(一次性)
- exam_titles = [
- ('牙科洗牙 + 牙周检查', '排查口腔慢性炎症源(全身炎症与代谢负担常见来源)'),
- ('空腹血糖 + HbA1c', '建立血糖基线(菌群失衡与胰岛素敏感性相关)'),
- ('血尿酸 + 肾功能(肌酐/eGFR)', '量化尿酸水平并评估肾功能,作为饮食与补充剂安全边界'),
- ]
- for title, desc in exam_titles:
- tid = insert_task(base_row(title=title, description=desc, points=10,
- category='打卡任务', task_type='onetime', repeat_type='none',
- frequency=None, is_template=0, deadline=today_end, action_type='checkin'))
- print('[B] 体检任务 id=%s: %s' % (tid, title))
- # 2b) 购买 4(一次性,category=购买任务)
- purchases = [
- ('购买:PHGG瓜尔豆益生元', 55, '瓜尔纤维', '喂菌:为肠道菌群提供可利用益生元底物'),
- ('购买:AKK + 乳杆菌', None, '益生菌', '补菌:补充有益菌株(平台暂无含AKK菌株商品)'),
- ('购买:Enagic 姜黄', 15, '姜黄', '抗炎:针对有害菌偏高带来的炎症负担'),
- ('购买:EPA + DHA', 79, '鱼油', '优杆菌:支持核心菌属与肠膜完整性'),
- ]
- purchase_ids = []
- for title, product_id, cat, desc in purchases:
- row = base_row(title=title, description=desc + '(方案ID ' + str(plan_id) + ',品类:' + cat + ')',
- points=10, category='购买任务', task_type='onetime', repeat_type='none',
- frequency=None, is_template=0, deadline=today_end)
- if product_id is not None:
- row['action_type'] = 'cart'
- row['action_config'] = json.dumps({'productId': product_id, 'quantity': 1,
- 'planId': plan_id, 'category': cat}, ensure_ascii=False)
- tid = insert_task(row)
- purchase_ids.append(tid)
- print('[B] 购买任务 id=%s: %s (product=%s)' % (tid, title, product_id or '无'))
- # 2c) 饮水 1(每日)
- wtid = insert_task(base_row(title='每天饮用2.5-3升还原水',
- description='分次饮用,增加饮水与排泄,帮助冲刷嘌呤代谢产物',
- points=5, category='饮食任务', task_type='recurring',
- repeat_type='daily', frequency='daily', is_template=1,
- deadline=plan_end, action_type='diet'))
- print('[B] 饮水任务 id=%s: 每天2.5-3升还原水' % wtid)
- # 2d) 服药 4(每日,前置=对应购买任务)
- meds = [
- ('服用:PHGG瓜尔豆益生元(每日)', '按产品说明每日服用,餐后为佳', 0),
- ('服用:AKK + 乳杆菌(每日)', '按产品说明每日服用', 1),
- ('服用:Enagic 姜黄(每日)', '按产品说明每日服用,抗炎方向', 2),
- ('服用:EPA + DHA(每日)', '按产品说明每日服用,优杆菌方向', 3),
- ]
- for title, desc, pi in meds:
- tid = insert_task(base_row(title=title, description=desc, points=5,
- category='打卡任务', task_type='recurring',
- repeat_type='daily', frequency='daily', is_template=1,
- deadline=plan_end, action_type='checkin',
- prerequisite_task_id=purchase_ids[pi]))
- print('[B] 服药任务 id=%s: %s (prereq=%s)' % (tid, title, purchase_ids[pi]))
- print('[B] 方案ID=%s,共生成 12 条任务' % plan_id)
- # ---------------------------------------------------------------- main
- print('=== 用户115 高尿酸健康管理 ===')
- if APPLY:
- print('模式: APPLY')
- backup()
- ok = part_a()
- if ok:
- part_b()
- conn.commit()
- print('[main] 已提交')
- elif VERIFY:
- print('模式: VERIFY(事务内执行+回滚,不落库)')
- ok = part_a()
- if ok:
- part_b()
- conn.rollback()
- print('[main] VERIFY 已回滚,未持久化任何数据')
- else:
- print('模式: DRY-RUN')
- part_a()
- print('[B] DRY-RUN 未执行方案/任务创建')
- print('=== 完成 ===')
- conn.close()
|