executemany problem, MySQLdb

Question

I'm using MySQLdb and run into the following problem:

STMT="""INSERT INTO test_table VALUES (%s, %s, %s, %s, %s)"""
rows=[('Wed Apr 14 14:00:00 2010', 23L, -2.3, 4.41, 0.83923)]
conn.cursor().executemay(STMT, rows)

results in:

Traceback (most recent call last):
 File "run.py", line 122, in <module>
 File "C:\Python25\lib\site-packages\mysql_python-1.2.2.0002-py2.5-win32.egg\MySQLdb\cursors.py", line 276, in _do_query
 db.query(q)
_mysql_exceptions.OperationalError: (1136, "Column count doesn't match value count at row 1")

Any hints ?

Answer 1

Try to write all columns in your INSERT explicitly:

STMT = 'INSERT INTO test_table (col1, col2, col3, col4, col5) VALUES (%s, %s, %s, %s, %s)'

• Mark Byers: +1 almost exactly what I wrote, even the fake column names are the same!
• rocksportrocker: That did it. I had an autoincrement id which I did not set in my statement. Stupid. But thanks for the fast answer.

Answer 2

How many columns are there altogether in test_table? Probably not 5, judging from the error. Try running SHOW CREATE TABLE test_table to see how the table is defined.

It is a good idea to explicitly list the column names when inserting in case new columns are added. Try this instead:

INSERT INTO test_table (col1, col2, col3, col4, col5) VALUES (%s, %s, %s, %s, %s)

You should change col1, col2, etc. to your real column names.