CODEWITHSUNDEEP
pythonsqliteinsertprimary-keyauto-increment
Roman
Roman

Reputation: 131088

How to insert into sqlite table that contains only one column with auto-incremental primary key?

I would like to have in sqlite a "counter" table that always give me a new unique ID. I have managed what I need in the following way. First, I create the following table:

cursor.execute('''create table second (id integer primary key autoincrement, age integer)''')

Then I perform the following sequence of commands:

cursor.execute('''insert into second (age) values (1)''')
cursor.lastrowid

Each time when I execute the above two columns I get a new integer. It is exactly what I need. However, the above solution is not elegant since I use a column ("age") that I do not really need. The reason I used is following. I can create a table that contains only one column with the IDs:

cursor.execute('''create table first (id integer primary key autoincrement)''')

However, the problem is that I cannot manage to insert into this table. The following does not work:

cursor.execute('''insert into first () values ()''')

I get the following error message:

sqlite3.OperationalError: near ")": syntax error

Does anybody knows how to solve the described problem?

Upvotes: 3

Views: 3875

Answers (2)

CL.
CL.

Reputation: 180060

The documentation says:

If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically.

So you can either explicitly specify NULL:

INSERT INTO first(id) VALUES(NULL)

or specify no value at all:

INSERT INTO first DEFAULT VALUES

Upvotes: 3

Gabi Purcaru
Gabi Purcaru

Reputation: 31524

This should work:

sqlite> CREATE TABLE first (id integer primary key autoincrement);
sqlite> INSERT INTO first (id) VALUES (null);
sqlite> SELECT * FROM first;
1
sqlite> INSERT INTO first (id) VALUES (null);
sqlite> SELECT * FROM first;
1
2

Upvotes: 5

Related Questions