sqlite3.OperationalError: table book has 6 columns but 5 values were supplied

Question

Getting an error:

Traceback (most recent call last):
  File "frontend.py", line 2, in <module>
    import backend
  File "/Users/egonsaks/Desktop/Demo/backend.py", line 28, in <module>
    insert("The sea", "John Tablet", 1918, 987654331)
  File "/Users/egonsaks/Desktop/Demo/backend.py", line 15, in insert
    cur.execute("INSERT INTO book VALUES (NULL,?,?,?,?)",(title, author, year, isbn))
  sqlite3.OperationalError: table book has 6 columns but 5 values were supplied

I want id column with primary key integer while inserting data in the table in the database. I checked my code against the tutorial and looked around in stack overflow for a solution from similar problems, but didn't find a solution. Tested some things like removing a null, but then it says 6 columns and 4 values were supplied.

Code here:

import sqlite3

def connect():
    conn = sqlite3.connect("books.db")
    cur = conn.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY, 
    title text, author text, year integer, isbn integer)") 
    conn.commit() 
    conn.close()

def insert(title, author, year, isbn):
    conn = sqlite3.connect("books.db") 
    cur = conn.cursor()
    cur.execute("INSERT INTO book VALUES (NULL,?,?,?,?)",(title, author, year, isbn))
    conn.commit()
    conn.close()

I cannot reproduce the error you are having on my system with the code you have. — Jerry
Are you sure the book table in your database matches that schema? There's not some older version in it left over from an earlier attempt that the IF NOT EXISTS is covering up? — Shawn
Thanks, @Shawn. It was so, deleting old db file and running program again helped and solved the problem. — Egon

gold_cy gold_cy · Accepted Answer · 2019-01-15T12:13:17

Please specify your columns when inserting, this is important, and yes do not include a NULL or any value for the id column!

cur.execute("INSERT INTO book(title, author, year, isbn) VALUES (?,?,?,?)",
                             (title, author, year, isbn))

sqlite3.OperationalError: table book has 6 columns but 5 values were supplied

1 Answers