File size: 6,033 Bytes
85ef28f |
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 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 |
"""
調試 SQL 查詢 - 檢查實際執行的 SQL
"""
def debug_sql_query():
"""調試 SQL 查詢邏輯"""
print("🔍 調試 SQL 查詢邏輯")
print("=" * 50)
# 模擬關鍵字
keywords = ['貓砂', '礦砂', '豆腐砂', '水晶砂', '木屑砂', 'litter', '貓']
print(f"關鍵字: {keywords}")
# 生成 SQL 查詢條件
search_conditions = []
for keyword in keywords:
search_conditions.extend([
f"productName ILIKE '%{keyword}%'",
f"productCode ILIKE '%{keyword}%'",
f"barcode ILIKE '%{keyword}%'"
])
print(f"\n生成的搜尋條件數量: {len(search_conditions)}")
print("前10個條件:")
for i, condition in enumerate(search_conditions[:10], 1):
print(f" {i}. {condition}")
# 組合完整的 SQL
where_clause = " OR ".join(search_conditions)
full_sql = f"""
SELECT id, productCode, productName, stock, warehouse, category_id
FROM products
WHERE is_deleted = false
AND ({where_clause})
ORDER BY stock DESC
LIMIT 5;
"""
print(f"\n完整 SQL 查詢:")
print(full_sql)
# 測試與實際商品的匹配
print(f"\n🛍️ 測試與實際商品的匹配")
print("=" * 50)
# 實際商品資料
products = [
{
"id": 1,
"productCode": "OL1100-1",
"productName": "毆力天然犬種300g 室內成犬無榖小顆粒",
"stock": 100
},
{
"id": 2,
"productCode": "SW-06-01",
"productName": "Shovel well豪好鏟 破碎型礦砂",
"stock": 50
},
{
"id": 3,
"productCode": "TL-03",
"productName": "美國極冠貓砂 薰衣草12kg",
"stock": 48
},
{
"id": 4,
"productCode": "SL11002",
"productName": "首領汪 膠原鴨舌 5入彭湃包",
"stock": 100
}
]
matched_products = []
for product in products:
product_name = product["productName"].lower()
product_code = product["productCode"].lower()
# 檢查是否匹配任一關鍵字
for keyword in keywords:
keyword_lower = keyword.lower()
if (keyword_lower in product_name or
keyword_lower in product_code):
matched_products.append({
"product": product,
"matched_keyword": keyword
})
print(f"✅ 匹配: {product['productName']} (關鍵字: '{keyword}')")
break
print(f"\n總共匹配 {len(matched_products)} 個商品")
if not matched_products:
print("❌ 沒有匹配的商品")
# 逐個測試關鍵字
print("\n🔍 逐個測試關鍵字:")
for keyword in keywords:
print(f"\n關鍵字: '{keyword}'")
found = False
for product in products:
if keyword.lower() in product["productName"].lower():
print(f" ✅ 匹配商品: {product['productName']}")
found = True
if not found:
print(f" ❌ 無匹配商品")
def debug_ilike_logic():
"""調試 ILIKE 邏輯"""
print(f"\n🔍 調試 ILIKE 邏輯")
print("=" * 50)
# 測試字串
test_strings = [
"美國極冠貓砂 薰衣草12kg",
"Shovel well豪好鏟 破碎型礦砂",
"毆力天然犬種300g 室內成犬無榖小顆粒",
"首領汪 膠原鴨舌 5入彭湃包"
]
# 測試關鍵字
test_keywords = ["貓砂", "礦砂", "貓", "狗", "犬"]
print("ILIKE 匹配測試:")
for keyword in test_keywords:
print(f"\n關鍵字: '{keyword}'")
for string in test_strings:
# 模擬 ILIKE '%keyword%' 邏輯
if keyword.lower() in string.lower():
print(f" ✅ '{string}' 包含 '{keyword}'")
else:
print(f" ❌ '{string}' 不包含 '{keyword}'")
def debug_or_logic():
"""調試 OR 邏輯"""
print(f"\n🔍 調試 OR 邏輯")
print("=" * 50)
# 模擬 SQLAlchemy OR 查詢
products = [
{"productName": "美國極冠貓砂 薰衣草12kg", "productCode": "TL-03"},
{"productName": "Shovel well豪好鏟 破碎型礦砂", "productCode": "SW-06-01"},
{"productName": "毆力天然犬種300g 室內成犬無榖小顆粒", "productCode": "OL1100-1"},
{"productName": "首領汪 膠原鴨舌 5入彭湃包", "productCode": "SL11002"}
]
keywords = ['貓砂', '礦砂']
print(f"使用關鍵字: {keywords}")
print("OR 邏輯測試:")
for product in products:
conditions_met = []
for keyword in keywords:
# 檢查 productName
if keyword.lower() in product["productName"].lower():
conditions_met.append(f"productName contains '{keyword}'")
# 檢查 productCode
if keyword.lower() in product["productCode"].lower():
conditions_met.append(f"productCode contains '{keyword}'")
if conditions_met:
print(f" ✅ {product['productName']}")
print(f" 滿足條件: {', '.join(conditions_met)}")
else:
print(f" ❌ {product['productName']} (無匹配)")
def main():
"""主函數"""
print("🚀 開始 SQL 查詢調試")
print("=" * 60)
debug_sql_query()
debug_ilike_logic()
debug_or_logic()
print("\n" + "=" * 60)
print("✅ SQL 調試完成！")
print("\n💡 如果邏輯測試都正確，問題可能在於:")
print("1. 資料庫連接問題")
print("2. SQLAlchemy 查詢執行問題")
print("3. 事務或會話問題")
print("4. 權限問題")
if __name__ == "__main__":
main()
|