Rocksolid Light

News from da outaworlds

mail  files  register  groups  login

Message-ID:  

You will outgrow your usefulness.

comp / comp.lang.python / Re: psycopg2: proper positioning of .commit() within try: except: blocks

SubjectAuthor
o Re: psycopg2: proper positioning of .commit() within try: except: blocksKarsten Hilbert

1
Subject: Re: psycopg2: proper positioning of .commit() within try: except: blocks
From: Karsten Hilbert
Newsgroups: comp.lang.python
Date: Sat, 7 Sep 2024 20:45 UTC
References: 1 2 3 4 5
Path: eternal-september.org!news.eternal-september.org!feeder3.eternal-september.org!news.gegeweb.eu!gegeweb.org!fdn.fr!3.eu.feeder.erje.net!feeder.erje.net!fu-berlin.de!uni-berlin.de!not-for-mail
From: Karsten.Hilbert@gmx.net (Karsten Hilbert)
Newsgroups: comp.lang.python
Subject: Re: psycopg2: proper positioning of .commit() within try: except:
blocks
Date: Sat, 7 Sep 2024 22:45:24 +0200
Lines: 219
Sender: <karsten.hilbert@gmx.net>
Message-ID: <mailman.45.1725741933.2917.python-list@python.org>
References: <Ztx1sZMGTnA3eLP1@hermes.hilbert.loc>
<7cb50df2-9c76-477f-91c9-e149c7637104@aklaver.com>
<ZtytJMhyvtExPxfF@hermes.hilbert.loc>
<5ee80b84-f04b-454d-ab39-45572e0751a1@aklaver.com>
<Zty7ZCMwKQl4C4Id@hermes.hilbert.loc>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
X-Trace: news.uni-berlin.de lGbS99v5IEv6+jUg0kSLzgJZoTtnHTM6V8iquFfy5C1g==
Cancel-Lock: sha1:wFPwvbfCh+4lY7GLl6wBfiEYsVA= sha256:NYS7O7+S1m98dFKhoVo05IXZ6vZz2n9n7tAvI1cpJTo=
Return-Path: <karsten.hilbert@gmx.net>
X-Original-To: python-list@python.org
Delivered-To: python-list@mail.python.org
Authentication-Results: mail.python.org; dkim=pass
reason="2048-bit key; unprotected key"
header.d=gmx.net header.i=karsten.hilbert@gmx.net
header.b=gz5ATRNX; dkim-adsp=pass; dkim-atps=neutral
X-Spam-Status: OK 0.001
X-Spam-Evidence: '*H*': 1.00; '*S*': 0.00; 'def': 0.04; 'explicitly':
0.07; 'received:212.227': 0.07; '"""': 0.09; 'cc:addr:python-
list': 0.09; 'elif': 0.09; 'else:': 0.09; 'finalized': 0.09;
'gpg': 0.09; 'karsten': 0.09; 'schrieb': 0.09; 'skip:_ 20': 0.09;
'then.': 0.09; 'typically': 0.09; "'':": 0.16; '1713': 0.16;
'2024': 0.16; 'args:': 0.16; 'assert': 0.16; 'column': 0.16;
'dicts': 0.16; 'elsewhere': 0.16; 'executed': 0.16; 'fetching':
0.16; 'lambda': 0.16; 'none)': 0.16; 'none:': 0.16; 'queries.':
0.16; 'skip:_ 60': 0.16; 'subject:() ': 0.16; 'subject:proper':
0.16; 'subject:try': 0.16; 'tuple': 0.16; 'usefully': 0.16;
'problem': 0.16; 'instead': 0.17; 'cc:addr:python.org': 0.20;
'exception': 0.22; 'returns': 0.22; 'sat,': 0.22; 'skip:_ 10':
0.22; 'code': 0.23; 'cc:2**1': 0.23; 'skip:p 30': 0.23; 'actual':
0.25; 'cannot': 0.25; "isn't": 0.27; 'function': 0.27; 'error':
0.29; 'skip:_ 40': 0.31; 'default': 0.31; 'raise': 0.31; 'false,':
0.32; 'privilege': 0.32; 'returning': 0.32; 'right,': 0.32; 'sep':
0.32; 'unless': 0.32; 'but': 0.32; 'mean': 0.34; 'header:In-Reply-
To:1': 0.34; 'running': 0.34; 'failed': 0.35; 'close': 0.35;
'errors': 0.36; 'source': 0.36; "skip:' 10": 0.37; 'skip:_ 30':
0.37; 'best,': 0.38; 'single': 0.39; 'list': 0.39; 'connection':
0.61; 'skip:i 20': 0.62; 'received:212': 0.62; 'involved': 0.63;
'true': 0.63; 'skip:r 20': 0.64; 'full': 0.64; 'skip:g 30': 0.64;
'transaction': 0.64; 'skip:t 30': 0.67; 'and,': 0.69; 'del': 0.70;
'deal': 0.73; 'field': 0.78; 'queries': 0.81; 'returned': 0.81;
'exceptions': 0.84; 'subject:within': 0.84; 'transaction.': 0.84;
'transactions': 0.84; 'true:': 0.84; 'retained': 0.91; 'will,':
0.91; 'skip:q 20': 0.95; 'positions': 0.96
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net;
s=s31663417; t=1725741926; x=1726346726; i=karsten.hilbert@gmx.net;
bh=qQDDhiDNx2sBON56u08/468ME3wUZXKF9UQ/z7Awa+Y=;
h=X-UI-Sender-Class:Date:From:To:Cc:Subject:Message-ID:References:
MIME-Version:Content-Type:In-Reply-To:Content-Transfer-Encoding:
cc:content-transfer-encoding:content-type:date:from:message-id:
mime-version:reply-to:subject:to;
b=gz5ATRNXaB5yKfWesB5GtkFazBkl1OQPAJ/QnR53EJb7n+w6lpnO/Paj2fo00SaM
YPJc8jpH/u4IIVyCvGbZL+U6fKxaFyN+hqITaY3W9fPLSWWyRMdtDmUsjWy1zjZKW
5DNmafnOMx8V3uBQ/vFThmb3bClbkvKKIIPw2WWln7R2PLP88OErkmo2NcsxB+xY8
BL/in62mlLb4HI9r4TEClWDD5UbFjDKo6LnKesLXdB7iUHFXD6x77d3md+uMMVZmC
mJvu4t7cCC50bKUrK22F2MBnCAevH5GeIROngyqx4Pq2lwxdO35XE9NaMTsFQ+rVN
CWDgOMXyoh8/i37Rfw==
X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a
Content-Disposition: inline
In-Reply-To: <5ee80b84-f04b-454d-ab39-45572e0751a1@aklaver.com>
Ma_X_il-Followup-to: d
Re_X_turn-receipt-to: Karsten.Hilbert@gmx.net
Di_X_sposition-Notification-To: Karsten.Hilbert@gmx.net
X-Confi_X_rm-Reading-To: Karsten.Hilbert@gmx.net
X-Pri_X_ority: 2 (High)
X-Provags-ID: V03:K1:7wLcXaJdPT7iGY/Eu6aY639RZxOO7E85bUBmg0TBNbVYs2lw4+e
2N2I3JnPj5fKiq0MXL9lyi7HkZgLrCA7lHDPKTxT/VHgjuHyaUMx1GyvsJiYzPFayCjbSTB
pdrMJq8nlu0KjrQBB8z9NNCmXX53jKhXmDoKWaz9rxTaQuAkhq1sBswg1jDX8QeuLIhkin2
dHruDEFpGNuzzrH9GQZyQ==
X-Spam-Flag: NO
UI-OutboundReport: notjunk:1;M01:P0:SBu5rJC1vkc=;sbEWshFAMIj+kkOEdz5X5UOO5XO
MF9HhzE8/qyL3dVFRP1ovD19cGxgtpg5kj6ffqiACwWdBhmEAKEkm+zViNIV+ngK/QAPOs0LQ
xBOUIu30ZnqdfDmBCPMDvg6Sp1YfR4DIKe3d7u3NdHDuEyLOc4D8jeqdCj2K3OQQuxJ2PU/QK
LUBn7iZdL9bK8O9krhODxhlptQ1xYq0Uqu2DqECIHwPNtxkS38RhtpspKBebdYSykSa9dotQc
0KWsIE/o4xhSaJJ9DtRQqqqkDzO0VrzYVfaui6s7dOGf43WzwjUtesYd/D4JrLr0M+ODj6Z/g
F4tF2Izn6Rdl9b5Jck06bAIiKFGfobLsS7/gu4QuUOIZNKR52t0R5fLlkfe23BoHH3922Ci2Q
gVH9/yftQIZKVmDK6kavyrv3IyHlPAXS5Vnwf194M9klnAcXKQhWAI4jSWUKlMi0NC0lIBRG4
f1Z6DZUEpE10HQ4mMC/tPExeIO/t6JaFbpyh2l7EohtS1Do0B9d//nKaY3+wsFVLk4hmEmWAf
oA5+9oMh/L3BmLFXVOtPYtC8Lp5hoPwzhzQ5BCOaz2ebOolQ3+kRC+em1NN6PMAdURQyL73OT
HBNjsgyMmE/mmcfZD+h+neOFd6UdmmaH5uZ8uTJVeHsRMkBYkZ9mZBWMsMEwQGi1swGrb3DgL
jz0W2rJl/LQ6IMvOvF/0oee6D2Aml4RyOP3en/kjOCrkCn7LXA3aTfdn3ni0b1y2LVKhPye+m
roqjymoy/3axEgiHzCVSpWlw/EDTjY3cno2YsZC1Iesn7yjTq3SSgudygWa2LN7kyRSjLfT4p
r4JNvP+Zc0nXrslfXogChyEg==
X-BeenThere: python-list@python.org
X-Mailman-Version: 2.1.39
Precedence: list
List-Id: General discussion list for the Python programming language
<python-list.python.org>
List-Unsubscribe: <https://mail.python.org/mailman/options/python-list>,
<mailto:python-list-request@python.org?subject=unsubscribe>
List-Archive: <https://mail.python.org/pipermail/python-list/>
List-Post: <mailto:python-list@python.org>
List-Help: <mailto:python-list-request@python.org?subject=help>
List-Subscribe: <https://mail.python.org/mailman/listinfo/python-list>,
<mailto:python-list-request@python.org?subject=subscribe>
X-Mailman-Original-Message-ID: <Zty7ZCMwKQl4C4Id@hermes.hilbert.loc>
X-Mailman-Original-References: <Ztx1sZMGTnA3eLP1@hermes.hilbert.loc>
<7cb50df2-9c76-477f-91c9-e149c7637104@aklaver.com>
<ZtytJMhyvtExPxfF@hermes.hilbert.loc>
<5ee80b84-f04b-454d-ab39-45572e0751a1@aklaver.com>
View all headers

