21

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?

moesef
  • 4,641
  • 16
  • 51
  • 68

3 Answers3

38

Use a tuple, I.E.:

db.execute("INSERT INTO present VALUES('test2', ?, 10)", (None,))
Tim Wilder
  • 1,607
  • 1
  • 18
  • 26
7

Last time tested on: August 2018

  • Python 3.6.1
  • Python 2.7.10

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,))
  • the one with the tuple, (None,) is the correct syntax. – redcartel Dec 03 '18 at 03:11
  • @redcartel, they both correct see examples under the `# And this is the named style:` for [Python 2](https://docs.python.org/2.7/library/sqlite3.html#sqlite3.Cursor.execute) and [Python 3](https://docs.python.org/3.7/library/sqlite3.html#sqlite3.Cursor.execute). –  Dec 20 '18 at 16:38
  • Your problem is that "This is a test!" is a string and iterable with 15 elements. `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. – Torrien Aug 06 '21 at 02:12
1

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.

Rusty
  • 11
  • 1