CODEWITHSUNDEEP
pythonsqlsqlite
cclloyd
cclloyd

Reputation: 9225

SQLite with Python "Table has X columns but Y were supplied"

I have a python script that executes some simple SQL.

c.execute("CREATE TABLE IF NOT EXISTS simpletable (id integer PRIMARY KEY, post_body text, post_id text, comment_id text, url text);")
command = "INSERT OR IGNORE INTO simpletable VALUES ('%s', '%s', '%s', '%s')" % (comments[-1].post_body, comments[-1].post_id, comments[-1].comment_id, 
comments[-1].url)
c.execute(command)
c.commit()

But when I execute it, I get an error

sqlite3.OperationalError: table simpletable has 5 columns but 4 values were supplied

Why is it not automatically filling in the id key?

Upvotes: 7

Views: 19094

Answers (4)

aref yazdi
aref yazdi

Reputation: 11

If you wrote code correctly delete your SQL file(name.db) and run your code again some time it solve the problem.

Imagine this is your code:

cursor.execute('''CREATE TABLE IF NOT EXISTS food(name TEXT , price TEXT)''')

cursor.execute('INSERT INTO food VALUES ("burger" , "20")')

connection.commit()

and you see an error like this:

table has 1 column but 2 values were supplied

it happened because for example you create a file with one column and then you modify your file to two column but you don't change the file name so compiler do not over write it because it exist.

Upvotes: 1

sticky bit
sticky bit

Reputation: 37482

If you don't specify the target columns VALUES is expected to provide values for all columns and that you didn't do.

INSERT
 OR IGNORE INTO simpletable
                (text,
                 post_id,
                 comment_id,
                 text)
                VALUES ('%s',
                        '%s',
                        '%s',
                        '%s');

Specifying the target columns is advisable in any case. The query won't break, if, for any reason, the order of the columns in the tables changes.

Upvotes: 6

Ronny K
Ronny K

Reputation: 3751

In Python 3.6 I did as shown below and data was inserted successfully.

I used None for autoincrementing ID since Null was not found.

conn.execute("INSERT INTO CAMPAIGNS VALUES (?, ?, ?, ?)", (None, campaign_name, campaign_username, campaign_password))

The ID structure is as follows.

ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL

Upvotes: 8

rsb55
rsb55

Reputation: 66

try to specify the columns names to ensure that the destination of values doesn't depends on order. ex:

INTO simpletable (text, post_id, comment_id, text)

And if you wants the id column to be automatically incremented make sure to add Identity property on, or similar auto increment of your dbms. ex:

CREATE TABLE IF NOT EXISTS simpletable (id integer PRIMARY KEY Identity(1,1),

and remember your script is not prepared to alter the table structure, only creation.

Upvotes: 2

Related Questions