vendredi 12 juin 2015

Issue with sending nested json objects to sqlite db

I have a large file of json objects that is one object per line. I would like to send this data to a sqlite database. Each json object is formatted like this:

{"1": {"google": 5},
 "2": "",
 "3": 0.0,
 "4": [10.0, 20.0, 30.0, 20.0],
 "6": "",
 "7": 2,
 "8": {},
 "9": 1.0,
 "10": 0.0}

I tried this code and got the following error:

query = "insert into daily values (?,?,?,?,?,?,?,?,?,?)"

columns = ['1', '2', '3', '4',
           '5', '6','7',
           '8', '9', '10']

with open(JSON_FILE) as f:
    head = islice(f, 5)
    for x in head:
        line = json.loads(x)
        keys = tuple(line[c] for c in columns)
        c.execute(query, keys)

InterfaceError: Error binding parameter 0 - probably unsupported type.

Two questions:

1) Why am I getting this error? I assume it has something to do with the the first hey's value being a nested dict, but I'm not sure how to fix it.

2) Currently I created the table like this:

c.execute('''create table daily
         (1 text,
          2 text,
          3 text,
          4 text,
          5 text,
          6 text,
          7 text,
          8 text,
          9 text,
          10 text)''')

Ideally, I would like to have separate columns for the key:value pair for the first key in the object. Again, I assume this is possible, but I'm not quite sure how to do this.

Aucun commentaire:

Enregistrer un commentaire