Checking for a unique constraint violation before inserting new records, is it recommended?

[alchemy1]

I'm using ORM and I've defined a unique constraint on a column, and I want to prevent inserting another row with the same value for that column. For example if you don't want 2 users with the same name.

One way to prevent it would be to take the provided name and query the user table to see if anyone has that name, and if not then insert the record. The problem with this is that in between checking for the name and inserting the name, someone else could have inserted a record with that name. So is there any advantage to doing this? Is there any better way to do it?

If not, I suppose you'd want to handle the IntegrityError that SQLAlchemy throws when it detects a unique constraint violation. But since IntegrityError handles several types of types of violations, would checking the string of the error message be the only way to ensure you're handling the correct error condition, so that you can put a specific message like "A user with that name already exists"?

sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates unique constraint "name_of_constraint"

If so, how can I use metadata to get the name of the particular constraint I have in mind, so that I can search for it in the error message string? I've tried looking through the MetaData API and have got this far but I am stuck. I can't seem to find the constraint name set on the name column here.

Base.metadata.tables.user.c.name
User.name.info

[Michael Bayer]

yes, the non-standardization of DBAPI errors is something SQLAlchemy as yet has not tried to solve, though in very recent releases it has begun to provide hooks so that these solutions can be rolled more liberally.   The way I’ve solved this in Openstack looks like this:  

https://github.com/openstack/oslo.db/blob/0265aa4e01270b8fc7cab1266b8602e1921c9ddb/oslo/db/sqlalchemy/exc_filters.py#L93

that regular expression is built upon a larger system that ultimately makes use of the SQLAlchemy handle_error() event, which is new in recent 0.9 series.    You can see that we pull the columns out of the exception message itself.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Kevin Isaac]

Any updates to this Michael? I've been researching about this but couldn't find a better solution than parsing the error message with regex. Would love to hear if there's a better way of doing this.

[Mike Bayer]

Not on my end !  The openstack code I referred towards is regex based but works very well.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.

[Jonathan Vanasco]

On Sunday, August 5, 2018 at 5:15:39 PM UTC-4, Mike Bayer wrote:

Not on my end ! The openstack code I referred towards is regex based but works very well.

I just limit what can trigger the exception and call a flush.  it's not the most performant code if you are changing many fields, but it's fine if you're just updating a username (which is what I use this for too!)


e.g. something like this

try:
  foo.username = 'foo'
  session.flush()
except sqlalchemy.exc.IntegrityError as e:
  raise UsernameTaken()