Add an autoincrementing ID column to an existing table with Sqlite

Question

Using Sqlite, I want to add an auto-incrementing ID column to an existing table which had previously no ID:

import sqlite3
db = sqlite3.connect(':memory:')
c = db.cursor()

c.execute('create table events (int weight, text str)')
c.execute('insert into events values(?, ?)', (1371, 'Test1'))
c.execute('insert into events values(?, ?)', (223, 'Test2'))
c.execute('select * from events'); print c.fetchall()
# [(1371, u'Test1'), (223, u'Test2')]

# add an autoincrementing ID to existing table
c.execute('alter table events add id int not null auto_increment primary key')

How to do it properly? I have this error:

sqlite3.OperationalError: near "auto_increment": syntax error

Answer 1

An SQLite table cannot modified in a significant manner using alter table once it has been created. One common popular suggestion is to create a new table with the existing fields as well as the additionally required field and copy/import your data into the new one and optionally delete the old one.

c.execute('create table events_copy(id integer primary key autoincrement, weight integer,str text)')
c.execute('insert into events_copy(weight, str) select weight, str from events')
c.execute('drop table events')
c.execute('alter table events_copy rename to events')
c.commit()