python3，sqlite3 如何把json文件存在数据库

这个就比XML简单多了，因为json有自己的格式和相关库，直接load一下就是个数组啊！

以下的用例依旧来自py4e，目的是把json文档按合适的关系模式存在数据库中。json中记录用户名，课程名称，和关系role（0是学生，1是教师）。为了减少重复，先建两张表course和user，然后中间建一个member去做映射。

relationship.png

1. json的一段，先写代码调一下json的输出。

[
  [
    "Kenan",
    "si110",
    1
  ],
  [
    "J",
    "si110",
    0
  ],
  [
    "Shayna",
    "si110",
    0
  ],
  [
    "Kristofer",
    "si110",
    0
  ],
]

#python
import json

fname = 'roster_data.json'
handle = open(fname).read()
data = json.loads(handle)

for entry in data:
    print(entry)

# python output 
('Kenan', 'si110', 1)
('J', 'si110', 0)
('Shayna', 'si110', 0)

2. 然后把数据库加上就行了。

import json
import sqlite3

conn = sqlite3.connect('rosterdb.sqlite')
cur = conn.cursor()

# setup tables and schema 
cur.executescript('''
DROP TABLE IF EXISTS User;
DROP TABLE IF EXISTS Member;
DROP TABLE IF EXISTS Course;

CREATE TABLE User (
    id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name   TEXT UNIQUE
);

CREATE TABLE Course (
    id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    title  TEXT UNIQUE
);

CREATE TABLE Member (
    user_id     INTEGER,
    course_id   INTEGER,
    role        INTEGER,
    PRIMARY KEY (user_id, course_id)
)
''')

#fname = 'roster_data.json'
fname = input('Enter file name: ')
if len(fname) < 1:
    fname = 'roster_data.json'

#read JSON file 
str_data = open(fname).read()
json_data = json.loads(str_data)

for entry in json_data:

    name = entry[0];
    title = entry[1];
    role = entry[2];

    print((name, title,role))

    cur.execute('''INSERT OR IGNORE INTO User (name)
        VALUES ( ? )''', ( name, ) )
    cur.execute('SELECT id FROM User WHERE name = ? ', (name, ))
    user_id = cur.fetchone()[0]

    cur.execute('''INSERT OR IGNORE INTO Course (title)
        VALUES ( ? )''', ( title, ) )
    cur.execute('SELECT id FROM Course WHERE title = ? ', (title, ))
    course_id = cur.fetchone()[0]

    cur.execute('''INSERT OR REPLACE INTO Member
        (user_id, course_id,role) VALUES ( ?, ?, ? )'''
        ( user_id, course_id,role) )

conn.commit()

2018.4.29