I am trying to insert a None value into a row entry of my db. The table present exists
db.execute("INSERT INTO present VALUES('test', ?, 9)", "This is a test!")
db.execute("INSERT INTO present VALUES('test2', ?, 10)", None)
but I get an error:
ValueError: parameters are of unsupported type
how do I insert a blank value for the second field in the row?
Use a tuple, I.E.:
db.execute("INSERT INTO present VALUES('test2', ?, 10)", (None,))
I'm not sure if it was the case back when the original question was posted, but as of today:
db.execute("INSERT INTO present VALUES('test', ?, 9)", "This is a test!")
throws
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 15 supplied.
when ran.
Because execute() accepts sql — which is sql query to execute, and parameters — which is iterable, containing query parameters (currently this is true for both Python 2 and Python 3. As string is iterable — it treats it accordingly.
Docs clearly state, that Python None type correspond to SQLite NULL type.
So, correct way to do it would be:
db.execute("INSERT INTO present VALUES('test2', :null, 10)", {'null':None})
#or
db.execute("INSERT INTO present VALUES('test2', ?, 10)", (None,))
db.execute("INSERT INTO present VALUES('test', ?, 9)", ("This is a test!", )) would be the correct format since it turns the string into the single element of a tuple, an iterable of the correct size. `["This is a test"] would also work, a list of one element.The problem here is that the values that you're supplying are meant to be in either a list or a tuple. The second argument is expected the be a collection (array of some kind) that can be iterated over. If you supply a collection it will work, even if that collection only has the one value in it. Another problem arises though in that a string behaves like a collection of characters, and can be iterated over. So when you supply a single string without putting it in a collection itself, it says the "collection" is too big, compared to the number of bindings (question marks). The string "This is a test!" is 15 characters long, and can look like a collection (array) with 15 values when the len() function is used on it. So sqlite3 thinks you've passed a collection as it wants, but the size of the collection doesn't match the number of bindings you've got in the query.
