13👍
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.
12👍
Update as of 9-6-2017:
A pretty elegant way to do this is to try
/except IntegrityError as exc
, and then use some useful attributes on exc.__cause__
and exc.__cause__.diag
(a diagnostic class that gives you some other super relevant information on the error at hand – you can explore it yourself with dir(exc.__cause__.diag)
).
The first one you can use was described above. To make your code more future proof you can reference the psycopg2 codes directly, and you can even check the constraint that was violated using the diagnostic class I mentioned above:
except IntegrityError as exc:
from psycopg2 import errorcodes as pg_errorcodes
assert exc.__cause__.pgcode == pg_errorcodes.UNIQUE_VIOLATION
assert exc.__cause__.diag.constraint_name == 'tablename_colA_colB_unique_constraint'
edit for clarification: I have to use the __cause__
accessor because I’m using Django, so to get to the psycopg2 IntegrityError class I have to call exc.__cause__
- Background processing in Django without Celery
- Database table names with Django
- Make Django return response as a "different filename"
- How to assert django uses particular template in pytest
- How to get all objects by instance in django
3👍
It could be better to use:
try:
obj, created = cls.objects.get_or_create(user_id=user.id, email=user.email)
except IntegrityError:
....
as in https://docs.djangoproject.com/en/dev/ref/models/querysets/#get-or-create
The IntegrityError
should be raised only in the case there’s a NOT NULL
constraint violation.
Furthermore you can use created
flag to know if the object already existed.