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?

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
– code base 5000, Commented Apr 4, 2014 at 18:05
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
– l4mpi, Commented Apr 4, 2014 at 18:19
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
– Larry Lustig, Commented Nov 19, 2014 at 16:40

Marcel Bro · Accepted Answer · 2021-04-27 23:27:36Z

0

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

answered Apr 27, 2021 at 23:27

Marcel Bro

5,1354 gold badges50 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tithen-Firion · Accepted Answer · 2014-11-19 16:14:56Z

-1

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

answered Nov 19, 2014 at 16:14

Tithen-Firion

6257 silver badges18 bronze badges

1 Comment

Larry Lustig Over a year ago

Lists are perfectly valid as parameter containers in Python / SQLite.

Travis D. · Accepted Answer · 2014-04-04 18:14:08Z

-2

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)

answered Apr 4, 2014 at 18:14

Travis D.

3421 silver badge9 bronze badges

1 Comment

l4mpi Over a year ago

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.

Collectives™ on Stack Overflow

JSON insert error, Sqlite3, Python

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related