In [19]:
import pandas as pd
import sqlite3
In [20]:
df = pd.read_csv("Data/academy_awards.csv", encoding = "ISO-8859-1")
print(df.head())
print(df.shape)
Year Category Nominee \
0 2010 (83rd) Actor -- Leading Role Javier Bardem
1 2010 (83rd) Actor -- Leading Role Jeff Bridges
2 2010 (83rd) Actor -- Leading Role Jesse Eisenberg
3 2010 (83rd) Actor -- Leading Role Colin Firth
4 2010 (83rd) Actor -- Leading Role James Franco
Additional Info Won? Unnamed: 5 Unnamed: 6 \
0 Biutiful {'Uxbal'} NO NaN NaN
1 True Grit {'Rooster Cogburn'} NO NaN NaN
2 The Social Network {'Mark Zuckerberg'} NO NaN NaN
3 The King's Speech {'King George VI'} YES NaN NaN
4 127 Hours {'Aron Ralston'} NO NaN NaN
Unnamed: 7 Unnamed: 8 Unnamed: 9 Unnamed: 10
0 NaN NaN NaN NaN
1 NaN NaN NaN NaN
2 NaN NaN NaN NaN
3 NaN NaN NaN NaN
4 NaN NaN NaN NaN
(10137, 11)
this dataset is a edited version of kaggle's academy-awards dataset, include all the nominees and winners in Oscar history 1927-2010
In [38]:
unnamed_cols = df.columns.tolist()[4:]
for i in unnamed_cols:
print(df[i].value_counts())
NO 7168 YES 2955 water and avalanches with familiar operators inspired by image compositing and painting operations. [Digital Imaging Technology]" 1 000 agents were controlled in several scenes. [Digital Imaging Technology]" 1 as well as 3D prosthetic appliances ranging in size from small wounds to entire torsos. They utilize self-adhesive material that features an unprecedented combination of tissue-thin edges 1 the Academy presents a Special Award to that master of fun 1 complex cloth could be achieved efficiently and robustly. Their work provided the conceptual foundation for many cloth simulation systems in use today. [Digital Imaging Technology]" 1 direct radiator style motion picture loudspeaker systems. The work of John M. Eargle 1 for his contributions to the development of motion pictures as entertainment." 1 economic and realistic filming of action sequences that may involve principal actors and dialogue. [Stage Operations]" 1 this premier reference manual has had a significant impact on decades of motion picture photography around the world." 1 and Tails" numbers from Top Hat [came in 2nd]" 1 which significantly reduces the need for painstaking 1 deforming objects. [Digital Imaging Technology]" 1 the "Caricature" system provides a degree of subtlety and refinement not possible with other systems. [Special Photographic]" 1 more conventional fog units. [Stage Operations]" 1 Name: Won?, dtype: int64 * 7 discoverer of stars 1 resilience 1 error-prone measurements on sets. [Digital Imaging Technology]" 1 D.B. "Don" Keele and Mark E. Engebretson has resulted in the over 20-year dominance of constant-directivity 1 Name: Unnamed: 5, dtype: int64 * 9 direct radiator bass style cinema loudspeaker systems. [Sound]" 1 flexibility and water resistance 1 sympathetic 1 Name: Unnamed: 6, dtype: int64 * 1 while requiring no dangerous solvents. [Systems]" 1 kindly 1 Name: Unnamed: 7, dtype: int64 understanding comedy genius - Mack Sennett."" 1 * 1 Name: Unnamed: 8, dtype: int64 * 1 Name: Unnamed: 9, dtype: int64 * 1 Name: Unnamed: 10, dtype: int64
In [22]:
#remove additional info from var "Year" and convert it to integer
df["Year"] = df["Year"].str[:4].astype("int64")
In [23]:
print(df.head())
Year Category Nominee \
0 2010 Actor -- Leading Role Javier Bardem
1 2010 Actor -- Leading Role Jeff Bridges
2 2010 Actor -- Leading Role Jesse Eisenberg
3 2010 Actor -- Leading Role Colin Firth
4 2010 Actor -- Leading Role James Franco
Additional Info Won? Unnamed: 5 Unnamed: 6 \
0 Biutiful {'Uxbal'} NO NaN NaN
1 True Grit {'Rooster Cogburn'} NO NaN NaN
2 The Social Network {'Mark Zuckerberg'} NO NaN NaN
3 The King's Speech {'King George VI'} YES NaN NaN
4 127 Hours {'Aron Ralston'} NO NaN NaN
Unnamed: 7 Unnamed: 8 Unnamed: 9 Unnamed: 10
0 NaN NaN NaN NaN
1 NaN NaN NaN NaN
2 NaN NaN NaN NaN
3 NaN NaN NaN NaN
4 NaN NaN NaN NaN
In [25]:
#filter out the awards after 2000
lt2000 = df[df["Year"] > 2000]
lt2000.shape
Out[25]:
(1294, 11)
In [28]:
#filter out the categories that are not within the actor-related categories
award_categories = ["Actress -- Supporting Role", "Actor -- Leading Role", "Actress -- Leading Role", "Actor -- Supporting Role"]
nominations = lt2000[lt2000["Category"].isin(award_categories)]
nominations.shape
Out[28]:
(200, 11)
In [36]:
yes_no = {"YES" : 1, "NO": 0}
nominations["Won"] = nominations["Won?"]
/Applications/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
In [37]:
nominations.head()
Out[37]:
| Year | Category | Nominee | Additional Info | Won? | Unnamed: 5 | Unnamed: 6 | Unnamed: 7 | Unnamed: 8 | Unnamed: 9 | Unnamed: 10 | Won | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2010 | Actor -- Leading Role | Javier Bardem | Biutiful {'Uxbal'} | 0 | NaN | NaN | NaN | NaN | NaN | NaN | 0 |
| 1 | 2010 | Actor -- Leading Role | Jeff Bridges | True Grit {'Rooster Cogburn'} | 0 | NaN | NaN | NaN | NaN | NaN | NaN | 0 |
| 2 | 2010 | Actor -- Leading Role | Jesse Eisenberg | The Social Network {'Mark Zuckerberg'} | 0 | NaN | NaN | NaN | NaN | NaN | NaN | 0 |
| 3 | 2010 | Actor -- Leading Role | Colin Firth | The King's Speech {'King George VI'} | 1 | NaN | NaN | NaN | NaN | NaN | NaN | 1 |
| 4 | 2010 | Actor -- Leading Role | James Franco | 127 Hours {'Aron Ralston'} | 0 | NaN | NaN | NaN | NaN | NaN | NaN | 0 |
In [39]:
unnamed_cols
Out[39]:
['Won?', 'Unnamed: 5', 'Unnamed: 6', 'Unnamed: 7', 'Unnamed: 8', 'Unnamed: 9', 'Unnamed: 10']
In [43]:
final_nominations = nominations.drop(unnamed_cols, axis = 1)
final_nominations.shape
Out[43]:
(200, 5)
In [46]:
addinfo1 = final_nominations["Additional Info"].str.rstrip("'}")
print(addinfo1)
0 Biutiful {'Uxbal
1 True Grit {'Rooster Cogburn
2 The Social Network {'Mark Zuckerberg
3 The King's Speech {'King George VI
4 127 Hours {'Aron Ralston
5 The Fighter {'Dicky Eklund
6 Winter's Bone {'Teardrop
7 The Town {'James Coughlin
8 The Kids Are All Right {'Paul
9 The King's Speech {'Lionel Logue
10 The Kids Are All Right {'Nic
11 Rabbit Hole {'Becca
12 Winter's Bone {'Ree
13 Black Swan {'Nina Sayers/The Swan Queen
14 Blue Valentine {'Cindy
15 The Fighter {'Charlene Fleming
16 The King's Speech {'Queen Elizabeth
17 The Fighter {'Alice Ward
18 True Grit {'Mattie Ross
19 Animal Kingdom {'Janine 'Smurf' Cody
135 Crazy Heart {'Bad Blake
136 Up in the Air {'Ryan Bingham
137 A Single Man {'George
138 Invictus {'Nelson Mandela
139 The Hurt Locker {'Staff Sergeant William James
140 Invictus {'Francois Pienaar
141 The Messenger {'Captain Tony Stone
142 The Last Station {'Tolstoy
143 The Lovely Bones {'George Harvey
144 Inglourious Basterds {'Col. Hans Landa
...
1040 Frida {'Frida Kahlo
1041 The Hours {'Virginia Woolf
1042 Unfaithful {'Connie Sumner
1043 Far from Heaven {'Cathy Whitaker
1044 Chicago {'Roxie Hart
1045 About Schmidt {'Roberta Hertzel
1046 The Hours {'Laura Brown
1047 Chicago {'Matron Mama Morton
1048 Adaptation {'Susan Orlean
1049 Chicago {'Velma Kelly
1157 A Beautiful Mind {'John Nash
1158 I Am Sam {'Sam Dawson
1159 Ali {'Muhammad Ali
1160 Training Day {'Alonzo
1161 In the Bedroom {'Matt Fowler
1162 Iris {'John Bayley
1163 Training Day {'Jake
1164 Sexy Beast {'Don Logan
1165 The Lord of the Rings: The Fellowship of the R...
1166 Ali {'Howard Cosell
1167 Monster's Ball {'Leticia Musgrove
1168 Iris {'Iris Murdoch
1169 Moulin Rouge {'Satine
1170 In the Bedroom {'Ruth Fowler
1171 Bridget Jones's Diary {'Bridget Jones
1172 A Beautiful Mind {'Alicia Nash
1173 Gosford Park {'Mrs. Wilson
1174 Gosford Park {'Constance, Countess of Trentham
1175 In the Bedroom {'Natalie Strout
1176 Iris {'Young Iris Murdoch
Name: Additional Info, Length: 200, dtype: object
In [52]:
movie = []
characters = []
for item in addinfo1:
result = item.split(" {'")
movie.append(result[0])
characters.append(result[1])
final_nominations["Movie"] = movie
final_nominations["Character"] = characters
print(final_nominations.head(10))
Year Category Nominee \
0 2010 Actor -- Leading Role Javier Bardem
1 2010 Actor -- Leading Role Jeff Bridges
2 2010 Actor -- Leading Role Jesse Eisenberg
3 2010 Actor -- Leading Role Colin Firth
4 2010 Actor -- Leading Role James Franco
5 2010 Actor -- Supporting Role Christian Bale
6 2010 Actor -- Supporting Role John Hawkes
7 2010 Actor -- Supporting Role Jeremy Renner
8 2010 Actor -- Supporting Role Mark Ruffalo
9 2010 Actor -- Supporting Role Geoffrey Rush
Additional Info Won Movie \
0 Biutiful {'Uxbal'} 0 Biutiful
1 True Grit {'Rooster Cogburn'} 0 True Grit
2 The Social Network {'Mark Zuckerberg'} 0 The Social Network
3 The King's Speech {'King George VI'} 1 The King's Speech
4 127 Hours {'Aron Ralston'} 0 127 Hours
5 The Fighter {'Dicky Eklund'} 1 The Fighter
6 Winter's Bone {'Teardrop'} 0 Winter's Bone
7 The Town {'James Coughlin'} 0 The Town
8 The Kids Are All Right {'Paul'} 0 The Kids Are All Right
9 The King's Speech {'Lionel Logue'} 0 The King's Speech
Character
0 Uxbal
1 Rooster Cogburn
2 Mark Zuckerberg
3 King George VI
4 Aron Ralston
5 Dicky Eklund
6 Teardrop
7 James Coughlin
8 Paul
9 Lionel Logue
In [53]:
final_nominations = final_nominations.drop("Additional Info", axis = 1)
In [54]:
final_nominations.head()
Out[54]:
| Year | Category | Nominee | Won | Movie | Character | |
|---|---|---|---|---|---|---|
| 0 | 2010 | Actor -- Leading Role | Javier Bardem | 0 | Biutiful | Uxbal |
| 1 | 2010 | Actor -- Leading Role | Jeff Bridges | 0 | True Grit | Rooster Cogburn |
| 2 | 2010 | Actor -- Leading Role | Jesse Eisenberg | 0 | The Social Network | Mark Zuckerberg |
| 3 | 2010 | Actor -- Leading Role | Colin Firth | 1 | The King's Speech | King George VI |
| 4 | 2010 | Actor -- Leading Role | James Franco | 0 | 127 Hours | Aron Ralston |
In [ ]:
#automatically setup new database and pass in data with pandas.to_sql method
conn = sqlite3.connect("nominations.db")
final_nominations.to_sql("nominations", conn, index = False)
In [68]:
schema = conn.execute("pragma table_info(nominations)").fetchall()
In [69]:
conn = sqlite3.connect("nominations.db")
first_ten = conn.execute("select * from nominations limit 10").fetchall()
In [70]:
for item in schema:
print(item)
(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)
In [71]:
for item in first_ten:
print(item)
(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')
In [72]:
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"),
]
In [ ]:
conn.execute('''
create table ceremonies(
id integer PRIMARY KEY,
Year integer,
Host text)''').fetchall()
In [76]:
insert_query = "INSERT INTO ceremonies (Year, Host) VALUES (?, ?);"
conn.executemany(insert_query, years_hosts)
Out[76]:
<sqlite3.Cursor at 0x1127abe30>
In [77]:
conn.execute("select * from ceremonies limit 10").fetchall()
Out[77]:
[(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')]
In [78]:
conn.execute("pragma table_info(ceremonies)").fetchall()
Out[78]:
[(0, 'id', 'integer', 0, None, 1), (1, 'Year', 'integer', 0, None, 0), (2, 'Host', 'text', 0, None, 0)]
In [79]:
#turn on foreign key constraints
conn.execute("PRAGMA foreign_keys = ON;")
Out[79]:
<sqlite3.Cursor at 0x1127abc70>
In [82]:
innerjoin = '''
SELECT nominations.category, nominations.nominee, nominations.movie, nominations.character, nominations.won, ceremonies.id
FROM nominations
INNER JOIN ceremonies ON
nominations.year == ceremonies.year
;'''
nomination2 = conn.execute(innerjoin).fetchall()
print(nomination2)
[('Actor -- Leading Role', 'Javier Bardem', 'Biutiful', 'Uxbal', 0, 1), ('Actor -- Leading Role', 'Jeff Bridges', 'True Grit', 'Rooster Cogburn', 0, 1), ('Actor -- Leading Role', 'Jesse Eisenberg', 'The Social Network', 'Mark Zuckerberg', 0, 1), ('Actor -- Leading Role', 'Colin Firth', "The King's Speech", 'King George VI', 1, 1), ('Actor -- Leading Role', 'James Franco', '127 Hours', 'Aron Ralston', 0, 1), ('Actor -- Supporting Role', 'Christian Bale', 'The Fighter', 'Dicky Eklund', 1, 1), ('Actor -- Supporting Role', 'John Hawkes', "Winter's Bone", 'Teardrop', 0, 1), ('Actor -- Supporting Role', 'Jeremy Renner', 'The Town', 'James Coughlin', 0, 1), ('Actor -- Supporting Role', 'Mark Ruffalo', 'The Kids Are All Right', 'Paul', 0, 1), ('Actor -- Supporting Role', 'Geoffrey Rush', "The King's Speech", 'Lionel Logue', 0, 1), ('Actress -- Leading Role', 'Annette Bening', 'The Kids Are All Right', 'Nic', 0, 1), ('Actress -- Leading Role', 'Nicole Kidman', 'Rabbit Hole', 'Becca', 0, 1), ('Actress -- Leading Role', 'Jennifer Lawrence', "Winter's Bone", 'Ree', 0, 1), ('Actress -- Leading Role', 'Natalie Portman', 'Black Swan', 'Nina Sayers/The Swan Queen', 1, 1), ('Actress -- Leading Role', 'Michelle Williams', 'Blue Valentine', 'Cindy', 0, 1), ('Actress -- Supporting Role', 'Amy Adams', 'The Fighter', 'Charlene Fleming', 0, 1), ('Actress -- Supporting Role', 'Helena Bonham Carter', "The King's Speech", 'Queen Elizabeth', 0, 1), ('Actress -- Supporting Role', 'Melissa Leo', 'The Fighter', 'Alice Ward', 1, 1), ('Actress -- Supporting Role', 'Hailee Steinfeld', 'True Grit', 'Mattie Ross', 0, 1), ('Actress -- Supporting Role', 'Jacki Weaver', 'Animal Kingdom', "Janine 'Smurf' Cody", 0, 1), ('Actor -- Leading Role', 'Jeff Bridges', 'Crazy Heart', 'Bad Blake', 1, 2), ('Actor -- Leading Role', 'George Clooney', 'Up in the Air', 'Ryan Bingham', 0, 2), ('Actor -- Leading Role', 'Colin Firth', 'A Single Man', 'George', 0, 2), ('Actor -- Leading Role', 'Morgan Freeman', 'Invictus', 'Nelson Mandela', 0, 2), ('Actor -- Leading Role', 'Jeremy Renner', 'The Hurt Locker', 'Staff Sergeant William James', 0, 2), ('Actor -- Supporting Role', 'Matt Damon', 'Invictus', 'Francois Pienaar', 0, 2), ('Actor -- Supporting Role', 'Woody Harrelson', 'The Messenger', 'Captain Tony Stone', 0, 2), ('Actor -- Supporting Role', 'Christopher Plummer', 'The Last Station', 'Tolstoy', 0, 2), ('Actor -- Supporting Role', 'Stanley Tucci', 'The Lovely Bones', 'George Harvey', 0, 2), ('Actor -- Supporting Role', 'Christoph Waltz', 'Inglourious Basterds', 'Col. Hans Landa', 1, 2), ('Actress -- Leading Role', 'Sandra Bullock', 'The Blind Side', 'Leigh Anne Tuohy', 1, 2), ('Actress -- Leading Role', 'Helen Mirren', 'The Last Station', 'Sofya', 0, 2), ('Actress -- Leading Role', 'Carey Mulligan', 'An Education', 'Jenny', 0, 2), ('Actress -- Leading Role', 'Gabourey Sidibe', "Precious: Based on the Novel 'Push' by Sapphire", 'Precious', 0, 2), ('Actress -- Leading Role', 'Meryl Streep', 'Julie & Julia', 'Julia Child', 0, 2), ('Actress -- Supporting Role', 'Penélope Cruz', 'Nine', 'Carla', 0, 2), ('Actress -- Supporting Role', 'Vera Farmiga', 'Up in the Air', 'Alex Goran', 0, 2), ('Actress -- Supporting Role', 'Maggie Gyllenhaal', 'Crazy Heart', 'Jean Craddock', 0, 2), ('Actress -- Supporting Role', 'Anna Kendrick', 'Up in the Air', 'Natalie Keener', 0, 2), ('Actress -- Supporting Role', "Mo'Nique", "Precious: Based on the Novel 'Push' by Sapphire", 'Mary', 1, 2), ('Actor -- Leading Role', 'Richard Jenkins', 'The Visitor', 'Walter', 0, 3), ('Actor -- Leading Role', 'Frank Langella', 'Frost/Nixon', 'Richard Nixon', 0, 3), ('Actor -- Leading Role', 'Sean Penn', 'Milk', 'Harvey Milk', 1, 3), ('Actor -- Leading Role', 'Brad Pitt', 'The Curious Case of Benjamin Button', 'Benjamin Button', 0, 3), ('Actor -- Leading Role', 'Mickey Rourke', 'The Wrestler', 'Randy', 0, 3), ('Actor -- Supporting Role', 'Josh Brolin', 'Milk', 'Dan White', 0, 3), ('Actor -- Supporting Role', 'Robert Downey Jr.', 'Tropic Thunder', 'Kirk Lazarus', 0, 3), ('Actor -- Supporting Role', 'Philip Seymour Hoffman', 'Doubt', 'Father Brendan Flynn', 0, 3), ('Actor -- Supporting Role', 'Heath Ledger', 'The Dark Knight', 'Joker', 1, 3), ('Actor -- Supporting Role', 'Michael Shannon', 'Revolutionary Road', 'John Givings', 0, 3), ('Actress -- Leading Role', 'Anne Hathaway', 'Rachel Getting Married', 'Kym', 0, 3), ('Actress -- Leading Role', 'Angelina Jolie', 'Changeling', 'Christine Collins', 0, 3), ('Actress -- Leading Role', 'Melissa Leo', 'Frozen River', 'Ray Eddy', 0, 3), ('Actress -- Leading Role', 'Meryl Streep', 'Doubt', 'Sister Aloysius Beauvier', 0, 3), ('Actress -- Leading Role', 'Kate Winslet', 'The Reader', 'Hanna Schmitz', 1, 3), ('Actress -- Supporting Role', 'Amy Adams', 'Doubt', 'Sister James', 0, 3), ('Actress -- Supporting Role', 'Penélope Cruz', 'Vicky Cristina Barcelona', 'Maria Elena', 1, 3), ('Actress -- Supporting Role', 'Viola Davis', 'Doubt', 'Mrs. Miller', 0, 3), ('Actress -- Supporting Role', 'Taraji P. Henson', 'The Curious Case of Benjamin Button', 'Queenie', 0, 3), ('Actress -- Supporting Role', 'Marisa Tomei', 'The Wrestler', 'Cassidy', 0, 3), ('Actor -- Leading Role', 'George Clooney', 'Michael Clayton', 'Michael Clayton', 0, 4), ('Actor -- Leading Role', 'Daniel Day-Lewis', 'There Will Be Blood', 'Daniel Plainview', 1, 4), ('Actor -- Leading Role', 'Johnny Depp', 'Sweeney Todd The Demon Barber of Fleet Street', 'Sweeney Todd', 0, 4), ('Actor -- Leading Role', 'Tommy Lee Jones', 'In the Valley of Elah', 'Hank Deerfield', 0, 4), ('Actor -- Leading Role', 'Viggo Mortensen', 'Eastern Promises', 'Nikolai', 0, 4), ('Actor -- Supporting Role', 'Casey Affleck', 'The Assassination of Jesse James by the Coward Robert Ford', 'Robert Ford', 0, 4), ('Actor -- Supporting Role', 'Javier Bardem', 'No Country for Old Men', 'Anton Chigurh', 1, 4), ('Actor -- Supporting Role', 'Philip Seymour Hoffman', "Charlie Wilson's War", 'Gust Avrakotos', 0, 4), ('Actor -- Supporting Role', 'Hal Holbrook', 'Into the Wild', 'Ron Franz', 0, 4), ('Actor -- Supporting Role', 'Tom Wilkinson', 'Michael Clayton', 'Arthur Edens', 0, 4), ('Actress -- Leading Role', 'Cate Blanchett', 'Elizabeth: The Golden Age', 'Queen Elizabeth I', 0, 4), ('Actress -- Leading Role', 'Julie Christie', 'Away from Her', 'Fiona', 0, 4), ('Actress -- Leading Role', 'Marion Cotillard', 'La Vie en Rose', 'Edith Piaf', 1, 4), ('Actress -- Leading Role', 'Laura Linney', 'The Savages', 'Wendy Savage', 0, 4), ('Actress -- Leading Role', 'Ellen Page', 'Juno', 'Juno MacGuff', 0, 4), ('Actress -- Supporting Role', 'Cate Blanchett', "I'm Not There", 'Jude', 0, 4), ('Actress -- Supporting Role', 'Ruby Dee', 'American Gangster', 'Mama Lucas', 0, 4), ('Actress -- Supporting Role', 'Saoirse Ronan', 'Atonement', 'Briony Tallis, aged 13', 0, 4), ('Actress -- Supporting Role', 'Amy Ryan', 'Gone Baby Gone', 'Helene McCready', 0, 4), ('Actress -- Supporting Role', 'Tilda Swinton', 'Michael Clayton', 'Karen Crowder', 1, 4), ('Actor -- Leading Role', 'Leonardo DiCaprio', 'Blood Diamond', 'Danny Archer', 0, 5), ('Actor -- Leading Role', 'Ryan Gosling', 'Half Nelson', 'Dan Dunne', 0, 5), ('Actor -- Leading Role', "Peter O'Toole", 'Venus', 'Maurice', 0, 5), ('Actor -- Leading Role', 'Will Smith', 'The Pursuit of Happyness', 'Chris Gardner', 0, 5), ('Actor -- Leading Role', 'Forest Whitaker', 'The Last King of Scotland', 'Idi Amin', 1, 5), ('Actor -- Supporting Role', 'Alan Arkin', 'Little Miss Sunshine', 'Grandpa', 1, 5), ('Actor -- Supporting Role', 'Jackie Earle Haley', 'Little Children', 'Ronnie J. McGorvey', 0, 5), ('Actor -- Supporting Role', 'Djimon Hounsou', 'Blood Diamond', 'Solomon Vandy', 0, 5), ('Actor -- Supporting Role', 'Eddie Murphy', 'Dreamgirls', "James 'Thunder' Early", 0, 5), ('Actor -- Supporting Role', 'Mark Wahlberg', 'The Departed', 'Dignam', 0, 5), ('Actress -- Leading Role', 'Penélope Cruz', 'Volver', 'Raimunda', 0, 5), ('Actress -- Leading Role', 'Judi Dench', 'Notes on a Scandal', 'Barbara Covett', 0, 5), ('Actress -- Leading Role', 'Helen Mirren', 'The Queen', 'The Queen', 1, 5), ('Actress -- Leading Role', 'Meryl Streep', 'The Devil Wears Prada', 'Miranda Priestly', 0, 5), ('Actress -- Leading Role', 'Kate Winslet', 'Little Children', 'Sarah Pierce', 0, 5), ('Actress -- Supporting Role', 'Adriana Barraza', 'Babel', 'Amelia', 0, 5), ('Actress -- Supporting Role', 'Cate Blanchett', 'Notes on a Scandal', 'Sheba Hart', 0, 5), ('Actress -- Supporting Role', 'Abigail Breslin', 'Little Miss Sunshine', 'Olive', 0, 5), ('Actress -- Supporting Role', 'Jennifer Hudson', 'Dreamgirls', 'Effie White', 1, 5), ('Actress -- Supporting Role', 'Rinko Kikuchi', 'Babel', 'Chieko', 0, 5), ('Actor -- Leading Role', 'Philip Seymour Hoffman', 'Capote', 'Truman Capote', 1, 6), ('Actor -- Leading Role', 'Terrence Howard', 'Hustle & Flow', 'DJay', 0, 6), ('Actor -- Leading Role', 'Heath Ledger', 'Brokeback Mountain', 'Ennis Del Mar', 0, 6), ('Actor -- Leading Role', 'Joaquin Phoenix', 'Walk the Line', 'John R. Cash', 0, 6), ('Actor -- Leading Role', 'David Strathairn', 'Good Night, and Good Luck.', 'Edward R. Murrow', 0, 6), ('Actor -- Supporting Role', 'George Clooney', 'Syriana', 'Bob Barnes', 1, 6), ('Actor -- Supporting Role', 'Matt Dillon', 'Crash', 'Officer Ryan', 0, 6), ('Actor -- Supporting Role', 'Paul Giamatti', 'Cinderella Man', 'Joe Gould', 0, 6), ('Actor -- Supporting Role', 'Jake Gyllenhaal', 'Brokeback Mountain', 'Jack Twist', 0, 6), ('Actor -- Supporting Role', 'William Hurt', 'A History of Violence', 'Richie Cusack', 0, 6), ('Actress -- Leading Role', 'Judi Dench', 'Mrs. Henderson Presents', 'Mrs. Laura Henderson', 0, 6), ('Actress -- Leading Role', 'Felicity Huffman', 'Transamerica', 'Bree', 0, 6), ('Actress -- Leading Role', 'Keira Knightley', 'Pride & Prejudice', 'Elizabeth Bennet', 0, 6), ('Actress -- Leading Role', 'Charlize Theron', 'North Country', 'Josey Aimes', 0, 6), ('Actress -- Leading Role', 'Reese Witherspoon', 'Walk the Line', 'June Carter', 1, 6), ('Actress -- Supporting Role', 'Amy Adams', 'Junebug', 'Ashley', 0, 6), ('Actress -- Supporting Role', 'Catherine Keener', 'Capote', 'Nelle Harper Lee', 0, 6), ('Actress -- Supporting Role', 'Frances McDormand', 'North Country', 'Glory', 0, 6), ('Actress -- Supporting Role', 'Rachel Weisz', 'The Constant Gardener', 'Tessa Quayle', 1, 6), ('Actress -- Supporting Role', 'Michelle Williams', 'Brokeback Mountain', 'Alma', 0, 6), ('Actor -- Leading Role', 'Don Cheadle', 'Hotel Rwanda', 'Paul Rusesabagina', 0, 7), ('Actor -- Leading Role', 'Johnny Depp', 'Finding Neverland', 'Sir James Matthew Barrie', 0, 7), ('Actor -- Leading Role', 'Leonardo DiCaprio', 'The Aviator', 'Howard Hughes', 0, 7), ('Actor -- Leading Role', 'Clint Eastwood', 'Million Dollar Baby', 'Frankie Dunn', 0, 7), ('Actor -- Leading Role', 'Jamie Foxx', 'Ray', 'Ray Charles', 1, 7), ('Actor -- Supporting Role', 'Alan Alda', 'The Aviator', 'Senator Ralph Owen Brewster', 0, 7), ('Actor -- Supporting Role', 'Thomas Haden Church', 'Sideways', 'Jack', 0, 7), ('Actor -- Supporting Role', 'Jamie Foxx', 'Collateral', 'Max', 0, 7), ('Actor -- Supporting Role', 'Morgan Freeman', 'Million Dollar Baby', 'Eddie Scrap-Iron Dupris', 1, 7), ('Actor -- Supporting Role', 'Clive Owen', 'Closer', 'Larry', 0, 7), ('Actress -- Leading Role', 'Annette Bening', 'Being Julia', 'Julia Lambert', 0, 7), ('Actress -- Leading Role', 'Catalina Sandino Moreno', 'Maria Full of Grace', 'Maria', 0, 7), ('Actress -- Leading Role', 'Imelda Staunton', 'Vera Drake', 'Vera', 0, 7), ('Actress -- Leading Role', 'Hilary Swank', 'Million Dollar Baby', 'Maggie Fitzgerald', 1, 7), ('Actress -- Leading Role', 'Kate Winslet', 'Eternal Sunshine of the Spotless Mind', 'Clementine Kruczynski', 0, 7), ('Actress -- Supporting Role', 'Cate Blanchett', 'The Aviator', 'Katharine Hepburn', 1, 7), ('Actress -- Supporting Role', 'Laura Linney', 'Kinsey', 'Clara McMillen', 0, 7), ('Actress -- Supporting Role', 'Virginia Madsen', 'Sideways', 'Maya', 0, 7), ('Actress -- Supporting Role', 'Sophie Okonedo', 'Hotel Rwanda', 'Tatiana Rusesabagina', 0, 7), ('Actress -- Supporting Role', 'Natalie Portman', 'Closer', 'Alice', 0, 7), ('Actor -- Leading Role', 'Johnny Depp', 'Pirates of the Caribbean: The Curse of the Black Pearl', 'Jack Sparrow', 0, 8), ('Actor -- Leading Role', 'Ben Kingsley', 'House of Sand and Fog', 'Behrani', 0, 8), ('Actor -- Leading Role', 'Jude Law', 'Cold Mountain', 'Inman', 0, 8), ('Actor -- Leading Role', 'Bill Murray', 'Lost in Translation', 'Bob Harris', 0, 8), ('Actor -- Leading Role', 'Sean Penn', 'Mystic River', 'Jimmy Markum', 1, 8), ('Actor -- Supporting Role', 'Alec Baldwin', 'The Cooler', 'Shelly Kaplow', 0, 8), ('Actor -- Supporting Role', 'Benicio Del Toro', '21 Grams', 'Jack Jordan', 0, 8), ('Actor -- Supporting Role', 'Djimon Hounsou', 'In America', 'Mateo', 0, 8), ('Actor -- Supporting Role', 'Tim Robbins', 'Mystic River', 'Dave Boyle', 1, 8), ('Actor -- Supporting Role', 'Ken Watanabe', 'The Last Samurai', 'Katsumoto', 0, 8), ('Actress -- Leading Role', 'Keisha Castle-Hughes', 'Whale Rider', 'Paikea', 0, 8), ('Actress -- Leading Role', 'Diane Keaton', "Something's Gotta Give", 'Erica Barry', 0, 8), ('Actress -- Leading Role', 'Samantha Morton', 'In America', 'Sarah', 0, 8), ('Actress -- Leading Role', 'Charlize Theron', 'Monster', 'Aileen Wuornos', 1, 8), ('Actress -- Leading Role', 'Naomi Watts', '21 Grams', 'Cristina Peck', 0, 8), ('Actress -- Supporting Role', 'Shohreh Aghdashloo', 'House of Sand and Fog', 'Nadi', 0, 8), ('Actress -- Supporting Role', 'Patricia Clarkson', 'Pieces of April', 'Joy Burns', 0, 8), ('Actress -- Supporting Role', 'Marcia Gay Harden', 'Mystic River', 'Celeste Boyle', 0, 8), ('Actress -- Supporting Role', 'Holly Hunter', 'Thirteen', 'Melanie', 0, 8), ('Actress -- Supporting Role', 'Renée Zellweger', 'Cold Mountain', 'Ruby Thewes', 1, 8), ('Actor -- Leading Role', 'Adrien Brody', 'The Pianist', 'Wladyslaw Szpilman', 1, 9), ('Actor -- Leading Role', 'Nicolas Cage', 'Adaptation', 'Charlie Kaufman & Donald Kaufman', 0, 9), ('Actor -- Leading Role', 'Michael Caine', 'The Quiet American', 'Thomas Fowler', 0, 9), ('Actor -- Leading Role', 'Daniel Day-Lewis', 'Gangs of New York', "Bill 'The Butcher' Cutting", 0, 9), ('Actor -- Leading Role', 'Jack Nicholson', 'About Schmidt', 'Warren Schmidt', 0, 9), ('Actor -- Supporting Role', 'Chris Cooper', 'Adaptation', 'John Laroche', 1, 9), ('Actor -- Supporting Role', 'Ed Harris', 'The Hours', 'Richard Brown', 0, 9), ('Actor -- Supporting Role', 'Paul Newman', 'Road to Perdition', 'John Rooney', 0, 9), ('Actor -- Supporting Role', 'John C. Reilly', 'Chicago', 'Amos Hart', 0, 9), ('Actor -- Supporting Role', 'Christopher Walken', 'Catch Me If You Can', 'Frank Abagnale', 0, 9), ('Actress -- Leading Role', 'Salma Hayek', 'Frida', 'Frida Kahlo', 0, 9), ('Actress -- Leading Role', 'Nicole Kidman', 'The Hours', 'Virginia Woolf', 1, 9), ('Actress -- Leading Role', 'Diane Lane', 'Unfaithful', 'Connie Sumner', 0, 9), ('Actress -- Leading Role', 'Julianne Moore', 'Far from Heaven', 'Cathy Whitaker', 0, 9), ('Actress -- Leading Role', 'Renée Zellweger', 'Chicago', 'Roxie Hart', 0, 9), ('Actress -- Supporting Role', 'Kathy Bates', 'About Schmidt', 'Roberta Hertzel', 0, 9), ('Actress -- Supporting Role', 'Julianne Moore', 'The Hours', 'Laura Brown', 0, 9), ('Actress -- Supporting Role', 'Queen Latifah', 'Chicago', 'Matron Mama Morton', 0, 9), ('Actress -- Supporting Role', 'Meryl Streep', 'Adaptation', 'Susan Orlean', 0, 9), ('Actress -- Supporting Role', 'Catherine Zeta-Jones', 'Chicago', 'Velma Kelly', 1, 9), ('Actor -- Leading Role', 'Russell Crowe', 'A Beautiful Mind', 'John Nash', 0, 10), ('Actor -- Leading Role', 'Sean Penn', 'I Am Sam', 'Sam Dawson', 0, 10), ('Actor -- Leading Role', 'Will Smith', 'Ali', 'Muhammad Ali', 0, 10), ('Actor -- Leading Role', 'Denzel Washington', 'Training Day', 'Alonzo', 1, 10), ('Actor -- Leading Role', 'Tom Wilkinson', 'In the Bedroom', 'Matt Fowler', 0, 10), ('Actor -- Supporting Role', 'Jim Broadbent', 'Iris', 'John Bayley', 1, 10), ('Actor -- Supporting Role', 'Ethan Hawke', 'Training Day', 'Jake', 0, 10), ('Actor -- Supporting Role', 'Ben Kingsley', 'Sexy Beast', 'Don Logan', 0, 10), ('Actor -- Supporting Role', 'Ian McKellen', 'The Lord of the Rings: The Fellowship of the Ring', 'Gandalf', 0, 10), ('Actor -- Supporting Role', 'Jon Voight', 'Ali', 'Howard Cosell', 0, 10), ('Actress -- Leading Role', 'Halle Berry', "Monster's Ball", 'Leticia Musgrove', 1, 10), ('Actress -- Leading Role', 'Judi Dench', 'Iris', 'Iris Murdoch', 0, 10), ('Actress -- Leading Role', 'Nicole Kidman', 'Moulin Rouge', 'Satine', 0, 10), ('Actress -- Leading Role', 'Sissy Spacek', 'In the Bedroom', 'Ruth Fowler', 0, 10), ('Actress -- Leading Role', 'Renée Zellweger', "Bridget Jones's Diary", 'Bridget Jones', 0, 10), ('Actress -- Supporting Role', 'Jennifer Connelly', 'A Beautiful Mind', 'Alicia Nash', 1, 10), ('Actress -- Supporting Role', 'Helen Mirren', 'Gosford Park', 'Mrs. Wilson', 0, 10), ('Actress -- Supporting Role', 'Maggie Smith', 'Gosford Park', 'Constance, Countess of Trentham', 0, 10), ('Actress -- Supporting Role', 'Marisa Tomei', 'In the Bedroom', 'Natalie Strout', 0, 10), ('Actress -- Supporting Role', 'Kate Winslet', 'Iris', 'Young Iris Murdoch', 0, 10)]
In [84]:
q1 = '''
CREATE TABLE nominations2(
id integer PRIMARY KEY,
category text,
nominee text,
movie text,
character text,
won integer,
ceremony_id integer,
FOREIGN KEY(ceremony_id) REFERENCES ceremonies (id)
);'''
conn.execute(q1)
Out[84]:
<sqlite3.Cursor at 0x1127cd180>
In [86]:
insert_q2 = "INSERT INTO nominations2 (category, nominee, movie, character, won, ceremony_id) VALUES (?, ?, ?, ?, ?, ?)"
conn.executemany(insert_q2, nomination2)
Out[86]:
<sqlite3.Cursor at 0x1127abab0>
In [87]:
conn.execute("SELECT * FROM nominations2 LIMIT 5").fetchall()
Out[87]:
[(1, 'Actor -- Leading Role', 'Javier Bardem', 'Biutiful', 'Uxbal', 0, 1), (2, 'Actor -- Leading Role', 'Jeff Bridges', 'True Grit', 'Rooster Cogburn', 0, 1), (3, 'Actor -- Leading Role', 'Jesse Eisenberg', 'The Social Network', 'Mark Zuckerberg', 0, 1), (4, 'Actor -- Leading Role', 'Colin Firth', "The King's Speech", 'King George VI', 1, 1), (5, 'Actor -- Leading Role', 'James Franco', '127 Hours', 'Aron Ralston', 0, 1)]
In [88]:
q3 = "DROP TABLE nominations"
conn.execute(q3)
Out[88]:
<sqlite3.Cursor at 0x1127cd030>
In [90]:
q4 = "ALTER TABLE nominations2 RENAME TO nominations"
conn.execute(q4)
Out[90]:
<sqlite3.Cursor at 0x1127cd570>
In [91]:
#create table movies
create_q1 = '''
CREATE TABLE movies (
id integer PRIMARY KEY,
movie text);
'''
conn.execute(create_q1)
Out[91]:
<sqlite3.Cursor at 0x1127cd420>
In [92]:
#create table actors
create_q2 = '''
CREATE TABLE actors (
id integer PRIMARY KEY,
actor text);
'''
conn.execute(create_q2)
Out[92]:
<sqlite3.Cursor at 0x1127cd5e0>
In [94]:
#create table movie_actor
create_q3 = '''
CREATE TABLE movie_actor (
id integer PRIMARY KEY,
movie_id integer REFERENCES movies(id),
actor_id integer REFERENCES actors(id));
'''
conn.execute(create_q3)
Out[94]:
<sqlite3.Cursor at 0x1127cd7a0>
In [95]:
conn.close()