Insert into database only if the entry doesn't exist

Ignition
,
2

There are two ways to do this -

  1. check for a duplicate manually with python before inserting, and if there’s a problem show the screen

  2. Make UNIQUE INDEX definitions on your table in the database, and then just try/except inserting into it. Personally I like to leave data integrity to the database where appropriate, and ensuring UNIQUE-ness is one of those times in my opinion. I use this method for a MySQL DB and it looks like this in practice.

My personal solution would be to make your name column or whatever you call it in your table UNIQUE. Then, you can do something like this -

import java.lang.Exception
try:
    #Your insert statement here
except Exception, e:
	# Catches any python errors
except java.lang.Exception, e:
    #SQL Integrity errors come as Java errors so you need java.lang.Exception
    if "Duplicate entry" in str(e.cause):
        #Do something to let user know its a duplicate

One thing to note - The error you get from a Integrity error is probably different depending on what database you so, so your if "Duplicate entry" in str(e.cause) may have to be different. But once you make a unique constraint, you can easily trigger the error and see exactly what you get.

7 Likes