JSON insert error, Sqlite3, Python

0
votes

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?

3
pythonjsonsqlite
I have a list of multiple rows to insert, so isn't a list or lists the proper way to use that function?>\ - code base 5000
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. - l4mpi
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(). - Larry Lustig

3 Answers

0
votes

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>"]]]
-1
votes

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().

-2
votes

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)