Django - 如何将'.json'文件插入SQLite DB？

Question

my '。Json文件' like

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

我搜索到谷歌，并试图解决这个问题…但没有解决。所以我使用pandas和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来验证该列的存在性，如果该列在行中不存在，则存储空字符串。