Inserting SQLite primary key in python

Question

I am trying to do a SQLite insert from my python script.

I am getting an error:

table history has 4 columns but 3 values were supplied

The reason there is 3 not 4, is that the first one is my ID. I assumed that as i declared it as a primary key it would auto-increment the value. How do I go about inserting the ID

.py:

c.execute("INSERT INTO history VALUES (?,?,?)", (q[0],t,in))

.db

CREATE TABLE history (id integer primary key, employeeID integer, time datetime, inout varchar);

(q[0],t,in) Wouldn't this give a parse error, as in is a keyword? — Colonel Thirty Two
– Colonel Thirty Two, Commented Mar 16, 2015 at 12:09

Martijn Pieters · Accepted Answer · 2015-03-16 12:03:16Z

7

You either have to provide values for all columns, or name the columns you are inserting into explicitly. SQLite will not go and guess what columns you provided values for, you need to be explicit about them.

You can use a NULL to have SQLite insert an auto-generated id for you:

c.execute("INSERT INTO history VALUES (NULL,?,?,?)", (q[0],t,in))

or you can name the 3 columns you are inserting into explicitly:

c.execute("INSERT INTO history (employeeID, time, inout) VALUES (?,?,?)", (q[0],t,in))

answered Mar 16, 2015 at 12:03

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

2 Comments

Bhargav Rao Over a year ago

Is NULL case-sensitive here?

Martijn Pieters Over a year ago

@AnttiHaapala: there are many bad ideas when it comes to interop. The better idea is to use SQLAlchemy if interop is important.

Collectives™ on Stack Overflow

Inserting SQLite primary key in python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related