我明白insert or replace是有则更新,无则插入
insert or ignore是有则忽略,无则插入
但是今天遇到一个问题不明白 为何只能用replace,不能用ignore。
(1)有一个json文件,每个小的单元里都有name, titile, role三个值
(2)现在创建三个table, 三张table的关系如图所示,其中user表中的name,course表中的titile,member表中的role的值都来自json文件,由于是多对多关系,建立了member表中的联合外键。
(3)现在我其他步骤都做好了,最后想把role的值输入member表中,这个要同时调用联合外键,正确能够跑出结果的代码如图所示,但是我最初用的是insert or ignore,这样输出的数据库结果在role列就是NULL。我不明白为什么,因为感觉member这个表并不需要更新(不像计算数数之类需要更新)
完整的正确代码如下:
import json
import sqlite3
conn = sqlite3.connect('E:/吴悠/大三下/coursera python/assignments/Using Databases with Python/exercise4-1/rosterdb.sqlite')
cur = conn.cursor()
# Do some setup
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 = input('Enter file name: ')
if len(fname) < 1:
fname = 'E:/吴悠/大三下/coursera python/assignments/Using Databases with Python/exercise4-1/roster_data.json'
# [
# [ "Charley", "si110", 1 ],
# [ "Mea", "si110", 0 ],
str_data = open(fname).read()
json_data = json.loads(str_data)
for entry in json_data:#entry itself is a row
name = entry[0];
title = entry[1];
role=entry[2]
print((name, title,role))#here print as a tuple
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, ) )# this line input the value into column
cur.execute('SELECT id FROM Course WHERE title = ? ', (title, ))#this line
course_id = cur.fetchone()[0]
cur.execute('''INSERT OR REPLACE INTO Member
(user_id, course_id) VALUES ( ?, ? )''',
( user_id, course_id ) )# here is a tuple too
cur.execute('''
INSERT OR REPLACE INTO Member(user_id,course_id,role)
VALUES (?,?,?)''',(user_id,course_id,role,))
conn.commit()# this cost time, so sometimes we donnot execute this line