Menu
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.
283
Syntax for MySQLMySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. VALUES ('Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
To get and print the ID of the last inserted row, lastrowid is used. This is a special keyword which is used to get the ID of the last inserted row.
Reset the auto increment fieldALTER TABLE `table` AUTO_INCREMENT = number; Replacing 'number' with the result of the previous command plus one and replacing table with the table name. If you deleted all the rows in the table, then you could run the alter table command and reset it to 0.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
