-1

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.

Samiksha Mishra
  • 21
  • 5
  • Not my field but does it make sense that you INSERT a field that is set as auto-increment? – roganjosh Aug 26 '17 at 15:33
  • @roganjosh As mentioned in Point 3 above, I tried that as well. Didn't work. – Samiksha Mishra Aug 26 '17 at 15:35
  • 1
    Actually, you stated the opposite. In point 1, when you removed the part that doesn't make sense to me, it worked. – roganjosh Aug 26 '17 at 15:36
  • How does `wordData` look like? – Michael Aug 26 '17 at 15:40
  • @roganjosh In Point 1, I mentioned that I removed wordid Auto_Increment key altogether, then it is working fine. – Samiksha Mishra Aug 26 '17 at 15:56
  • @MichaelO. wordData is a list of tuples, something like: (('bye', '36930001437'), ('good', '36930001437'), ('night', '36930001437')) – Samiksha Mishra Aug 26 '17 at 15:59
  • From [this link](https://www.w3schools.com/sql/sql_autoincrement.asp): "To insert a new record into the "Persons" table, we will NOT have to specify a value for the "ID" column (a unique value will be added automatically)". I don't know what we're debating. – roganjosh Aug 26 '17 at 15:59
  • @roganjosh I tried that. Please refer point 3, wherein I tried the same what you are implying. It is working with cursor.execute() but not with cursor.executemany(). – Samiksha Mishra Aug 26 '17 at 16:04
  • Ok, I suggest you reword your question a bit then. So, you're saying that `execute` works fine and `executemany` with a list of tuples does not? You keep referring me to point 3 but I genuinely can't see how it's relevant or what it means specifically. Note; I'm talking about `execute` in the context of INSERT. – roganjosh Aug 26 '17 at 16:07
  • @roganjosh execute doesn't work with List of tuples containing multiple entries. It works only when there is exactly one entry. That is the issue why I can't use just execute and require executemany as my list of tuples has multiple entries, each constituting a row in my database and I want an additional column named wordid which will auto increment it's values. – Samiksha Mishra Aug 26 '17 at 16:13
  • @roganjosh When I don't need execute, why should I mention that in my question. I know it's not working in my case. I need it with executemany. Is that you who downvoted my question without understanding it properly? – Samiksha Mishra Aug 27 '17 at 04:54
  • And if that doesn't answer the question, you need to clarify what "DIDN'T WORK" means. What is the error? – roganjosh Aug 27 '17 at 10:07
  • @roganjosh Leave it please. As mentioned tons of times above, I tried that as mentioned in Point 3. Please go through that point again, it does not use Auto_Increment field, still it doesn't work. In SQL that means, the table doesn't get populated with values. The solution is not that straightforward and "Simple" as you seem to suggest. Don't troll just because you have a high reputation here. – Samiksha Mishra Aug 28 '17 at 08:07
  • I will leave it. Please don't think that I'm trolling you though as that's not the intent. The fact is that the question remains unanswered so whether or not you agree with me, something is not clear enough for people to answer. I am suggesting that you edit to clarify some points. Do you think it's an unreasonable request for you to include some kind of error message? – roganjosh Aug 28 '17 at 09:36

1 Answers1

0

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.

Samiksha Mishra
  • 21
  • 5