【Python】SQLite3获取新插入的记录ID及ROWID探究

sqlite是一个非常好用的轻量级数据库，并且python自带操作sqlite的函数库，开箱即用，对新手特别友好。

1. 傻瓜式调用

在Python官方API中，sqlite3.Cursor对象有 lastrowid 属性，使用 cursor.lastrowid 即可得到新插入的数据记录的ID值。

lastrowid

This read-only attribute provides the row id of the last inserted row. It is only updated after successful INSERT or REPLACE statements using the execute() method. For other statements, after executemany() or executescript(), or if the insertion failed, the value of lastrowid is left unchanged. The initial value of lastrowid is None.

Note

Inserts into WITHOUT ROWID tables are not recorded.

Changed in version 3.6: Added support for the REPLACE statement.

翻译：

lastrowid

该属性只读，提供最后插入的行记录的row id。它只有在使用 execute() 方法成功 insert 和 replace 时才会被更新。

使用 executemany() 和 executescript() 方法时 或者插入操作失败时， lastrowid 的值不会变化。

lastrowid 初始值是 None。

注意：

在没有 ROWID 的数据库表执行插入，lastrowid不会记录值。

版本3.6变化：增加对 replace 语句的支持。

代码如下：

# Python3
# -*- coding: utf-8 -*-
import sqlite3

con = sqlite3.connect(":memory:")	# 表示在内存中创建的数据库文件，运行完数据即丢失
cur = con.cursor()

# id 自增
cur.execute("create table t_haha(id INTEGER PRIMARY KEY AUTOINCREMENT,name text, age INTEGER)")

# 第1条数据
data = ("Tom",18)
cur.execute("insert into t_haha(name,age) values (?,?)", data)

theid = cur.lastrowid

2. 深入研究

仔细看说明，可以发现官方手册中说的是 返回新插入记录的 row id，而不是我们自定义的主键id。

而这个 row id是何方神圣呢？

2.1 row id是什么

 默认情况下，所有的SQLite表每一行都有一个特殊的列，通常被称为 rowid，它是每个 表内代表每个行的唯一标识。

如果某个建表语句 create table后附带了 without rowid ，则该表的 rowid 特殊列就会被删除，这个新建的表就是个 WITHOUT ROWID table。

[译自官方此文]

也就是说一般的SQLite的每个表都带有 rowid 列。

2.2 rowid table

注意：下文中的 rowid 和 rowid table 都是专有名词，具有特定的含义，指代特定的一类东西。

我们看看 SQLite 官方网站对 rowid table 的介绍，节选翻译自官方网站的此文。

rowid table定义：

• 不是 virtual table（和视图有点像的东西）
• 不是 WITHOUT ROWID table

大部分的典型的SQLite数据库表都是 rowid table。

Rowid table 一般有以下特征：它们都有唯一的(unique), 非空的(not-NULL), 带有符号的 64位整型(signed 64-bit integer) rowid，用来在 B-tree存储引擎中作为key索引数据。

变形(Quirks)

• rowid table 的 PRIMARY KEY 通常并不是用来在B-Tree中索引数据那种意义上的主键。（博主注：说人话，就是说 在SQLite内部B-Tree中，并不是用 PRIMARY KEY 来索引该数据记录的）。这条规则的例外就是，当 rowid table 声明了一个 INTEGER PRIMARY KEY。在这种情况下，这个 INTEGER PRIMARY KEY 就变成了 rowid 的别称。
• rowid table 真正的主键（primary key）是 rowid。（主键（primary key）的值通常用来在内部B-Tree存储中查找指定行的key）。
• rowid table 的 rowid 可以使用 "rowid"或"oid"或“_rowid_”中的任一个名称作为列名来访问、读写。有一种例外，如果某个表在创建时占用了那些特殊的列名，那么这些特殊的列名就代表声明时的含义，而不是指代 rowid 了。
• 通过 rowid 获取数据库记录是经过高度优化，速度很快。
•  rowid 没有被  INTEGER PRIMARY KEY 代替，那么它不是持久的，可能会被更改。特别是 VACUUM命令会改变那些没有声明 INTEGER PRIMARY KEY 的表的rowid。所以，应用软件不建议直接访问 rowid，而是应该用一个 INTEGER PRIMARY KEY 列来代替rowid。

总结一下，就是说：

1. 创建表时，主键要创建为 INTEGER PRIMARY KEY，其实我觉得最好再加上自增 AUTOINCREMENT ，这样最简单最完美。

2. 带有 INTEGER PRIMARY KEY 列的SQLite 数据库表，其 rowid 就是 该INTEGER PRIMARY KEY 列。所以，前面 使用 cur.lastrowid 得到就是我们的ID值了。

3. 创建表SQL语句应该这样：

create table t_haha(id INTEGER PRIMARY KEY AUTOINCREMENT,name text, age INTEGER)

这样这个表 t_haha 的id 列就代表原始的 rowid了。

注意：INTEGER PRIMARY KEY  不要写成了 int primary key。大小写无所谓，但是 integer 不要写成了 int，因为 int 不是 sqlite 的基本数据类型。

3. 代码

# Python3
# -*- coding: utf-8 -*-
import sqlite3

con = sqlite3.connect(":memory:")	# 表示在内存中创建的数据库文件，运行完数据即丢失
cur = con.cursor()
# id 自增
cur.execute("create table t_haha(id INTEGER PRIMARY KEY AUTOINCREMENT,name text, age INTEGER)")

# 第1条数据
data = ("Tom",18)
cur.execute("insert into t_haha(name,age) values (?,?)", data)

# 第2条数据
data = ("Jerry",22)
cur.execute("insert into t_haha(name,age) values (?,?)", data)

# 第3条数据
data = ("Lily",25)
cur.execute("insert into t_haha(name,age) values (?,?)", data)

# 打印全部数据
cur.execute("select * from t_haha")
print(cur.fetchall())
print("===insert 3 data, and last row id is", cur.lastrowid)

# 删除第1条 
cur.execute("delete from t_haha where id = 1")

# 打印全部数据
cur.execute("select * from t_haha")
print(cur.fetchall())
print("===delete the first row")

# 再插入1条数据，此时id自动递增，插入后id应该为4
data = ("Peter",100)
cur.execute("insert into t_haha(name,age) values (?,?)", data)

# 验证下 lastrowid 是否严格和自增的id一致
cur.execute("select * from t_haha")
print(cur.fetchall())
print("===last row id:",cur.lastrowid)

con.close()
con.close()

程序输出如下：

表明 cur.lastrowid 值其实就是我们的 id 列值。