CODEWITHSUNDEEP
pythonsqlite
ShankarG
ShankarG

Reputation: 1165

Python sqlite3 module returning incorrect rowid values when inserting record

I'm having a very peculiar problem with Python's sqlite3 module - and even more oddly, it only occurs with one particular table, but it occurs with that table whichever database it's in.

So, I create a table in an sqlite3 database with the following:

CREATE TABLE BlockedNumbers (MobileNumber integer PRIMARY KEY NOT NULL, BlockedTimes integer);

Then run this in Python:

import sqlite3
conn = sqlite3.connect('/tmp/test.db',isolation_level=None, detect_types=sqlite3.PARSE_DECLTYPES);
sqliteobj = conn.cursor()
sqliteobj.execute("INSERT INTO BlockedNumbers (MobileNumber,BlockedTimes) VALUES ('9999999999','1');")
print sqliteobj.lastrowid

According to the sqlite documentation:

If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. You can then access the ROWID using any of four different names, the original three names described above or the name given to the INTEGER PRIMARY KEY column.

So in this case, we would expect to get a rowid of 9999999999, and indeed that is precisely what I get if I run select last_insert_rowid(); in the sqlite3 shell. But in Python I get:

1283723252

or all kinds of random numbers, including negative ones. This happens even with a clean, new database if this particular table is created.

This is Python 2.6.6, which I'm running as I am using it with a script that runs under SL4A. Is this a bug? I can't find any references to this kind of behaviour.

Upvotes: 1

Views: 583

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1123970

Yes, this is a bug in Python; see issue 17073. Any rowid over sys.maxint (e.g. 2147483647 on 64-bit systems) would overflow:

$ python
Python 2.6.6 (r266:84292, Dec 27 2010, 00:02:40) 
[GCC 4.4.5] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sys
>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> sqliteobj = conn.cursor()
>>> sqliteobj.execute('CREATE TABLE BlockedNumbers (MobileNumber integer PRIMARY KEY NOT NULL, BlockedTimes integer);')
<sqlite3.Cursor object at 0xb73fb7a0>
>>> sqliteobj.execute("INSERT INTO BlockedNumbers (MobileNumber,BlockedTimes) VALUES (?, '1')", (sys.maxint,))
<sqlite3.Cursor object at 0xb73fb7a0>
>>> sqliteobj.lastrowid
2147483647
>>> sqliteobj.execute("INSERT INTO BlockedNumbers (MobileNumber,BlockedTimes) VALUES (?, '1')", (sys.maxint + 1,))
<sqlite3.Cursor object at 0xb73fb7a0>
>>> sqliteobj.lastrowid
-2147483648

Python versions 2.6.9, 2.7.4, 3.2.4 and 3.3.1 all include the fix for this bug.

Upvotes: 2

Related Questions