SQLite: rowid

The rowid value can be queried with the rowid keyword. Synonyms for rowid are: oid and _rowid.

If a table has a column that is defined to be an integer primary key, this column stores the rowid, that is, the respective column names is an alias for rowid (or vice versa).

Demonstration

The following script tries to demonstrate rowids:

.mode column
.width 2 2 2 2 10

create table tq84_foo (
  id    abcdef primary key,
  col_1 text
);

insert into tq84_foo values (  2, 'two'      );
insert into tq84_foo values (  7, 'seven'    );
insert into tq84_foo values (  5, 'five'     );
insert into tq84_foo values (  9, 'three'    );

update tq84_foo set id = 3 where col_1 = 'three';

select
  rowid,
  oid,       -- alternative name for rowid
  _rowid_,   -- alternative name for rowid
  id,
  col_1
from
  tq84_foo;
--
-- 1   1   1   2   two
-- 2   2   2   7   seven
-- 3   3   3   5   five
-- 4   4   4   3   three

drop table tq84_foo;

--
-- If the primary key of the table is one column of type »integer«, it
-- becomes an alias for the rowid:

create table tq84_foo (
  id    integer primary key,
  col_1 text
);


insert into tq84_foo values (  2, 'two'      );
insert into tq84_foo values (  7, 'seven'    );
insert into tq84_foo values (  5, 'five'     );
insert into tq84_foo values (  9, 'three'    );

update tq84_foo set id = 3 where col_1 = 'three';

select
  rowid,
  oid,
 _rowid_,
  id,
  col_1
from
  tq84_foo;
-- 
-- 2   2   2   2   two
-- 3   3   3   3   three
-- 5   5   5   5   five
-- 7   7   7   7   seven

drop table tq84_foo;

Github repository about-sqlite, path: /tables/rowid/intro.sql

Selecting the last inserted rowid

The rowid of the last inserted row can be selected with the last_insert_rowid() function:

create table ri (col_1, col_2);

insert into ri values (42, 'forty-two');
select last_insert_rowid();
-- 1

insert into ri (rowid, col_1, col_2) values (17, 99, 'ninety-nine');
select last_insert_rowid();
-- 17

Github repository about-sqlite, path: /tables/rowid/last_insert_rowid.sql

See also autoincrementing the primary key.

In Python, the last rowid can be selected with a cursor's attribute lastrowid.