sydomain

import os
import pandas as pd
import pymysql

# 获取当前文件路径
current_path = os.path.dirname(os.path.realpath(__file__))
excel_file_path = os.path.join(current_path, 'a.xlsx')

# 读取Excel文件并去重
excel_data = pd.read_excel(excel_file_path)
excel_data.fillna('', inplace=True)  # 将所有NaN值替换为空字符串

excel_data.drop_duplicates(inplace=True)

success_count = 0
failure_count = 0
failed_data = []

# 连接到数据库，并指定字符集为utf8mb4
connection = pymysql.connect(
    host='localhost',
    port=3306,
    user='mykehu',
    password='MQW80nu9z2qpMiP',
    database='mykehu',
    charset='utf8mb4',
    collation='utf8mb4_general_ci'
)

with connection.cursor() as cursor:
    for index, row in excel_data.iterrows():
        customer_name = row['customer_name']
        contact_number = row['contact_number']
        home_address = row['home_address']
        tracking_number = row['tracking_number']
       
        # 查询数据库中是否已存在相同数据
        query_sql = "SELECT * FROM files WHERE customer_name = %s AND contact_number = %s AND home_address = %s AND tracking_number = %s"
        cursor.execute(query_sql, (customer_name, contact_number, home_address, tracking_number))
        result = cursor.fetchone()
       
        if result:
            continue  # 如果数据库中已存在相同数据，则跳过当前数据的插入操作
       
        insert_sql = "INSERT INTO files (customer_name, contact_number, home_address, tracking_number) VALUES (%s, %s, %s, %s)"
        try:
            cursor.execute(insert_sql, (customer_name, contact_number, home_address, tracking_number))
            success_count += 1
        except Exception as e:
            failure_count += 1
            failed_data.append(row)

connection.commit()
connection.close()

print("成功上传 {} 条数据".format(success_count))
print("上传失败 {} 条数据".format(failure_count))
if failure_count > 0:
    print("上传失败的数据:")
    for data in failed_data:
        print(data)