在上一步驟的基礎(chǔ)上缭召，使用sqlite3創(chuàng)建以下幾個表：
- ceremonies 存儲舉行時間和地點
- movies
- actors
- movies_actors 用于存儲movies和actors間的多對多關(guān)系
重點內(nèi)容：
- 數(shù)據(jù)庫的鏈接/表的創(chuàng)建
- commit()
- sqlite的特性，死鎖概念及原因
1.創(chuàng)建ceremonies
鏈接數(shù)據(jù)庫
import sqlite3
conn=sqlite3.connect('./data/nominations.db')
schema=conn.execute("pragma table_info(nominations);").fetchall()
first_ten=conn.execute("select * from nominations limit 10; ")
for row in schema:
print(row)
for row in first_ten:
print(row)
(0, 'Year', 'INTEGER', 0, None, 0)
(1, 'Category', 'TEXT', 0, None, 0)
(2, 'Nominee', 'TEXT', 0, None, 0)
(3, 'Won', 'INTEGER', 0, None, 0)
(4, 'Movie', 'TEXT', 0, None, 0)
(5, 'Character', 'TEXT', 0, None, 0)
(2010, 'Actor -- Leading Role', 'Javier Bardem', 0, 'Biutiful ', 'Uxbal')
(2010, 'Actor -- Leading Role', 'Jeff Bridges', 0, 'True Grit ', 'Rooster Cogburn')
(2010, 'Actor -- Leading Role', 'Jesse Eisenberg', 0, 'The Social Network ', 'Mark Zuckerberg')
(2010, 'Actor -- Leading Role', 'Colin Firth', 1, "The King's Speech ", 'King George VI')
(2010, 'Actor -- Leading Role', 'James Franco', 0, '127 Hours ', 'Aron Ralston')
(2010, 'Actor -- Supporting Role', 'Christian Bale', 1, 'The Fighter ', 'Dicky Eklund')
(2010, 'Actor -- Supporting Role', 'John Hawkes', 0, "Winter's Bone ", 'Teardrop')
(2010, 'Actor -- Supporting Role', 'Jeremy Renner', 0, 'The Town ', 'James Coughlin')
(2010, 'Actor -- Supporting Role', 'Mark Ruffalo', 0, 'The Kids Are All Right ', 'Paul')
(2010, 'Actor -- Supporting Role', 'Geoffrey Rush', 0, "The King's Speech ", 'Lionel Logue')
創(chuàng)建表并插入數(shù)據(jù)：
conn.execute('''create table ceremonies(
id integer primary key,
Year integer,
Host text
);''')
years_hosts= [(2010, "Steve Martin"),
(2009, "Hugh Jackman"),
(2008, "Jon Stewart"),
(2007, "Ellen DeGeneres"),
(2006, "Jon Stewart"),
(2005, "Chris Rock"),
(2004, "Billy Crystal"),
(2003, "Steve Martin"),
(2002, "Whoopi Goldberg"),
(2001, "Steve Martin"),
(2000, "Billy Crystal")
]
#主鍵不用指定,系統(tǒng)自動設(shè)置
conn.execute("pragma foreign_keys=ON;")#每次插入操作前建議設(shè)置，防止插入不含外鍵的非法數(shù)據(jù)
conn.executemany("insert into ceremonies(Year,Host) values(?,?);",years_hosts)
conn.commit()#注意：每次執(zhí)行修改操作應(yīng)提交哥桥，close不會自動調(diào)用commit蚓再，直接關(guān)閉數(shù)據(jù)庫會導(dǎo)致修改丟失
result=conn.execute('select * from ceremonies;').fetchall()
print(result)
返回一個由tuple組成的list：
[(1, 2010, 'Steve Martin'),
(2, 2009, 'Hugh Jackman'), (3, 2008, 'Jon Stewart'),
(4, 2007, 'Ellen DeGeneres'), (5, 2006, 'Jon Stewart'),
(6, 2005, 'Chris Rock'), (7, 2004, 'Billy Crystal'),
(8, 2003, 'Steve Martin'), (9, 2002, 'Whoopi Goldberg'),
(10, 2001, 'Steve Martin'), (11, 2000, 'Billy Crystal')]
2. nominations表
nominations中year列已經(jīng)存在于ceremonies表中了站粟，即冗余列浦妄，應(yīng)該刪除呢灶；而sqlite為保持輕量級吴超，不允許對表結(jié)構(gòu)進行修改，所以想刪除某列只能通過新建的方式鸯乃。
conn.execute('''create table nominations_two(
id integer primary key,
category text,
nominee text,
movie text,
character text,
won integer,
ceremonies_id integer,
foreign key(ceremonies_id) references ceremonies(id)
);''')
useful_data=conn.execute('''select nominations.category, nominations.nominee, nominations.movie, nominations.character, nominations.won, ceremonies.id
from nominations
inner join ceremonies
on nominations.year==ceremonies.year;
''').fetchall()
conn.executemany("insert into nominations_two(category,nominee,movie,character,won,ceremonies_id) values(?,?,?,?,?,?)",useful_data)
print(conn.execute("select * from nominations_two limit 10;").fetchall())
conn.execute('DROP table nominations;')
conn.execute('alter table nominations_two rename to nominations;')
3.創(chuàng)建movies/actors/movies_actors表
創(chuàng)建表
conn.execute("create table movies(id integer primary key,movie text);")
conn.execute("create table actors(id integer primary key,actor text);")
conn.execute('''create table movies_actors(
id integer primary key,
movie_id integer references movies(id),
actor_id integer references actors(id));
''')
插入數(shù)據(jù)
conn.execute("insert into movies(Movie) select distinct movie from nominations;")#字段不區(qū)分大小寫
conn.execute("insert into actors(Actor) select distinct Nominee from nominations;")
movie_actor_pair=conn.execute("select distinct movie,Nominee from nominations;").fetchall()
conn.executemany('''insert into movies_actors(movie_id,actor_id)
values((select id from movies where movie==?),(select id from actors where actor==?));'''
,movie_actor_pair)
conn.commit()
conn.close()
創(chuàng)建完成鲸阻，此時我們可以通過終端或者可視化工具查看創(chuàng)建結(jié)果：
