exec_test_sql_v2.py
3.49 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
88
89
90
91
92
93
94
95
96
97
98
99
100
101
#!/usr/bin/env python3
"""
测试环境 SQL 执行脚本 - 尝试不同端口
"""
import pymysql
import time
# 尝试多个配置
DB_CONFIGS = [
{'host': '192.168.31.91', 'port': 3306, 'user': 'root', 'password': 'HuaHeng@2025', 'database': 'wms', 'charset': 'utf8mb4'},
{'host': '192.168.1.91', 'port': 3306, 'user': 'root', 'password': 'HuaHeng@2025', 'database': 'wms', 'charset': 'utf8mb4'},
{'host': '192.168.31.91', 'port': 3307, 'user': 'root', 'password': 'HuaHeng@2025', 'database': 'wms', 'charset': 'utf8mb4'},
]
def try_connect():
"""尝试连接"""
for i, config in enumerate(DB_CONFIGS):
try:
print(f"尝试配置 {i+1}: {config['host']}:{config['port']}...")
conn = pymysql.connect(**config)
cursor = conn.cursor()
cursor.execute("SELECT 1")
print(f"✅ 连接成功: {config['host']}:{config['port']}")
return conn, cursor, config
except Exception as e:
print(f"❌ 连接失败: {e}")
time.sleep(1)
return None, None, None
def main():
conn, cursor, config = try_connect()
if not conn:
print("\n❌ 无法连接到测试环境数据库，请手动执行 SQL:")
print("SQL 文件: D:/Projects/wms4_globalnancangkuangji/test_env_add_status.sql")
return
try:
print("=" * 60)
print(f"已连接测试环境: {config['host']}:{config['port']}")
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__':
main()