Django - how can i insert '.json' file to SQLite DB?

Question

my '.json file' like

{
  "users": [
    {
      "userId": 1,
      "firstName": "AAAAA",
      "lastName": "as23",
      "phoneNumber": "123456",
      "emailAddress": "[email protected]",
      "homepage": "https://amogg.tistory.com/1"
    },
    {
      "userId": 2,
      "firstName": "BBBB",
      "lastName": "h5jdd",
      "phoneNumber": "123456",
      "homepage": "https://amogg.tistory.com/2"
    },
    {
      "userId": 3,
...

i was search that to google, and try to this problem.. but unresolved. so i use pandas and sqlite3

import sqlite3 as db
import pandas as pd

df = pd.read_json('test.json')
con = db.connect('./test.db')

df.to_sql('test', con=con)

so DB is created, but .json file data dont save in DB how can solve this problem...?

Sherar MDP · Accepted Answer · 2021-09-03 03:36:20Z

You will have to create the table 'test' beforehand, iterate over the pandas dataframe df and insert the records into the table one by one:

import sqlite3 as db
import pandas as pd

df = pd.read_json('test.json', orient='index')
con = db.connect('./test.db')
cursor = con.cursor()
cursor.execute('''create table test (userId int primary key,
                                     firstName text,
                                     lastName text,
                                     phoneNumber text,
                                     emailAddress text,
                                     homePage text)''')


for index, row in df.iterrows():
    for element in row.iteritems():
        try:
            firstName = element[1]['firstName']
        except:
            firstName = ''
        try:
            lastName = element[1]['lastName']
        except:
            lastName = ''
        try:
            phoneNumber = element[1]['phoneNumber']
        except:
            phoneNumber = ''
        try:
            emailAddress = element[1]['emailAddress']
        except:
            emailAddress = ''
        try:
            homepage = element[1]['homepage']
        except:
            homepage = ''

        cursor.execute("INSERT INTO test VALUES (?,?,?,?,?,?)", (element[1]['userId'],
                                                                 firstName,
                                                                 lastName,
                                                                 phoneNumber,
                                                                 emailAddress,
                                                                 homepage))

con.commit()
con.close()

Since not all the records have the same valid values for all the columns, you will need to validate the existance of the column with a try/except and store an empty string if the column does not exist in the row.

Collectives™ on Stack Overflow

Django - how can i insert '.json' file to SQLite DB?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related