exec_test_sql_v3.py
2.61 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
#!/usr/bin/env python3
"""
测试环境 SQL 执行脚本
连接测试环境 192.168.1.91 执行 SQL
"""
import pymysql
# 测试环境数据库配置
DB_CONFIG = {
'host': '192.168.1.91',
'port': 3306,
'user': 'root',
'password': 'HuaHeng@2025',
'database': 'wms',
'charset': 'utf8mb4'
}
def execute_sql():
"""执行添加status字段的SQL"""
conn = pymysql.connect(**DB_CONFIG)
cursor = conn.cursor()
try:
print("=" * 60)
print("连接测试环境数据库成功: 192.168.1.91")
print("=" * 60)
# 1. 检查字段是否已存在
cursor.execute("""
SELECT COUNT(*)
FROM information_schema.columns
WHERE table_schema = 'wms'
AND table_name = 'receipt_container_detail'
AND column_name = 'status'
""")
exists = cursor.fetchone()[0] > 0
if exists:
print("⚠️ status 字段已存在，跳过添加")
else:
# 2. 添加 status 字段
cursor.execute("""
ALTER TABLE receipt_container_detail
ADD COLUMN status INT NULL
COMMENT '状态：0=新建, 10=组盘, 20=入库完成, 50=已回传'
""")
print("✅ 添加 status 字段成功")
# 3. 同步现有数据状态
cursor.execute("""
UPDATE receipt_container_detail rcd
SET status = rch.status
FROM receipt_container_header rch
WHERE rcd.receipt_container_id = rch.id
AND rcd.status IS NULL
""")
print(f"✅ 同步状态成功，影响行数: {cursor.rowcount}")
# 4. 查询状态分布
cursor.execute("""
SELECT
IFNULL(status, 'NULL') as status,
COUNT(*) as cnt
FROM receipt_container_detail
GROUP BY status
ORDER BY IFNULL(status, 999)
""")
print("\n📊 入库组盘详情状态分布:")
print("-" * 40)
for row in cursor.fetchall():
status_map = {0: '新建', 10: '组盘', 20: '入库完成', 50: '已回传', 'NULL': '未设置'}
status_name = status_map.get(row[0], f'未知({row[0]})')
print(f" status={row[0]} ({status_name}): {row[1]}条")
conn.commit()
print("\n✅ 所有SQL执行完成！")
except Exception as e:
conn.rollback()
print(f"❌ 执行失败: {e}")
raise
finally:
cursor.close()
conn.close()
if __name__ == '__main__':
execute_sql()