Python, SQLite3: cursor returns duplicates when a commit intervenes

Question

This Python code creates a table, inserts three rows into it and iterates through the rows, with intervening commits before the cursor has been fully exhausted. Why does it return five rows instead of three? If the intervening commit is removed, the number of returned rows is three as expected. Or is it expected that a commit (which doesn't even touch the table in question) invalidates a cursor?

Edit: Added a forgotten commit (which makes the issue disappear) and an insert to an unrelated table (which makes the issue appear again).

#!/usr/bin/env python3

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 is:

count=3
(0,)
(1,)
(0,)
(1,)
(2,)
count=3

Generally, with N rows inserted, N+2 rows are returned by the iterator, apparently always with the first two duplicated.

You are not using .fetchone() in the loop, making your title inaccurate at best. — Martijn Pieters
– Martijn Pieters, Commented Dec 23, 2014 at 16:16
Wow, good find. I just ran into this issue as well and was thoroughly puzzled until I found this post. This seems like a serious bug to me—unless this behavior is actually documented somewhere in the sqlite3 docs? — Dan Lenski
– Dan Lenski, Commented Apr 11, 2016 at 21:31
Looks like this bug has been posted at bugs.python.org, including your example above: bugs.python.org/issue23129 — Dan Lenski
– Dan Lenski, Commented Apr 11, 2016 at 21:33

user590028 · Accepted Answer · 2014-12-24 01:36:53Z

4

Your followup comment disturbed me (particularly because it was clear you were right). So I spent some time studying the source code to the python _sqlite.c library (https://svn.python.org/projects/python/trunk/Modules/_sqlite/).

I think the problem is how the sqlite Connection object is handling cursors. Internally, Connection objects maintain a list of cursors AND prepared statements. The nested db.execute('INSERT ...') call resets the list of prepared statements associated to the Connection object.

The solution is to not rely on the shortcut execute() method's automatic cursor management, and to explicitly hold a reference to the running Cursor. Cursors maintain their own prepared statement lists which are separate from Connection objects.

You can either explicitly create a cursor OR invoke fetchall() on the db.execute() call. Example of the later:

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').fetchall():
    print(col)
    db.execute('INSERT INTO tbl2 VALUES (?)', col)
    db.commit()

print('count=' + str(db.execute('SELECT count(*) FROM tbl').fetchone()[0]))

The output is as expected:

count=3
(0,)
(1,)
(2,)
count=3

If the fetchall() approach is memory prohibitive, then you may need to fall back to relying on isolation between two database connections (https://www.sqlite.org/isolation.html). Example:

db1 = sq.connect('temp.db')

db1.execute('CREATE TABLE tbl (col INTEGER)')
db1.execute('CREATE TABLE tbl2 (col INTEGER)')
db1.executemany('INSERT INTO tbl (col) VALUES (?)', [(0,), (1,), (2,)])
db1.commit()

print('count=' + str(db1.execute('SELECT count(*) FROM tbl').fetchone()[0]))

db2 = sq.connect('temp.db')

# Read and print the values just inserted into tbl
for col in db1.execute('SELECT col FROM tbl').fetchall():
    print(col)
    db2.execute('INSERT INTO tbl2 VALUES (?)', col)
    db2.commit()

print('count=' + str(db1.execute('SELECT count(*) FROM tbl').fetchone()[0]))

edited Dec 24, 2014 at 1:36

answered Dec 23, 2014 at 16:23

user590028

11.8k5 gold badges43 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sami Liedes Over a year ago

Ok, that seems to fix the issue, but I think it probably only masks it... Adding an insert to a second (unrelated) table will make it happen again. I'll edit my question.

Sami Liedes Over a year ago

Explicitly creating a cursor doesn't seem to help either (I created separate cursors for the "SELECT col FROM tbl" and "INSERT INTO tbl2"; I still get the same behavior as before). fetchall() obviously works, but might be unfeasible for large data sets...

user590028 Over a year ago

I'm not satisfied with this answer. I see I inadvertently used fetchall() in the second example as well. I've taken this question over to the sqlite mailing list to get assistance. I'll report back with what I learn.

user590028 Over a year ago

I found a fix...but more research is required. It's definitely how the connection manager is handling cursors. If you set your code to autocommit mode, it works as you expected. ie: db = sq.connect(':memory:', isolation_level=None). I'm still researching this...but we're definitely circling in to land on this one.

Collectives™ on Stack Overflow

Python, SQLite3: cursor returns duplicates when a commit intervenes

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related