Python Convert Json to SQLite?-CodePudding

so I am looking to convert from json to sqlite due to the fact that I have a json file that has 500,000 entries in and it is taking too long to append dump the file. This is what my json file looks like:

{"number": "1234567890", "used_services": [], "service": "television"},
{"number": "1234567890", "used_services": [], "service": "television"},
{"number": "1234567890", "used_services": [], "service": "television"},
{"number": "123457890", "used_services": [], "service": "television"},
{"number": "1234567890", "used_services": [], "service": "television"},
{"number": "123457890", "used_services": [], "service": "television"},
{"number": "1234567890", "used_services": [], "service": "television"},
{"number": "1234567890", "used_services": [], "service": "television"},
{"number": "1234567890", "used_services": [], "service": "television"},
{"number": "123467890", "used_services": [], "service": "television"},
{"number": "123567890", "used_services": [], "service": "television"},
{"number": "124567890", "used_services": [], "service": "television"},
{"number": "1234567890", "used_services": [], "service": "television"},
{"number": "1234567890", "used_services": [], "service": "television"},
{"number": "1234562390", "used_services": [], "service": "television"},
{"number": "1234567891", "used_services": [], "service": "television"},

In the future I will append a list inside of used_services, like so:

["cellphone", "television", "snailmail"]

import json
import sqlite3

connection = sqlite3.connect('db.sqlite')
cursor = connection.cursor()
cursor.execute('Create Table if not exists numbers (number Text, service Text, used_services Text)')

traffic = json.load(open('stock.json'))
columns = ['number','service','used_services']
for row in traffic:
    keys= tuple(row[c] for c in columns)
    cursor.execute('insert into numbers values(?,?,?)',keys)
    print(f'{row["name"]} data inserted Succefully')

connection.commit()
connection.close()

CodePudding user response：

Put everything in a list and use executemany() so it will insert them in large batches.

params = [tuple(row[c] for c in columns) for row in traffic]
cursor.executemany('insert into numbers values(?,?,?)', params)
connection.commit()