python - 当数据库中已经存在相同的数据时，如何覆盖或跳过数据？

Question

我正在从包含一些重复数据的文本文件中获取数据。我正在尝试将它们插入数据库而不进行重复。我在插入重复数据时遇到了麻烦。它不应该再次插入。数据不是静态值。

text_file = open(min_file, "r") #doc = text_file.readlines() for line in text_file: field = line.split(";") print(field)

try:
    connection = mysql.connector.connect(host='localhost',
                                     database='testing',
                                     user='root',
                                     password='root')
    if connection.is_connected():
        db_Info = connection.get_server_info()
        print("Connected to MySQL Server version ", db_Info)
        cursor = connection.cursor()
        cursor.execute("select database();")
        record = cursor.fetchone()
        print("You're connected to database: ", record)
    
        mycursor = connection.cursor()
        #before inserting
        mycursor.execute("Select * from ftp")
        myresult = mycursor.fetchall()
        for i in myresult:
            print(i)

        sql ="Insert into ftp(a,b,c,d) \
        select * from( Select VALUES(%s,%s,%s,%s) as temp \
        where not exists \
        (Select a from ftp where a = %s) LIMIT 1"
        
        mycursor.execute(sql,field)
        print(mycursor.rowcount, "record inserted.")
        connection.commit()

except Error as e:
    print("Error while connecting to MySQL", e)
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

标签： python

Answer 1

一种选择是添加唯一约束并让数据库验证唯一性，这将引发您可以捕获和跳过的异常。