Expose sqlite3 connection inTransaction as read-only in_transaction attribute

[bitdancer]

BPO	8845
Nosy	@loewis, @pitrou, @vstinner, @ned-deily, @bitdancer, @florentx
Files	sqlite.in_transaction.patch dbapi.patch: unit-test added sqlite3_in_transaction.patch: complete patch

^{Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.}

Show more details

GitHub fields:

assignee = 'https://github.com/bitdancer'
closed_at = <Date 2010-10-17.19:49:56.356>
created_at = <Date 2010-05-28.17:54:08.166>
labels = ['extension-modules', 'OS-mac', 'type-feature', 'easy']
title = 'Expose sqlite3 connection inTransaction as read-only in_transaction attribute'
updated_at = <Date 2010-10-17.21:19:15.019>
user = 'https://github.com/bitdancer'

bugs.python.org fields:

activity = <Date 2010-10-17.21:19:15.019>
actor = 'r.david.murray'
assignee = 'r.david.murray'
closed = True
closed_date = <Date 2010-10-17.19:49:56.356>
closer = 'loewis'
components = ['Extension Modules', 'macOS']
creation = <Date 2010-05-28.17:54:08.166>
creator = 'r.david.murray'
dependencies = []
files = ['17482', '17486', '17500']
hgrepos = []
issue_num = 8845
keywords = ['patch', 'easy', 'buildbot']
message_count = 19.0
messages = ['106683', '106686', '106687', '106688', '106689', '106690', '106694', '106761', '106821', '107617', '107619', '114712', '116921', '117573', '118884', '118895', '118911', '118971', '118985']
nosy_count = 10.0
nosy_names = ['loewis', 'ghaering', 'exarkun', 'janssen', 'pitrou', 'vstinner', 'ned.deily', 'r.david.murray', 'flox', 'l0nwlf']
pr_nums = []
priority = 'normal'
resolution = 'fixed'
stage = 'resolved'
status = 'closed'
superseder = None
type = 'enhancement'
url = 'https://bugs.python.org/issue8845'
versions = ['Python 3.2']

[bitdancer]

I have a use case where I'd like to be able to check whether or not there is an uncommitted transaction. The use case is a REPL database editor. If the user issues the 'save' command a commit is done. When they quit the application, I'd like to be able to prompt them with a 'save or discard' if and only if they have made changes since the last save. Exposing the connection's inTransaction attribute would allow me to do this. The attached patch does this as a read only attribute named in_transaction. I'll add unit tests if this idea isn't rejected.

[l0nwlf]

Tested this patch, works perfectly fine. Also it suits for the particular use case which David mentioned where there is no better alternate approach.

[exarkun]

If the user issues the 'save' command a commit is done. When they quit the application, I'd like to be able to prompt them with a 'save or discard' if and only if they have made changes since the last save.

Isn't this the same as "if they have issued any non-SELECT" statements? And isn't that pretty easy to track?

(Not that I'm opposed to making the SQLite3 wrapper more complete.)

[l0nwlf]

>>> conn = sqlite3.connect('dbdump.sqlite')
>>> c = conn.cursor()
>>> conn.in_transaction
False
>>> c.execute('CREATE TABLE foo (id integer, name text)')
<sqlite3.Cursor object at 0x1004a7730>
>>> conn.in_transaction
False

It gives True for Insert and False for Select. perfect Ok. However It should give True when we Create tables ?

Adding unit-test which upon showing this behavior fails the test.

[bitdancer]

@exarkun: yes, but since the module is already tracking that information, it seems silly to duplicate it in my code, especially since that duplication could include data-losing bugs.

@l0nwlf: its behaviour is in accord with the module documentation, which talks about the fact that issuing a create statement (for example) commits the current transaction. So in_transaction is an accurate representation of what sqlite3 is actually doing.

[l0nwlf]

Ok then.

Uploading unit-test which takes value of in_transaction as False after issuing a Create statement. It passes with the patch applied.

[loewis]

The patch lacks documentation. Otherwise, I think it's fine.

[bitdancer]

Here is a complete patch, including documentation. I tweaked Shashwat's unit test a bit, and added one to make sure the attribute is read only.

I'll apply this soonish if there are no objections.

[bitdancer]

Committed in r81632.

[vstinner]

Reopen the issue: the test fails on Sparc.

http://www.python.org/dev/buildbot/builders/sparc%20Ubuntu%203.x/builds/222

======================================================================
FAIL: CheckInTransaction (sqlite3.test.dbapi.ConnectionTests)
----------------------------------------------------------------------

Traceback (most recent call last):
  File "/home/pybot/buildarea/3.x.klose-ubuntu-sparc/build/Lib/sqlite3/test/dbapi.py", line 152, in CheckInTransaction
    self.assertEqual(cx.in_transaction, True)
AssertionError: False != True

[vstinner]

Same error on builder "PPC Leopard 3.x".

http://www.python.org/dev/buildbot/builders/PPC%20Leopard%203.x/builds/87/steps/test/logs/stdio

test_sqlite
test test_sqlite failed -- Traceback (most recent call last):
  File "/Users/buildbot/buildarea/3.x.parc-leopard-1/build/Lib/sqlite3/test/dbapi.py", line 152, in CheckInTransaction
    self.assertEqual(cx.in_transaction, True)
AssertionError: False != True

[bitdancer]

The sparc ubuntu buildbot appears to no longer exist. The sparc solaris buildbot doesn't have a problem with it. So right now only PPC Tiger is failing. An endianness issue was suggested, but since T_BOOL is used by _io, and that is tested and passes on the PPC buildbot, I'm thinking that is probably not the problem.

Antoine also suggested it might be the particular version of sqlite in use on the buildbot, but I don't currently know what version that is. Bill, can you clue me in?

[pitrou]

Antoine also suggested it might be the particular version of sqlite in
use on the buildbot, but I don't currently know what version that is.
Bill, can you clue me in?

You could simply print out sqlite3.sqlite_version at the beginning of test_sqlite when it is run in verbose mode, as is done in test_ssl.

[bitdancer]

Added version print to test_sqlite in r85807.

[janssen]

PPC Tiger is using Python 2.7, so it's 3.6.11.

Python 3.X also seems to be failing the sqlite tests on PPC Leopard. Is the required version # different between 3.1 and 3.x?

[bitdancer]

The attribute and test aren't in 3.1 (or 2.7), so this issue only applies to 3.x trunk.

[ned-deily]

FYI, in a recent py3k installer build test using the same installer image (2-way i386/ppc universal with statically linked sqlite 3.6.11), the test fails on ppc G3 (10.4), ppc g4 (10.5) but passes on i386 (10.6) so I'd go along with Bill's big- vs little-endian hypothesis.

[loewis]

This is now fixed in r85660. The field associated with a T_BOOL must be of type char, so I changed the type of inTransaction.

[bitdancer]

Thank you, Martin.