Am Sat, Sep 07, 2024 at 01:03:34PM -0700 schrieb Adrian Klaver:

> In the case you show you are doing commit() before the close() so any errors in the
> transactions will show up then. My first thought would be to wrap the commit() in a
> try/except and deal with error there.

Right, and this was suggested elsewhere ;)

And, yeah, the actual code is much more involved :-D

#------------------------------------------------------------------------
def __safely_close_cursor_and_rollback_close_conn(close_cursor=None, rollback_tx=None, close_conn=None):
if close_cursor:
try:
close_cursor()
except PG_ERROR_EXCEPTION as pg_exc:
_log.exception('cannot close cursor')
gmConnectionPool.log_pg_exception_details(pg_exc)
if rollback_tx:
try:
# need to rollback so ABORT state isn't retained in pooled connections
rollback_tx()
except PG_ERROR_EXCEPTION as pg_exc:
_log.exception('cannot rollback transaction')
gmConnectionPool.log_pg_exception_details(pg_exc)
if close_conn:
try:
close_conn()
except PG_ERROR_EXCEPTION as pg_exc:
_log.exception('cannot close connection')
gmConnectionPool.log_pg_exception_details(pg_exc)

#------------------------------------------------------------------------
def run_rw_queries (
link_obj:_TLnkObj=None,
queries:_TQueries=None,
end_tx:bool=False,
return_data:bool=None,
get_col_idx:bool=False,
verbose:bool=False
) -> tuple[list[dbapi.extras.DictRow], dict[str, int] | None]:
"""Convenience function for running read-write queries.

Typically (part of) a transaction.

Args:
link_obj: None, cursor, connection
queries:

* a list of dicts [{'cmd': <string>, 'args': <dict> or <tuple>)
* to be executed as a single transaction
* the last query may usefully return rows, such as:

SELECT currval('some_sequence');
or
INSERT/UPDATE ... RETURNING some_value;

end_tx:

* controls whether the transaction is finalized (eg.
COMMITted/ROLLed BACK) or not, this allows the
call to run_rw_queries() to be part of a framing
transaction
* if link_obj is a *connection* then "end_tx" will
default to False unless it is explicitly set to
True which is taken to mean "yes, you do have full
control over the transaction" in which case the
transaction is properly finalized
* if link_obj is a *cursor* we CANNOT finalize the
transaction because we would need the connection for that
* if link_obj is *None* "end_tx" will, of course, always
be True, because we always have full control over the
connection, not ending the transaction would be pointless

return_data:

* if true, the returned data will include the rows
the last query selected
* if false, it returns None instead

get_col_idx:

* True: the returned tuple will include a dictionary
mapping field names to column positions
* False: the returned tuple includes None instead of a field mapping dictionary

Returns:

* (None, None) if last query did not return rows
* ("fetchall() result", <index>) if last query returned any rows and "return_data" was True

* for *index* see "get_col_idx"
"""
assert queries is not None, '<queries> must not be None'

