Search code examples
pythonmysqlpymysqlexecutemany

MySQL and Python: Auto_Increment Not Working with cursor.executemany()

I created a table as below:

    cur.execute("""CREATE TABLE IF NOT EXISTS worddata(
               wordid INT(11) AUTO_INCREMENT PRIMARY KEY,
               Keyword VARCHAR(100) NOT NULL,
               FileName VARCHAR(100));""")

and then executed it using:

     cur.executemany("INSERT INTO worddata VALUES(%s, %s, %s)", wordData)

where wordData is a List of tuples with multiple entries.

This is NOT working. I tried following:

  1. If I remove "wordid INT(11) AUTO_INCREMENT PRIMARY KEY" from the CREATE TABLE statement and remove one of the placeholders (%s), its working fine.

  2. If I just remove one of the placeholders (%s) as suggested in some posts for cursor.execute, it is NOT working (Note I have to use "cursor.executemany")

  3. I even tried using wordData(Keyword, FileName) during INSERT phase, DIDN'T work.

  4. Tried many other options like putting DEFAULT, NULL, O etc. during INSERT and also during CREATE, none worked.

What is the way out wherein I can use cursor.executemany along with Auto_Increment and a list of tuples while inserting the entries in MySQL.

Solution

  • The solution is simple:

    I was using Tuple of Tuples like: (('bye', '36930001437'), ('good', '36930001437'), ('night', '36930001437'))

    The trick is to use List of Tuples: [('bye', '36930001437'), ('good', '36930001437'), ('night', '36930001437')]

    With this simple change, I am now getting Auto_Increment column without any issues with respect to insert values command.