New submission from Jim Carroll: I reported this to the sqlite mailing list, and the comments I received suggested the problem may by the python sqlite connector issue, so I'm opening this as a bug report.
I understand that performing a SELECT and nested COMMIT on the same table is not supported in sqlite, but I would have expected a COMMIT on a separate table would not be a problem. Some test code in python however reveals that performing the COMMIT disrupts the SELECT statement, and causes duplicate data to be returned. If this is not a supported operation, would you mind pointing me to the docs so I can understand it better? Example #!/usr/bin/env python import sqlite3 as sq db = sq.connect(':memory:') db.execute('CREATE TABLE tbl (col INTEGER)') db.execute('CREATE TABLE tbl2 (col INTEGER)') db.executemany('INSERT INTO tbl (col) VALUES (?)', [(0,), (1,), (2,)]) db.commit() print('count=' + str(db.execute('SELECT count(*) FROM tbl').fetchone()[0])) # Read and print the values just inserted into tbl for col in db.execute('SELECT col FROM tbl'): print(col) db.execute('INSERT INTO tbl2 VALUES (?)', col) db.commit() print('count=' + str(db.execute('SELECT count(*) FROM tbl').fetchone()[0])) The output we see is: count=3 (0,) (1,) (0,) (1,) (2,) count=3 The expected output was: count=3 (0,) (1,) (2,) count=3 Tested on Linux: sqlite version 3.7.13 sqlite3 connector version 2.6.0 # uname -a Linux hostname 3.16-0.bpo.3-amd64 #1 SMP Debian 3.16.5-1~bpo70+1 (2014-11-02) x86_64 GNU/Linux Also tested on Windows with identical results Sqlite version 3.6.21 Windows 7 Professional, 64-bit ---------- messages: 233178 nosy: jamercee priority: normal severity: normal status: open title: sqlite3 COMMIT nested in SELECT returns unexpected results type: behavior versions: Python 2.7 _______________________________________ Python tracker <rep...@bugs.python.org> <http://bugs.python.org/issue23129> _______________________________________ _______________________________________________ Python-bugs-list mailing list Unsubscribe: https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com