if link_obj is None:
conn = get_connection(readonly = False)
curs = conn.cursor()
conn_close = conn.close
tx_commit = conn.commit
tx_rollback = conn.rollback
curs_close = curs.close
notices_accessor = conn
else:
conn_close = lambda *x: None
tx_commit = lambda *x: None
tx_rollback = lambda *x: None
curs_close = lambda *x: None
if isinstance(link_obj, dbapi._psycopg.cursor):
curs = link_obj
notices_accessor = curs.connection
elif isinstance(link_obj, dbapi._psycopg.connection):
if end_tx:
tx_commit = link_obj.commit
tx_rollback = link_obj.rollback
curs = link_obj.cursor()
curs_close = curs.close
notices_accessor = link_obj
else:
raise ValueError('link_obj must be cursor, connection or None but not [%s]' % link_obj)

for query in queries:
try:
args = query['args']
except KeyError:
args = None
try:
curs.execute(query['cmd'], args)
if verbose:
gmConnectionPool.log_cursor_state(curs)
for notice in notices_accessor.notices:
_log.debug(notice.replace('\n', '/').replace('\n', '/'))
del notices_accessor.notices[:]
# DB related exceptions
except dbapi.Error as pg_exc:
_log.error('query failed in RW connection')
gmConnectionPool.log_pg_exception_details(pg_exc)
for notice in notices_accessor.notices:
_log.debug(notice.replace('\n', '/').replace('\n', '/'))
del notices_accessor.notices[:]
__safely_close_cursor_and_rollback_close_conn (
curs_close,
tx_rollback,
conn_close
)
# privilege problem ?
if pg_exc.pgcode == PG_error_codes.INSUFFICIENT_PRIVILEGE:
details = 'Query: [%s]' % curs.query.decode(errors = 'replace').strip().strip('\n').strip().strip('\n')
if curs.statusmessage != '':
details = 'Status: %s\n%s' % (
curs.statusmessage.strip().strip('\n').strip().strip('\n'),
details
)
if pg_exc.pgerror is None:
msg = '[%s]' % pg_exc.pgcode
else:
msg = '[%s]: %s' % (pg_exc.pgcode, pg_exc.pgerror)
raise gmExceptions.AccessDenied (
msg,
source = 'PostgreSQL',
code = pg_exc.pgcode,
details = details
)

# other DB problem
gmLog2.log_stack_trace()
raise

# other exception
except Exception:
_log.exception('error running query in RW connection')
gmConnectionPool.log_cursor_state(curs)
for notice in notices_accessor.notices:
_log.debug(notice.replace('\n', '/').replace('\n', '/'))
del notices_accessor.notices[:]
gmLog2.log_stack_trace()
__safely_close_cursor_and_rollback_close_conn (
curs_close,
tx_rollback,
conn_close
)
raise

data = None
col_idx = None
if return_data:
try:
data = curs.fetchall()
except Exception:
_log.exception('error fetching data from RW query')
gmLog2.log_stack_trace()
__safely_close_cursor_and_rollback_close_conn (
curs_close,
tx_rollback,
conn_close
)
raise

if get_col_idx:
col_idx = get_col_indices(curs)
curs_close()
tx_commit()
conn_close()
return (data, col_idx)

#------------------------------------------------------------------------

Best,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

1

rocksolid light 0.9.8
clearnet tor