How would I create a new column containing an index of column in sqlite database Python?-CodePudding

Say I have a table with one column called name and each row with one observation.

 NAME
('John Smith')
('Paul Smith')
('Jake Smith')

How would I modify the table so it contains an index of each row to get something like below. I need to use standard Python packages only.

 NAME               INDEX
('John Smith')        1
('Paul Smith')        2
('Jake Smith')        3



con = sqlite3.connect('example.db')
cur = con.cursor()
table= "CREATE TABLE names(name TEXT)"
cur.execute(table)

INSERT INTO names(name)
VALUES ('John Smith');
VALUES ('Paul Smith');
VALUES ('Jake Smith');

CodePudding user response：

SQLite creates a hidden column called "ROWID" that does this automatically.

sqlite> create table names(id TEXT);
sqlite> insert into names values ('John Smith'),('Paul Smith'),('Jake Smith');
sqlite> select rowid, id from names;
1|John Smith
2|Paul Smith
3|Jake Smith
sqlite> ALTER TABLE names ADD COLUMN names_index INTEGER;
sqlite> UPDATE names SET names_index=rowid;
sqlite> .mode box
sqlite> SELECT * FROM names;
┌────────────┬─────────────┐
│     id     │ names_index │
├────────────┼─────────────┤
│ John Smith │ 1           │
│ Paul Smith │ 2           │
│ Jake Smith │ 3           │
└────────────┴─────────────┘
sqlite>

If you create your own column that is INTEGER PRIMARY KEY, it will be used as the ROWID.