python - 重定向的 URL 列表比较

Question

我得到了 2 个 CSV 文件，每个文件包含 3000 多个 URL。

我的任务是创建一个从“旧站点”到“新站点”的 .htaccess“重定向” block ，而不是手动比较它们，我想我可以简单地尝试 bash/python脚本，或将它们导入 MySQL 进行比较。

因此，在 Bash 中我尝试了以下代码:

#!/bin/bash

awk 'BEGIN{FS=OFS="/"} {gsub(/\/$/, ""); $NF=tolower($NF)} NR==FNR{a[$NF]=$0; next} $NF in a {print a[$NF] " " $0 > "combined.csv"}' oldsite.csv newsite.csv

但是，它返回一个空的“combined.csv”，所以我想也许是“Python”……但是，唉，我对 Python 知之甚少，所以我想到了 MySQL……如果我只导入每个 CSV到一个新表中，我可以运行一个比较 SQL 语句并将结果转储到一个 2 列的新表中......再次唉，我不确定从哪里开始比较，计算 LIKE 比较语句，但我想知道的是“最佳”(意思是最准确的比较)方法是什么……如果是 Python，又如何呢？

CSV 样本

新网址

"new-url"
"/product/dangle-hoop-earrings-for-girls-with-cz-and-heart-dangle-in-14k-gold/"
"/product/dangle-hoop-earrings-for-girls-with-cz-and-butterfly-dangle-in-14k-gold/"
"/product/petite-lever-back-earrings-for-little-girls-in-14k-yellow-gold-with-blue-topaz-high-end-childrens-earrings/"

旧网址

"old-url"
"/product/0903-HUGGIEGK/Dangle-Hoop-Earrings-for-Girls-with-CZ-and-Heart-Dangle-in-14K-Gold/"
"/product/0954-HUGGIEGK/Dangle-Hoop-Earrings-for-Girls-with-CZ-and-Butterfly-Dangle-in-14K-Gold/"
"/product/10049Y4JBT/Petite-Lever-Back-Earrings-for-Little-Girls-in-14K-Yellow-Gold-with-Blue-Topaz---High-End-Childrens-Earrings/"

预期组合

"old-url", "new-url"
"/product/0903-HUGGIEGK/Dangle-Hoop-Earrings-for-Girls-with-CZ-and-Heart-Dangle-in-14K-Gold/", "/product/dangle-hoop-earrings-for-girls-with-cz-and-heart-dangle-in-14k-gold/"
"/product/0954-HUGGIEGK/Dangle-Hoop-Earrings-for-Girls-with-CZ-and-Butterfly-Dangle-in-14K-Gold/", "/product/dangle-hoop-earrings-for-girls-with-cz-and-butterfly-dangle-in-14k-gold/"
"/product/10049Y4JBT/Petite-Lever-Back-Earrings-for-Little-Girls-in-14K-Yellow-Gold-with-Blue-Topaz---High-End-Childrens-Earrings/", "/product/petite-lever-back-earrings-for-little-girls-in-14k-yellow-gold-with-blue-topaz-high-end-childrens-earrings/"

Answer 1

正如我们在评论线程中发现的那样，您需要转换数据以便通过删除 MS-DOS 的 \r 部分在 awk/unix 中进行处理行尾为

dos2unix file

将 file 行结尾从 \r\n 转换为 \n。请注意，您可以使用多个文件名调用 dos2unix，每个文件都将被处理，即

dos2unix old.csv new.csv many_more ...

这是您修改后的代码，它将为"new"文件中不匹配的记录创建一个单独的文件。我发现唯一需要更正的是更改最终输出以包含 , 字符，因此 print a[$NF] ","$0 。

#!/bin/bash 
awk 'BEGIN{FS=OFS="/"}
  { gsub(/\/$/, "")
    # print "#dbg: FILENAME="FILENAME "\tNR="NR "\tFNR="FNR
    $NF=tolower($NF)
  }
  NR==FNR{
    a[$NF]=$0; next
  }
  {
    if ($NF in a) {
      print  a[$NF] "," $0  > "combined.csv"
    }
    else {
      print  a[$NF] "," $0  > "unmatched.csv"
    }
  }
  ' oldsite.csv newsite.csv

输出

cat combined.csv

"/product/10049Y4JBT/Petite-Lever-Back-Earrings-for-Little-Girls-in-14K-Yellow-Gold-with-Blue-Topaz---High-End-Childrens-Earrings/","/product/dangle-hoop-earrings-for-girls-with-cz-and-heart-dangle-in-14k-gold/"
"/product/10049Y4JBT/Petite-Lever-Back-Earrings-for-Little-Girls-in-14K-Yellow-Gold-with-Blue-Topaz---High-End-Childrens-Earrings/","/product/dangle-hoop-earrings-for-girls-with-cz-and-butterfly-dangle-in-14k-gold/"
"/product/10049Y4JBT/Petite-Lever-Back-Earrings-for-Little-Girls-in-14K-Yellow-Gold-with-Blue-Topaz---High-End-Childrens-Earrings/","/product/petite-lever-back-earrings-for-little-girls-in-14k-yellow-gold-with-blue-topaz-high-end-childrens-earrings/"

cat unmatched.csv
,"new-url"

健康教育