Search code examples
pythonmysqlinsertrecordauto-increment

Mysql with Python can't insert record with autoincrement id, why?

I create a table 'test' with two column:(age int, name TEXT) in mysql database.

Then I insert a record using the following codes(with a list): 

record = [12, 'Tom']
cursor.execute("insert into test values(%s,%s)", record)

The above codes work in mysql(I use python 2.7 for programming).

I then delete the old table and want to add an AUTO_INCREMENT P_id field for the new table, by adding the following code to the CREATE TABLE sql:

P_id int(11) PRIMARY KEY AUTO_INCREMENT,

And the new table works and I find it in mysql. However, when I try to insert a new record using the same codes:

record = [12, 'Tom']
cursor.execute("insert into test values(%s,%s)", record)

But it doesn't work and reports:

OperationalError: (1136, "Column count doesn't match value count at row 1")

It looks like I should add the value of P_id by myself? But should it increase automatically and I can omit that? I'm quite a newbie in mysql and please help with details.

This is my first time question in StackOverflow and thanks for any help.

Solution

  • Use this query:

    insert into test (age,name) values(%s,%s)

    Your code will look like:

    record = [12, 'Tom']
cursor.execute("insert into test (age,name) values(%s,%s)", record)