How insert some data into MySQL table?-CodePudding

I am trying to write data to a table (tbl_1) as follows:

curr.execute("""INSERT INTO tbl_1 VALUES (%s,%s,%s,%s,%s)""",
(
 item['some_val_1'],
 item['some_val_2'],
 item['some_val_3'],
 item['some_val_4'],
 item['some_val_5'],      
)
)
conn.commit()

but there are still other columns in my table (some_val_6, some_val_7, some_val_8...), therefore, I get an error:

mysql.connector.errors.DataError: 1136 (21S01): Column count doesn't match value count at row 1

how do I formulate the code so that the necessary data (some_val_1 ... some_val_5) is inserted into the table, and those that are and that I do not explicitly specify in the query (some_val_6, some_val_7, some_val_8...) remain empty?

CodePudding user response：

It is, as has been noted above, in the documentation - but to sift through that, in case you're not great with walls of text... If you don't specify the fields for the inserts, it will default to thinking you're referring to them in their natural order.

So, instead of INSERT INTO tbl_1 VALUES (%s,%s,%s,%s,%s)

if there are more than 5 fields in that table, you'll need to specify them,

e.g.

INSERT INTO tbl_1 (col1, col2, someOtherCol) VALUES (%s,%s,%s)

basically specifying the target fields in the first set of parenthesis, then the values - in the same order - in the next.

INSERT INTO ({target fields}) VALUES ({values})