Thread: exception psycopg.Error from psycopg2 to psycopg 3
Hi all
Porting my program from psycopg2 to psycopg 3 i found another different
behaviour
This SQL code inside a function:
RAISE EXCEPTION 'Error wrong database' USING HINT = 'You need to use a
different database', ERRCODE = 'PA002';
is catched in python code with:
try:
<execute sql function>
except psycopg.Error as er:
raise PyAppDBError(er.sqlstate, er)
With psycopg2 er.sqlstate is set to 'PA002' in psycopg3 is set to None
Is this an expected behavour ? Is there a way to get the error code in
psycopg3 like it is available in psycopg2 ?
Or maybe Am I doing something wrong ?
Thanks
--
Paolo De Stefani
On Fri, 11 Feb 2022 at 18:34, Paolo De Stefani <paolo@paolodestefani.it> wrote:
Hi Paolo,
Yes, I can see some inconsistency there. At the moment I suggest you
use `e.diag.sqlstate`, which works as expected and is available in
psycopg2 too.
In [8]: try: cnn.execute("""
...: do $$
...: begin
...: RAISE EXCEPTION 'Error wrong database' USING HINT = 'You
need to use a different database', ERRCODE = 'PA002';
...: end$$
...: language plpgsql
...: """)
...: except Exception as e: ex = e
In [10]: ex.diag.sqlstate
Out[10]: 'PA002'
In [11]: ex.sqlstate
None
What is happening is that, in psycopg 3, Error.sqlstate is a class
property and is only set for the known classes - the ones listed at
<https://www.psycopg.org/psycopg3/docs/api/errors.html#sqlstate-exceptions>.
The attribute `e.diag.sqlstate` instead comes from the error message
received from the server. When the error is received, the matching
class is looked up by sqlstate, with the basic dbapi exceptions as
fallback if the code is not known, and the server result is passed to
the exception state, so that `e.diag` is populated with all the
details (such as the message, the hint etc).
The cases I had in mind where 1) known exceptions where Error.sqlstate
and e.diag.sqlstate would match, and 2) non-server exceptions (e.g. on
connection, or raised by Python code) where Error.sqlstate and
e.diag.sqlstate are both None.
I didn't think about the case 3) where a sqlstate exists, but psycopg
doesn't know it. In this case, the result of the current
implementation is to raise an exception with the sqlstate left to None
on the class but available in diag.
ISTM that setting e.sqlstate = e.diag.sqlstate would be an
improvement. The docs describe indeed that sqlstate is expected to be
None on the DBAPI classes
(https://www.psycopg.org/psycopg3/docs/api/errors.html#psycopg.Error.sqlstate)
but that wasn't written thinking about the inconsistency above. It
makes more sense that Error.sqlstate is whatever state received, if
any.
Does it sound right?
-- Daniele
On Fri, 11 Feb 2022 at 19:47, Daniele Varrazzo <daniele.varrazzo@gmail.com> wrote: > ISTM that setting e.sqlstate = e.diag.sqlstate would be an > improvement. https://github.com/psycopg/psycopg/issues/225 -- Daniele
Yes perfect solution for me
Thank your the lightspeed response..
Il 11/02/2022 19:47 Daniele Varrazzo ha scritto:
> On Fri, 11 Feb 2022 at 18:34, Paolo De Stefani
> <paolo@paolodestefani.it> wrote:
>
> Hi Paolo,
>
> Yes, I can see some inconsistency there. At the moment I suggest you
> use `e.diag.sqlstate`, which works as expected and is available in
> psycopg2 too.
>
> In [8]: try: cnn.execute("""
> ...: do $$
> ...: begin
> ...: RAISE EXCEPTION 'Error wrong database' USING HINT = 'You
> need to use a different database', ERRCODE = 'PA002';
> ...: end$$
> ...: language plpgsql
> ...: """)
> ...: except Exception as e: ex = e
>
> In [10]: ex.diag.sqlstate
> Out[10]: 'PA002'
>
> In [11]: ex.sqlstate
> None
>
> What is happening is that, in psycopg 3, Error.sqlstate is a class
> property and is only set for the known classes - the ones listed at
> <https://www.psycopg.org/psycopg3/docs/api/errors.html#sqlstate-exceptions>.
>
> The attribute `e.diag.sqlstate` instead comes from the error message
> received from the server. When the error is received, the matching
> class is looked up by sqlstate, with the basic dbapi exceptions as
> fallback if the code is not known, and the server result is passed to
> the exception state, so that `e.diag` is populated with all the
> details (such as the message, the hint etc).
>
> The cases I had in mind where 1) known exceptions where Error.sqlstate
> and e.diag.sqlstate would match, and 2) non-server exceptions (e.g. on
> connection, or raised by Python code) where Error.sqlstate and
> e.diag.sqlstate are both None.
>
> I didn't think about the case 3) where a sqlstate exists, but psycopg
> doesn't know it. In this case, the result of the current
> implementation is to raise an exception with the sqlstate left to None
> on the class but available in diag.
>
> ISTM that setting e.sqlstate = e.diag.sqlstate would be an
> improvement. The docs describe indeed that sqlstate is expected to be
> None on the DBAPI classes
> (https://www.psycopg.org/psycopg3/docs/api/errors.html#psycopg.Error.sqlstate)
> but that wasn't written thinking about the inconsistency above. It
> makes more sense that Error.sqlstate is whatever state received, if
> any.
>
> Does it sound right?
>
> -- Daniele
--
Paolo De Stefani
Am Fri, Feb 11, 2022 at 07:47:22PM +0100 schrieb Daniele Varrazzo: > ISTM that setting e.sqlstate = e.diag.sqlstate would be an > improvement. The docs describe indeed that sqlstate is expected to be > None on the DBAPI classes > (https://www.psycopg.org/psycopg3/docs/api/errors.html#psycopg.Error.sqlstate) > but that wasn't written thinking about the inconsistency above. It > makes more sense that Error.sqlstate is whatever state received, if > any. > > Does it sound right? +1 Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B