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

Question

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');

what format is the table in? like what data type is it stored in? — Eli Harold
– Eli Harold, Commented Jan 19, 2022 at 21:55
I should have specified, but its in sqlite database as a table — chriswang123456
– chriswang123456, Commented Jan 19, 2022 at 21:59

Tim Roberts · Accepted Answer · 2022-01-20 00:01:03Z

1

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.

edited Jan 20, 2022 at 0:01

answered Jan 19, 2022 at 22:04

Tim Roberts

55.3k4 gold badges29 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

chriswang123456 Over a year ago

I see that is helpful. For my purposes I do need the rowid as a seperate column called names_index. Is there a way to take that hidden column and make it as an actual column?

Tim Roberts Over a year ago

UPDATE names SET names_index=rowid, assuming the names_index field already exists. That won't be updated as new rows are added. Really, if you need an index field, you should be adding the index field yourself.

chriswang123456 Over a year ago

Sorry, I am new to SQL in general. In my case, the names_index field won't already exist. There is one field which is names, and my question is how I can add the index column and ultimately create hew new field in sqlite

Tim Roberts Over a year ago

ALTER TABLE names ADD COLUMN names_index INTEGER

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related