CODEWITHSUNDEEP
pythonmysql
Jack AQ
Jack AQ

Reputation: 9

How do you avoid duplicate entry into database?

I would like to filter database inserts to avoid duplicates so it only insert 1 product per 1 ProductId. How do I do this? This is my insert:

add_data = ("INSERT INTO productdetails"
            "(productId, productUrl, discount, evaluateScore, volume, packageType, lotNum, validTime, storeName, storeUrl, allImageUrls, description) "
            "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)")

This is how it suppose to look like but in PyMySQL, how do I do the same in mysql.connector ?

INSERT INTO producttable (productId, productTitle, salePrice, originalPrice )
        SELECT * FROM (SELECT %(productId)s, %(productTitle)s, %(salePrice)s, %(originalPrice)s) AS tmp
        WHERE NOT EXISTS (
            SELECT productId FROM producttable WHERE productId = %(productId)s
        )
        LIMIT 1;

Upvotes: 0

Views: 150

Answers (3)

V.Khakhil
V.Khakhil

Reputation: 265

Set column to unique. then use INSERT IGNORE statement, If there is duplicate entry the query will not execute. you can read more here about INSERT IGNORE.

Upvotes: 0

mur
mur

Reputation: 933

The proper approach to do this is at the database end. You need to add a unique constraint:

ALTER TABLE productdetails
ADD UNIQUE (productId);

You can than simply do Insert, without any where or if.

Why? If you keep a set as suggested by yayati, you will limit yourself by having the set and the processing surrounding it as a bottleneck.

If you add the constraint, than it's left to the database to do fast checks for uniqueness even with millions of rows. Than you see if the DB returns error if its not unique.

Upvotes: 2

Yayati Sule
Yayati Sule

Reputation: 1631

What you could do is create the Insert statements via the String Interpolation in and keep adding them in a Set. The Set collection would only keep unique strings in itself. You can then bulk load the Set of unique SQL insert statements into your RDBMS.

Upvotes: 0

Related Questions