SQLite: Error binding parameter 1 - probably unsupported type-CodePudding

I read answers to question similar to this, but none worked in my case.

I created a table with 3 columns, like so:

CREATE TABLE raw_data(ID INTEGER PRIMARY KEY , timestamp INTEGER NOT NULL, payload JSON NOT NULL);

Populating this table with a test data, this way works:

# test data 1    
timer = 12345
load = 9999

query = "INSERT INTO raw_data(timestamp, payload) VALUES(?,?)"
data = (timer, load)

try:
    cursor.execute(query, data)
    con.commit()
except sqlite3.Error as error:
    print(f"Error while working with SQLite: {error}")

sqlite> select * from raw_data ;
1|12345|9999

However, when I load a json object file and run the script, I get the above frustrating error:

json file test.json (I can confirm this is a valid json object):

{
  "data": {
    "author_id": "497042614",
    "created_at": "2022-08-18T17:32:36.000Z",
    "geo": {
      "place_id": "00611f1548c7bdef"
    },
    "id": "1560318759969214473",
    "text": "What's your Primary School nickname?"
  },
  "matching_rules": [
    {
      "id": "1560077018183630848",
      "tag": "comma-separated-keywords"
    }
  ]
}

reading file and attempting to populate table:

timer = 6789
f = open('test.json')
load = json.load(f)

query = "INSERT INTO raw_data(timestamp, payload) VALUES(?,?)"
data = (timer, load)

try:
    cursor.execute(query, data)
    con.commit()
except sqlite3.Error as error:
    print(f"Error while working with SQLite: {error}")

Error:

Error while working with SQLite: Error binding parameter 1 - probably unsupported type.

But what's wrong with parameter 1 here, after all its value was auto inserted in the first example?

CodePudding user response：

You are not passing in a JSON string, you are passing in a Python object. There is no automatic conversion back to JSON when you do this.

You could just insert the unparsed JSON:

timer = 6789
with open('test.json') as f:
    json_data = f.read()

query = "INSERT INTO raw_data(timestamp, payload) VALUES(?,?)"
data = (timer, json_data)

try:
    cursor.execute(query, data)
    con.commit()
except sqlite3.Error as error:
    print(f"Error while working with SQLite: {error}")

Internally, SQLite3 just stores a string; the json1 extension doesn't require a column to have the JSON type, but it can be helpful for external applications that work with the database to be able to introspect the column type and expect JSON data.

You can register an adapter function to convert, say, dictionaries and lists to a JSON string, and a separate converter function to turn JSON data back in to Python objects, but without such niceties, the sqlite3 library will not do the conversion for you.