-2

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()
lemon
  • 14,875
  • 6
  • 18
  • 38
big boss
  • 11
  • 4

1 Answers1

1

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()
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I changed values to text, text, text assuming thats correct, but it's been hanging for about 15 mins now. Json file does have 500,000 lines though but still – big boss Jan 25 '23 at 23:51
  • 1
    You should first test it with a small list. – Barmar Jan 25 '23 at 23:57
  • True. I'm getting: Traceback (most recent call last): File cursor.executemany('insert into numbers values(number,used_services,service)', params) sqlite3.OperationalError: no such column: number not sure why since its defined in cursor and in the json – big boss Jan 26 '23 at 00:28
  • You're missing the `?` characters in the SQL. It should be `values(?, ?, ?)` – Barmar Jan 26 '23 at 00:30
  • Ah, ok. I tried that and got: Traceback (most recent call last): File , line 12, in cursor.executemany('insert into numbers values(?, ?, ?)', params) sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type. – big boss Jan 26 '23 at 00:31
  • You can't insert a list into a table column. What are you expecting all those `"used_services": []` to be in the database? – Barmar Jan 26 '23 at 00:35
  • Yes, because I need to track what services each number has been used on – big boss Jan 26 '23 at 00:39
  • You need to convert it to a string. If you're planning on putting a comma-separated list in the column, don't do that. Create another table with one row per service and a foreign key pointing to this table. See https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Barmar Jan 26 '23 at 00:40
  • What's wrong with having a comma separated list in the column? – big boss Jan 26 '23 at 01:14
  • Read the link I gave above. – Barmar Jan 26 '23 at 01:21