Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to avoid duplicate entries in a MySQL database without throwing an error

I am using the Python-MySQL (MySQLdb) library to insert values into a database. I want to avoid duplicate entries from being inserted into the database, so I have added the unique constraint to that column in MySQL. I am checking for duplicates in the title column. In my Python script, I am using the following statement:

cursor.execute ("""INSERT INTO `database` (title, introduction) VALUES (%s, %s)""", (title, pure_introduction))

Now when a duplicate entry is added to the database, it will produce an error. I do not want an error message to appear; I just want that if a duplicate entry is found then it should simply not enter that value into the database. How do I do this?

like image 604
hnvasa Avatar asked Jan 05 '15 20:01

hnvasa


2 Answers

You can utilize the INSERT IGNORE syntax to suppress this type of error.

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

In your case, the query would become:

INSERT IGNORE INTO `database` (title, introduction) VALUES (%s, %s)
like image 67
Andy Avatar answered Oct 17 '22 23:10

Andy


Aside from what @Andy suggested (which should really be posted as an answer), you can also catch the exception in Python and silence it:

try:
    cursor.execute ("""INSERT INTO `database` (title, introduction) VALUES (%s, %s)""", (title, pure_introduction))
except MySQLdb.IntegrityError:
    pass  # or may be at least log?
like image 23
alecxe Avatar answered Oct 17 '22 22:10

alecxe