CODEWITHSUNDEEP
pythonjson
Sr New
Sr New

Reputation: 77

Error while reading JSON to keep in sqllite

I am actually trying to get a json file and keep in sqllite. when I am reading data from JSON I am geting Errors.

My Data is (in a file) :

{"_id":{"$oid":"5e15608e952f72ddc69cc1bd"},"a":1,"a1":"city1","b":1,"C":21,"D":"ECE","E":""}
{"_id":{"$oid":"5e15608e952f72ddc69cc1b2"},"a":2,"a1":"city2","b":14,"C":31,"D":"ECE","E":""}
{"_id":{"$oid":"5e15608e952f72ddc69cc1b3"},"a":3,"a1":"city3","b":12,"C":41,"D":"ECE","E":""}
{"_id":{"$oid":"5e15608e952f72ddc69cc1b4"},"a":4,"a1":"city4","b":11,"C":51,"D":"ECE","E":""}

My Code is :

with open(r'C:\Users\Logs1.json', encoding='utf-8-sig') as json_file:
    json_data = json.loads(json_file.read())

#Aim of the block is to get the list of the columns in the JSON file.
    columns = []
    column = []
    for data in json_data:
        column = list(data.keys())
        print("the value is "+column)
        for col in column:
            if col not in columns:
                columns.append(col)
                print("the value is col"+col)

#Here we get values of the columns in the JSON file in the right order.   
    value = []
    values = [] 
    for data in json_data:
        for i in columns:
            value.append(str(dict(data).get(i)))   
        values.append(list(value)) 
        value.clear()

Error I am getting is :

Traceback (most recent call last):
  File ".\sqlliteutility.py", line 10, in <module>
    json_data = json.loads(json_file.read())
  File "C:\Users\Anaconda3\lib\json\__init__.py", line 348, in loads
    return _default_decoder.decode(s)
  File "C:\Users\Anaconda3\lib\json\decoder.py", line 340, in decode
    raise JSONDecodeError("Extra data", s, end)
json.decoder.JSONDecodeError: Extra data: line 2 column 1 (char 93)

I am writting this utility so that I can keep this data in SQLlite

Upvotes: 0

Views: 57

Answers (3)

Mark Tolonen
Mark Tolonen

Reputation: 177775

Your data is in JSON Lines format. Each line is a JSON record. Process each line individually:

import json
import pprint

with open('data.jsonl', encoding='utf8') as f:
    data = [json.loads(line) for line in f]

pprint.pprint(data,width=120)

Output:

[{'C': 21, 'D': 'ECE', 'E': '', '_id': {'$oid': '5e15608e952f72ddc69cc1bd'}, 'a': 1, 'a1': 'city1', 'b': 1},
 {'C': 31, 'D': 'ECE', 'E': '', '_id': {'$oid': '5e15608e952f72ddc69cc1b2'}, 'a': 2, 'a1': 'city2', 'b': 14},
 {'C': 41, 'D': 'ECE', 'E': '', '_id': {'$oid': '5e15608e952f72ddc69cc1b3'}, 'a': 3, 'a1': 'city3', 'b': 12},
 {'C': 51, 'D': 'ECE', 'E': '', '_id': {'$oid': '5e15608e952f72ddc69cc1b4'}, 'a': 4, 'a1': 'city4', 'b': 11}]

Upvotes: 1

Aswin
Aswin

Reputation: 349

you can also export the data in CSV format with the selective field. which would be easier to digest through python. 

mongoexport -d test -c viva --query ''  --type csv --out vida.csv --fields a,b,c

Upvotes: 0

Subbeh
Subbeh

Reputation: 924

Your json data isn't formatted properly. This is probably how it should look:

[{"_id":{"$oid":"5e15608e952f72ddc69cc1bd"},"a":1,"a1":"city1","b":1,"C":21,"D":"ECE","E":""},
{"_id":{"$oid":"5e15608e952f72ddc69cc1b2"},"a":2,"a1":"city2","b":14,"C":31,"D":"ECE","E":""},
{"_id":{"$oid":"5e15608e952f72ddc69cc1b3"},"a":3,"a1":"city3","b":12,"C":41,"D":"ECE","E":""},
{"_id":{"$oid":"5e15608e952f72ddc69cc1b4"},"a":4,"a1":"city4","b":11,"C":51,"D":"ECE","E":""}]

Note the commas separating the records and brackets at the beginning and end of the data

Upvotes: 0

Related Questions