61 lines
1.6 KiB
Python
61 lines
1.6 KiB
Python
import psycopg2
|
||
import sys
|
||
|
||
sys.stdout.reconfigure(encoding="utf-8")
|
||
|
||
conn = psycopg2.connect(
|
||
host="192.168.110.252",
|
||
port=15432,
|
||
database="postgresql",
|
||
user="postgresql",
|
||
password="Jchl1528",
|
||
)
|
||
|
||
cursor = conn.cursor()
|
||
cursor.execute("SET search_path TO hisdev, public")
|
||
|
||
print("=" * 80)
|
||
print("检查手术医嘱的 activity_id 和 advice_name")
|
||
print("=" * 80)
|
||
print()
|
||
|
||
# 查询手术医嘱的 activity_id
|
||
cursor.execute("""
|
||
SELECT
|
||
sr.id,
|
||
sr.prescription_no,
|
||
sr.activity_id,
|
||
ad.name as activity_name,
|
||
sr.content_json
|
||
FROM wor_service_request sr
|
||
LEFT JOIN wor_activity_definition ad ON ad.id = sr.activity_id AND ad.delete_flag = '0'
|
||
WHERE sr.category_enum = 4
|
||
AND sr.delete_flag = '0'
|
||
AND sr.encounter_id = 2038823905749327873
|
||
""")
|
||
|
||
rows = cursor.fetchall()
|
||
for row in rows:
|
||
print(f"医嘱ID: {row[0]}")
|
||
print(f"单号: {row[1]}")
|
||
print(f"activity_id: {row[2]}")
|
||
print(f"activity_name: {row[3]}")
|
||
if row[4]:
|
||
import json
|
||
|
||
try:
|
||
content = json.loads(row[4]) if isinstance(row[4], str) else row[4]
|
||
print(f"手术名称: {content.get('surgeryName', 'N/A')}")
|
||
except:
|
||
print(f"content_json: {row[4][:100]}")
|
||
print("-" * 80)
|
||
|
||
print()
|
||
print("问题：SQL查询使用 activity_id 关联 wor_activity_definition 获取 advice_name")
|
||
print("但手术医嘱的 activity_id 可能为 null 或指向不存在的记录！")
|
||
print()
|
||
print("解决方案：应该从 content_json 中解析 surgeryName 作为 advice_name")
|
||
|
||
cursor.close()
|
||
conn.close()
|