Path: csiph.com!fu-berlin.de!uni-berlin.de!not-for-mail
From: "Frank Millman" <frank@chagford.com>
Newsgroups: comp.lang.python
Subject: Re: Handling transactions in Python DBI module
Date: Thu, 11 Feb 2016 07:28:47 +0200
Lines: 27
Message-ID: <mailman.31.1455168546.22075.python-list@python.org>
References: <92D3C964-0323-46EE-B770-B89E7E7E6D36@ravnalaska.net> <n9h4s7$aqb$1@ger.gmane.org> <CAPTjJmphJvtCKUB6Qr-vp_1epEWxBgQxmfKEPMOhQp3pAPGG+A@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Trace: news.uni-berlin.de 9JPXX1q/32Gnowqe+Wlc5AzDsNO9OQoMXgucvkL4Bj3Q==
Return-Path: <python-python-list@m.gmane.org>
X-Original-To: python-list@python.org
Delivered-To: python-list@mail.python.org
X-Spam-Status: OK 0.000
X-Spam-Evidence: '*H*': 1.00; '*S*': 0.00; 'subject:Python': 0.05; 'important,': 0.07; 'executes': 0.09; 'postgresql,': 0.09; 'received:80.91': 0.09; 'received:80.91.229': 0.09; 'received:gmane.org': 0.09; 'received:list': 0.09; 'subject:module': 0.09; "'begin": 0.16; 'conn': 0.16; 'conn,': 0.16; 'reason.': 0.16; 'received:80.91.229.3': 0.16; 'received:io': 0.16; 'received:plane.gmane.org': 0.16; 'received:psf.io': 0.16; 'selects': 0.16; 'skip:n 70': 0.16; 'select': 0.23; 'wrote': 0.23; 'header:In-Reply-To:1': 0.24; 'header:X-Complaints-To:1': 0.26; 'not.': 0.27; 'specify': 0.27; "skip:' 10": 0.28; 'locks': 0.29; 'creating': 0.30; 'transaction': 0.30; 'lock': 0.33; 'basic': 0.36; 'to:addr:python-list': 0.36; 'subject:: ': 0.37; 'received:org': 0.37; 'skip:p 20': 0.38; 'does': 0.39; 'to:addr:python.org': 0.40; 'forget': 0.60; 'your': 0.60; 'frank': 0.72; 'one).': 0.84
X-Injected-Via-Gmane: http://gmane.org/
X-Gmane-NNTP-Posting-Host: 197.89.154.180
In-Reply-To: <CAPTjJmphJvtCKUB6Qr-vp_1epEWxBgQxmfKEPMOhQp3pAPGG+A@mail.gmail.com>
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 15.4.3502.922
X-MimeOLE: Produced By Microsoft MimeOLE V15.4.3502.922
X-BeenThere: python-list@python.org
X-Mailman-Version: 2.1.21rc2
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: <http://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>
Xref: csiph.com comp.lang.python:102784

"Chris Angelico"  wrote in message 
news:CAPTjJmphJvtCKUB6Qr-vp_1epEWxBgQxmfKEPMOhQp3pAPGG+A@mail.gmail.com...
>
> When I advise my students on basic databasing concepts, I recommend
> this structure:
>
> conn = psycopg2.connect(...)
>
> with conn, conn.cursor() as cur:
>     cur.execute(...)
>

Does this automatically issue a 'conn.commit()' on exit?

I have found that this is important, for the following reason.

If you issue a bare SELECT to PostgreSQL, it executes it without creating 
any locks. However, if it is inside a transaction, it does create a lock (I 
forget exactly which one).

Because psycopg2 silently executes 'BEGIN TRANSACTION', your SELECTs always 
happen inside a transaction whether you specify it or not. If you do not 
issue a conn.commit(), the locks do not get cleared.

Frank