python - IntegrityError: distinguish between unique constraint and not null violations

Question

I have this code:

try:
    principal = cls.objects.create(
        user_id=user.id,
        email=user.email,
        path='something'
    )
except IntegrityError:
    principal = cls.objects.get(
        user_id=user.id,
        email=user.email
    )

It tries to create a user with the given id and email, and if there already exists one - tries to get the existing record.

I know this is a bad construction and it will be refactored anyway. But my question is this:

How do i determine what kind of IntegrityError has happened: the one related to unique constraint violation (there is unique key on (user_id, email)) or the one related to not null constraint (path cannot be null)?

Answer 1

psycopg2 provides the SQLSTATE with the exception as the pgcode member, which gives you quite fine-grained error information to match on.

python3
>>> import psycopg2
>>> conn = psycopg2.connect("dbname=regress")
>>> curs = conn.cursor()
>>> try:
...     curs.execute("INVALID;")
... except Exception as ex:
...     xx = ex
>>> xx.pgcode
'42601'

See Appendix A: Error Codes in the PostgreSQL manual for code meanings. Note that you can match coarsely on the first two chars for broad categories. In this case I can see that SQLSTATE 42601 is syntax_error in the Syntax Error or Access Rule Violation category.

The codes you want are:

23505   unique_violation
23502   not_null_violation

so you could write:

try:
    principal = cls.objects.create(
        user_id=user.id,
        email=user.email,
        path='something'
    )
except IntegrityError as ex:
    if ex.pgcode == '23505':
        principal = cls.objects.get(
            user_id=user.id,
            email=user.email
        )
    else:
        raise

That said, this is a bad way to do an upsert or merge. @pr0gg3d is presumably right in suggesting the right way to do it with Django; I don't do Django so I can't comment on that bit. For general info on upsert/merge see depesz's article on the topic.