2

I've researched similar links, but still can't figure this out. Most of the other links are trying to insert into multiple rows or columns, but I need this list of tuples in one column.

word_in_python = cat
listOfTuples = [('word1', 12), ('word2', 14), ('word3', 4), ('word5', 23)]

Here is my code:

conn = sqlite3.connect('mydb.db')
c = conn.cursor()
c.execute("INSERT OR IGNORE INTO saved_words (word, data) VALUES (?, ?)", (word_in_python, [listOfTuples]))
conn.commit()
conn.close()

I need the INSERT OR IGNORE because the word column is a unique column so duplicate words should not be entered into the db.

I've tried changing to:

c.execute("INSERT OR IGNORE INTO saved_words (word, data) VALUES (?, ?)", (word_in_python, (listOfTuples, )))

c.executemany("INSERT OR IGNORE INTO saved_words (word, data) VALUES (?, ?)", (word_in_python, [listOfTuples]))

for each in listOfTuples:
  c.executemany("INSERT OR IGNORE INTO saved_words (word, data) VALUES (?, ?)", (word_in_python, (each[0], each[1]) ))

None of these work.

The error I am typically getting is:

Incorrect number of bindings supplied. The current statement uses 2, and there are 5 supplied.

I'm also confused by this error. I understand that (?, ?) in my execute line means I am trying to input 2 bindings. My list of tuples has 10 tuples in it, so I am not sure where the 5 supplied bindings are coming from.

Also, my saved_words table had the data column as type BLOB. I thought this would be best for inserting a list.

EDIT

What I want is word_in_python (cat) in one column and in the next column listOfTuples, this is all in one row.

zfa
  • 105
  • 1
  • 2
  • 7
  • What is `word_in_python`? – alecxe Jun 06 '16 at 17:01
  • @alecxe it is a separate string I am inputting into another column. While debugging, I tested out inserting just the word_in_python into the db and it worked fine. I believe my error is in inserting listOfTuples. – zfa Jun 06 '16 at 17:05
  • A list is not a blob. How exactly should the list look like when it is stored in the DB? – CL. Jun 07 '16 at 07:31
  • @CL. the column for the list should include the entire list in one record and one column. – zfa Jun 07 '16 at 12:44
  • SQLite does not have a list [data type](http://www.sqlite.org/datatype3.html#storageclasses). – CL. Jun 07 '16 at 12:54
  • @CL. yeah, that is why I used BLOB. I was thinking it might be able to hold a list. – zfa Jun 07 '16 at 13:57

1 Answers1

0

Not sure what word_in_python is, but the correct way to pass a list of tuples to executemany is this:

c.executemany("INSERT OR IGNORE INTO saved_words (word, data) VALUES (?, ?)", listOfTuples))

In this case, the database driver would execute a statement for every tuple in the list using tuple items as query parameters - word1 for word, 12 for data, then word2 for word, 14 for data etc.

alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
  • I've edited to post to explain word_in_python. It is actually a separate string and not the first word in the tuple. – zfa Jun 06 '16 at 17:25