Why doesn't Python sqlite3 insert auto-incremented ID

Question

In Python 3.4.1, why doesn't sqlite3 insert the auto-incremented ID into the table in the below program? According to the SQLite documentation, an integer primary key column should auto-increment, and I can see that from cur.lastrowid returning a valid integer, BUT the same value is not inserted into the table (it becomes NULL instead).

import sqlite3


with sqlite3.connect(':memory:') as conn:
    cur = conn.cursor()
    # Note that column 'id' is an integer primary key
    cur.execute('create table test (id int primary key , name text)')
    cur.execute('insert into test (name) values (?)', ('Test',))
    last_id = cur.lastrowid
    assert last_id is not None

    id_, = cur.execute('select id from test').fetchone()
    assert id_ == last_id, '{} != {}'.format(id_, last_id)

aknuds1 aknuds1 · Accepted Answer · 2014-07-30T12:23:31

Apparently, I was mistaken in thinking that 'int' is a synonym for 'integer' in SQLite. In fact, columns are typeless in SQLite and integer primary key is an exception to this rule, declaring effectively an auto-incremented column:

import sqlite3


with sqlite3.connect(':memory:') as conn:
    cur = conn.cursor()
    # Note that column 'id' is an integer primary key
    cur.execute('create table test (id integer primary key , name text)')
    cur.execute('insert into test (name) values (?)', ('Test',))
    last_id = cur.lastrowid
    assert last_id is not None

    id_, = cur.execute('select id from test').fetchone()
    assert id_ == last_id, '{} != {}'.format(id_, last_id)

Why doesn't Python sqlite3 insert auto-incremented ID

1 Answers