CODEWITHSUNDEEP
pythonsqlitesql-insert
moesef
moesef

Reputation: 4851

Insert Null into SQLite3 in Python

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?

Upvotes: 22

Views: 43641

Answers (3)

Rusty
Rusty

Reputation: 11

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.

Upvotes: 1

user8554766
user8554766

Reputation:

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,))

Upvotes: 7

Tim Wilder
Tim Wilder

Reputation: 1647

Use a tuple, I.E.:

db.execute("INSERT INTO present VALUES('test2', ?, 10)", (None,))

Upvotes: 41

Related Questions