SQLite3 serial type wasn't incremented

Question

I was creating a database by using SQLite3. and my python version is 2.7.5.

Before creating a database I simply created example database and tested it whether it will function well or not. and It failed that the id wasn't incremented even I declared it as a serial type.

I created simple database:

import sqlite3
con = sqlite3.connect('sample.db')
cur = con.cursor()
cur.execute("""CREATE TABLE sample(id serial,test real)""")
cur.execute("""INSERT INTO sample(test) VALUES(?)""",(3,))
cur.execute("""INSERT INTO sample(test) VALUES(?)""",(6,))
cur.execute("""INSERT INTO sample(test) VALUES(?)""",(8,))
con.commit()

Then I fetched all data:

data = cur.execute("""SELECT * from sample""")
t = data.fetchall()

In [33]: t
Out[33]: [(None, 3.0), (None, 6.0), (None, 8.0)]

I expected this:Out[33]: [(1, 3.0), (2, 6.0), (3, 8.0)] However, As you can see, all of the id element was None

How can I solve this problem ? I know I can do by just incrementing a variable and Insert it. like this:

id += 1
cur.execute("""CREATE TABLE sample(id serial,test real)""")
id += 1
cur.execute("""INSERT INTO sample(test) VALUES(?)""",(3,))
id += 1
cur.execute("""INSERT INTO sample(test) VALUES(?)""",(6,))

However, isn't this awful ? I don't want to do it. I'd like to make my code clear and smart.

Answer 1

There is no serial data type in SQLite. The correct way to create the table is with AUTOINCREMENT in SQLite:

CREATE TABLE sample ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
                      test REAL);

Answer 2

You'd need to mark a column as INTEGER PRIMARY KEY or INTEGER PRIMARY KEY AUTOINCREMENT to get auto-incrementation behaviour.

The difference between those two types is subtle; the latter form will never reuse IDs. See the ROWID and the INTEGER PRIMARY KEY documentation.