Django如何将“.json”文件插入SQLite DB？

Question

我的“.json文件”类似

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

我正在谷歌上搜索，并试图解决这个问题。。但是没有解决。 所以我使用熊猫和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)

因此创建了DB，但是.json文件数据不保存在DB中 如何解决这个问题

Answer 1

您必须事先创建“test”表，遍历pandas数据帧df并将记录逐个插入表中：

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()

由于并非所有记录对所有列都具有相同的有效值，因此需要使用try/except验证列的存在性，如果行中不存在该列，则需要存储空字符串