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

Question

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?

Take a look at [INSERT IGNORE](http://stackoverflow.com/a/548570/189134) — Andy, Jan 05 '15 at 20:37
wow! sometimes small changes can do wonder!!! Hope i could mark it as an accepted answer :) — hnvasa, Jan 05 '15 at 20:48

score 20 · Accepted Answer · answered Jan 05 '15 at 20:54

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)

Note that this will potentially ignore a whole swathe of other errors that could occur. (The quote from the docs does say this, but it's worth repeating!) — Air, Jan 08 '15 at 19:02

score 9 · Answer 2 · answered Jan 05 '15 at 20:40

9

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?

answered Jan 05 '15 at 20:40

alecxe

462,703
120
1,088
1,195

As a side question, where could I find documentation for cursor? I searched for it and I found [this](https://code.google.com/p/pyodbc/wiki/Cursor) but it seems to be insufficient. – SaidbakR Jan 05 '15 at 20:45
@sємsєм for example, [here](http://mysql-python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb.cursors-module.html) (MySQLdb specifically). – alecxe Jan 05 '15 at 20:46
+one this is surely a good alternative to similar sounding issues! – hnvasa Jan 05 '15 at 20:49
Unfortunately the documentation does not show what does it return on errors? @alecxe – SaidbakR Jan 05 '15 at 20:51
2

@sємsєм as for `MySQLdb` - yes, [documentation](http://mysql-python.sourceforge.net/MySQLdb.html) really sucks. – alecxe Jan 05 '15 at 21:23
it seems that the `MySQLdb.IntegrityError` only provides `args` attribute , which is a short list describing the cause of error , `IntegrityError.args[0]` shows error code defined in mysql , `IntegrityError.args[1]` is a formatted string text describing the detail, which is not really convenient because you may need to extract words from the formatted string text. – Ham Jul 31 '21 at 16:51

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

2 Answers2

Linked