sqlite3 doesn't populate the lastrowid member of the Cursor object when a SQL REPLACE statement is executed.

The following snippet doesn't work as I would expect:
cursor = db.execute("REPLACE INTO table(column) VALUES ('datum')")
print cursor.lastrowid # prints None

The following snippet, with SQL which is in effect identical to SQLite, does work as expected:
cursor = db.execute("INSERT OR REPLACE INTO table(column) VALUES ('datum')")
print cursor.lastrowid # prints some rowid

Looking at Modules/_sqlite/cursor.c, in _pysqlite_query_execute(), the following snippet is found:

if (!multiple && statement_type == STATEMENT_INSERT) {
    Py_BEGIN_ALLOW_THREADS
    lastrowid = sqlite3_last_insert_rowid(self->connection->db);
    Py_END_ALLOW_THREADS
    self->lastrowid = PyLong_FromLong((long)lastrowid);
} else {
    Py_INCREF(Py_None);
    self->lastrowid = Py_None;
}

I suggest this should read something like:
if (!multiple && (statement_type == STATEMENT_INSERT || statement_type == STATEMENT_REPLACE)) {
instead of:
if (!multiple && statement_type == STATEMENT_INSERT) {

Thanks,

Jim

Considering that sqlite's 'replace' is a synonym for 'insert or replace', I think the logic error is actually in the detect_statement_type function. Since actions are conditionally taken on the REPLACE statement type in the code, including at least one that adjusts the lastrowid, I don't think the fix for lastrowid is as simple as just always setting it. But I'm not that familiar with sqlite internals, so perhaps someone with more knowledge will weigh in.

Have a unit test that replicates this bug. Working on the C code to fix it right now.

Patch that fixes bpo-16864. Follows Jim Minters suggestion.

Unit test will reproduce the issue without the c modifications. C modifications fix the issue.

Made some review comments. There also needs to be a documentation change since the docs currently say it is set for insert only. There should be a .. versionchanged directive. This may be small enough not to pass the what's new threshold, but I'd rather add a note and let the person who does the final edit on that document decide, so please add that as well.

(You will note that I've changed this to enhancement...it is documented as *only* working for INSERT, so I believe it must be treated as an enhancement to make it work for REPLACE.)

Updated the patch to have a versionchanged for lastrowid in Doc/Library/sqlite3.rst and Doc/whatsnew/3.5.rst

One thing of note is that I wasn't able to get the indentation to be on the same level for sqlite3.rst (it would either intent the description text or the versionchange text).

Now that I'm actually starting to understand the dbapi and sqlite3 I've come to the conclusion that the lastrowid method should update the lastrowid for the INSERT OR ROLLBACK, INSERT OR ABORT, INSERT OR FAIL, INSERT OR IGNORE statements as well as the INSERT and INSERT OR REPLACE statements.

I'm unsure how hard or simple supporting those statements will be

The code in question is

704 Py_DECREF(self->lastrowid);$
705 if (!multiple && statement_type == STATEMENT_INSERT) {$
706 sqlite_int64 lastrowid;$
707 Py_BEGIN_ALLOW_THREADS$
708 lastrowid = sqlite3_last_insert_rowid(self->connection->db);$
709 Py_END_ALLOW_THREADS$
710 self->lastrowid = _pysqlite_long_from_int64(lastrowid);

And the difficulty will be if sqlite3_last_insert_rowid (line 708) does or doesn't return a row id if the OR STATEMENT portion of those inserts are triggered.

The Problem I'm having is that when I tried to find sqlite3_last_insert_rowid in the Modules/_sqlite directory I got nothing

$ grep -r "sqlite3_last_insert_rowid" Modules/_sqlite/
Modules/_sqlite//cursor.c:            lastrowid = sqlite3_last_insert_rowid(self->connection->db);

When I pulled the grep out to the entire cpython repository

$ grep -r "sqlite3_last_insert_rowid" .
Binary file ./build/lib.macosx-10.10-x86_64-3.5-pydebug/_sqlite3.cpython-35dm-darwin.so matches
Binary file ./build/lib.macosx-10.10-x86_64-3.5-pydebug/_sqlite3.so matches
Binary file ./build/temp.macosx-10.10-x86_64-3.5-pydebug/Users/alexlord/mercurial/cpython/Modules/_sqlite/cursor.o matches
./Modules/_sqlite/cursor.c:            lastrowid = sqlite3_last_insert_rowid(self->connection->db);

I still didn't find anything and I'm not sure where to go from here.

Thanks for Alex_gayner and lifeless. They pointed out the sqlite3_last_row_id is part of the sqlite3 module itself (not cpython).

https://www.sqlite.org/c3ref/last_insert_rowid.html

According the documentation we can expect that if a constraint stops an insertion then the lostrowid is not modified. As such the changes required to add full INSERT OR CONDITION support for last row id is unit tests for each statement and changing the conditional to recognize all insert cases.

Went back to test to see if the other statements are covered already. Unit tests show that lastrowid is set properly no matter what form of sqlite insert statement is used.

sqlite_lastrowid_35_v2.patch contains the doc changes, unit test changes, and code change.

I believe that the latest version of this patch is ready for a code review by a core dev.

Added review comments.

There was a bunch of things wrong with that patch.

In addition to the issues you brought up in the review I was mixing up what the actual problem is

REPLACE INTO is an alias for INSERT OR REPLACE. INSERT OR REPLACE was correctly setting the lastrowid values but REPLACE INTO was not setting the last rowid value. I changed the documentation modifications to reflect this.

In addition I cleaned up the unit tests. The unit tests were kind of a mess because I was trying to figure out what the problem was and never refactored after getting a reproduction.

I at first went down the path of making the tests use a for loop like you suggested

    for statement in ["INSERT OR REPLACE", "REPLACE"]:
        sql = "{} INTO test(id, unique_test) VALUES (?, ?)".format(
            statement)
        self.cu.execute(sql, (1, "foo"))
        self.assertEqual(self.cu.lastrowid, 1)
        self.cu.execute(sql, (1, "foo"))$
        self.assertEqual(self.cu.lastrowid, 1) 

Which I don't think is as nice as a cleaned up unrolled version

self.cu.execute("INSERT OR REPLACE INTO test(id, unique_test) VALUES (?, ?)", (1, "bar",))
self.assertEqual(self.cu.lastrowid, 1)
self.cu.execute("INSERT OR REPLACE INTO test(id, unique_test) VALUES (?, ?)", (1, "bar",))
self.assertEqual(self.cu.lastrowid, 1)
self.cu.execute("REPLACE INTO test(id, unique_test) VALUES (?, ?)", (1, "bar",))
self.assertEqual(self.cu.lastrowid, 1)

I've created a patch that fixes all of the issues brought up in the code review and is just generally much cleaner.

All lines need to be wrapped to <80 columns.

The idea behind the loop is this:

sql = "{} INTO test(id, unique_test) VALUES (?, ?)"
self.cu.execute(sql.format('INSERT OR REPLACE, (1, "foo")
for statement in ["INSERT OR REPLACE", "REPLACE"]:
    with self.subTest(statement=statement):
        self.cu.execute(sql.format(statement), (1, "foo"))
        self.assertEqual(self.cu.lastrowid, 1) 

What this does is run *both* tests, even if one fails, and reports the results separately (labeled with 'statement='INSERT OR REPLACE', ect).

Oh, alright. That makes a lot of sense. Sorry for being dense. I should have read the docs on subtest.

All lines are under 80 characters and I modified the unit test to use subtest.

Thanks for taking the time to walk me through this.

Adding a patch for 3.6 since 3.5 is in beta.

New changeset 2126e8cbc12f by Berker Peksag in branch 'default':
Issue bpo-16864: Cursor.lastrowid now supports REPLACE statement
https://hg.python.org/cpython/rev/2126e8cbc12f

Thanks for the patch, Alex.