JSON insert error, Sqlite3, Python

Question

I am trying to insert raw JSON strings into a sqlite database using the sqlite3 module in python.

When I do the following:

rows = [["a", "<json value>"]....["n", "<json_value>"]]
cursor.executemany("""INSERT OR IGNORE INTO FEATURES(UID, JSON) VALUES(?, ?)""", rows)

I get the following error:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 48 supplied.

How can I insert the raw json into a table? I assume it's the commas in the json string.

How can I get around this?

• code base 5000: I have a list of multiple rows to insert, so isn't a list or lists the proper way to use that function?>\
• l4mpi: Your list doesn't look like you think it looks like; or at least not like the example you gave here. The sqlite module tells you it has 48 values instead of two so you better believe it... I suggest you print your list prior to insertion to see what's really inside.
• Larry Lustig: Either you have a problem in your nested lists (most likely what you think is a single string json value has really been parsed into 47 items) or else you're mistakenly calling execute() instead of executemany().

Answer 1

Your input is interpreted as a list of characters (that's where the '48 supplied' is coming from - 48 is the length of the <json value> string).

You will be able to pass your input in as a string if you wrap it in square brackets like so

["<json value>"]

The whole line would then look like

rows = [["a", ["<json value>"]]....["n", ["<json value>"]]]

Answer 2

Second argument passed to executemany() has to be list of touples, not list of lists:

[tuple(l) for l in rows]

From sqlite3 module documentation:

Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method.

The same applies to executemany().

• Larry Lustig: Lists are perfectly valid as parameter containers in Python / SQLite.

Answer 3

It's kind of a long shot... but perhaps you could quote the JSON values to ensure the parsing works as desired:

cursor.executemany("""INSERT OR IGNORE INTO FEATURES(UID, JSON) VALUES(?, '?')""", rows)

EDIT: Alternatively... this might force the json into a sting in the insertion?

rows = [ uid, '"{}"'.format( json_val ) for uid, json_val in rows ]
cursor.executemany("""INSERT OR IGNORE INTO FEATURES(UID, JSON) VALUES(?, ?)""", rows)

• l4mpi: You don't seem to know how prepared statements work - this is obviously wrong as sqlite takes care of the parameter bindings and there is no need to escape anything.