I have a perspective view where I am adding users to a database on a button event script. I want to prevent duplicate names from being added, in other words if the name already exists in the table, don’t add the entry and put an alert on the screen.
Originally I wanted to do this check in the python script. I pull down the column of names from the DB and compare the new name to be added to that list using the ‘x in list’, but the strings returned from the db have a fixed size and have padded spaces at the end. So instead of the list containing strings like “John Doe” it has "John Doe " and my comparison fails. The datatype of the column is nchar(20) which is fixed length as I understand. Is there away to make this field variable length or a better way to do this comparison in python?
check for a duplicate manually with python before inserting, and if there’s a problem show the screen
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.
You can use the LTRIM and RTRIM functions to tidy up your returned names from the SQL query. These functions remove spaces (among other things) at the start and end of the a string. But yes, as @bkarabinchak.psi said, you should also configure the name field in the table to be unique, and that exception handling is always handy to know!
No problem! For future reference you can also make multi-column unique indexes, so if your table had a firstName column and a lastName column you could make it so you only accept unique combinations of first and last names.
One caveat to this -- if the columns don't have a NOT NULL constraint you can get some unexpected results with some SQL vendors. NULL doesn't equal NULL in SQL, so you can have what seems like duplicate entries when one value is NULL.
If you need to allow NULL values, take a look and see how your database handles the uniqueness of them. If it's an issue, there are several ways around it, depending on the DB, but it's good to find out before it's a problem.
The above is for multiple columns of the same row? So, I am not certain what you're asking.
When creating a UNIQUE INDEX the question you need to answer is what makes this data UNIQUE. For instance in the example that you gave, would the values 100 | Test2 be Accepted or Rejected? If yes then you need to make a CLUSTERED INDEX. This tells the database to compare the values of both the code and desc column to determine uniqueness. The way this is done depends on the database you're using, and as @KathyApplebaum cautions above you should be aware of how your database treats NULL values if either column doesn't have a NOT NULL constraint.
If only the code determines if the row is unique or not then you would create a simple UNIQUE INDEX.
#Updated with Thrwoable
import java.lang.Throwable
try:
insertParams = {'namedQueryParam1':1, 'namedQueryParam2':'something'}
system.db.runNamedQuery("SomeInsertQuery",insertParams)
except Exception, e:
# Catches any python errors
except java.lang.Throwable, 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
updateParams = {'someNamedQueryParams':"someValue",...}
system.db.runNamedQuery("someUpdateQuery", updateParams)
My example uses MySQL 5.6. You'll have to do some testing or read your databases documentation to see what if "Duplicate entry" in str(e.cause): should be for you.
