技术: Sqlite 主键自增(Python接口)

Python 接口操作 Sqlite 数据库，主键自增设置。

sqlite3 里面设置主键自增比较特殊。

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print "Table created successfully";
conn.close()

这样操作失败了，如果要让主键 auto_increment, 应该设置 INTEGER PRIMARY KEY，并且不能设置 NOT NULL。

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute('''CREATE TABLE NEW
       (ID            INTEGER 	  PRIMARY KEY,
       NAME           TEXT    NOT NULL);''')
print "Table created successfully";
conn.close()

插入数据的时候，自增的主键那一列，要插入 NULL:

conn.execute("INSERT INTO NEW2 (ID,NAME) VALUES (NULL, 'namehere')");

例如:

import sqlite3
conn = sqlite3.connect('test.db')
for i in range(1,9):
  t = 'list' + str(i)
  conn.execute("INSERT INTO NEW2 (ID,NAME) VALUES (NULL, ?)",(t,))
conn.commit()
conn.close()

运行结果:

ID =  1
NAME =  list1
ID =  2
NAME =  list2
ID =  3
NAME =  list3
ID =  4
NAME =  list4
ID =  5
NAME =  list5
ID =  6
NAME =  list6
ID =  7
NAME =  list7
ID =  8
NAME =  list8

总结:

• 主键设置为 INTEGER PRIMARY KEY
• 不要设置 NOT NULL
• 插入数据应该让主键那一列为 NULL，或者不插入。