Insert the or ignore is ignored, without inserting
But met a problem today do not understand why can only use the replace, cannot ignore,
(1) there is a json file, each small unit has a name, titile, role three values
(2) now create three table, the relationship between the three table, as shown in the figure of the user in the table name, course in the table titile, member, the role of values in the table are from the json file, because it is a many-to-many relationship, establish the member joint foreign key in a table,
(3) other steps are done, I now want to finally get the role of input member table, this joint foreign key to call at the same time, the right to run out of the results of the code as shown in figure, but I was using the insert or ignore, so that the output database results in role column is NULL, I don't know why, because it feels member this table does not need to be updated (unlike calculation to count and so on need to be updated)
Complete the correct code is as follows:
The import json
The import sqlite3
Conn=sqlite3. Connect (' E:/sugizo/junior/coursera python/assignments/Using the Databases with python/exercise4-1/rosterdb. Sqlite ')
Cur=conn. Cursor ()
# Do some setup
Cur. Executescript (" '
DROP TABLE IF the EXISTS the User;
DROP TABLE IF the EXISTS Member;
DROP TABLE IF the EXISTS Course;
The CREATE TABLE User (
Id is an INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
The name TEXT UNIQUE
);
The CREATE TABLE Course (
Id is an INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
The title TEXT UNIQUE
);
The CREATE TABLE Member (
User_id INTEGER,
Course_id INTEGER,
Role of INTEGER,
The PRIMARY KEY (user_id, course_id)
)
"')
Fname=input (" Enter the file name: ')
If len (fname) & lt; 1:
Fname='E:/sugizo/junior/coursera python/assignments/Using the Databases with python/exercise4-1/roster_data. Json'
# [
# [" Charley si110 ", "", 1),
# [" Mea ", "si110", 0],
Str_data=https://bbs.csdn.net/topics/open (fname). The read ()
Json_data=https://bbs.csdn.net/topics/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 the 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 the column of
Cur. Execute (' SELECT id FROM Course WHERE the 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,))
MIT () # conn.com this cost time, so sometimes we donnot execute this line