%load_ext idb
/Library/Python/2.7/site-packages/pytz/__init__.py:29: UserWarning: Module idb was already imported from /Users/dongweiming/.ipython/extensions/idb.py, but /Library/Python/2.7/site-packages/ipython_db-1.0-py2.7.egg is being added to sys.path from pkg_resources import resource_stream
%db_connect sqlite:///Users/dongweiming/baseball-archive-2012.sqlite
Indexing schema. This will take a second...finished! Refreshing schema. Please wait...done!
DB[sqlite][localhost]:None > None@None
%tables
Table | Columns |
---|---|
allstarfull | playerID, yearID, gameNum, gameID, teamID, lgID, GP, startingPos |
appearances | yearID, teamID, lgID, playerID, G_all, G_batting, G_defense, G_p, G_c, G_1b, G_2 b, G_3b, G_ss, G_lf, G_cf, G_rf, G_of, G_dh, G_ph, G_pr |
awardsmanagers | managerID, awardID, yearID, lgID, tie, notes |
awardsplayers | playerID, awardID, yearID, lgID, tie, notes |
awardssharemanagers | awardID, yearID, lgID, managerID, pointsWon, pointsMax, votesFirst |
awardsshareplayers | awardID, yearID, lgID, playerID, pointsWon, pointsMax, votesFirst |
battingpost | yearID, round, playerID, teamID, lgID, G, AB, R, H, 2B, 3B, HR, RBI, SB, CS, BB, SO, IBB, HBP, SH, SF, GIDP |
fielding | playerID, yearID, stint, teamID, lgID, POS, G, GS, InnOuts, PO, A, E, DP, PB, WP , SB, CS, ZR |
fieldingof | playerID, yearID, stint, Glf, Gcf, Grf |
fieldingpost | playerID, yearID, teamID, lgID, round, POS, G, GS, InnOuts, PO, A, E, DP, TP, PB , SB, CS |
halloffame | hofID, yearid, votedBy, ballots, needed, votes, inducted, category |
hofold | hofID, yearid, votedBy, ballots, votes, inducted, category |
managers | managerID, yearID, teamID, lgID, inseason, G, W, L, rank, plyrMgr |
managershalf | managerID, yearID, teamID, lgID, inseason, half, G, W, L, rank |
master | lahmanID, playerID, managerID, hofID, birthYear, birthMonth, birthDay, birthCoun try, birthState, birthCity, deathYear, deathMonth, deathDay, deathCountry, death State, deathCity, nameFirst, nameLast, nameNote, nameGiven, nameNick, weight, he ight, bats, throws, debut, finalGame, college, lahman40ID, lahman45ID, retroID, holtzID, bbrefID |
pitching | playerID, yearID, stint, teamID, lgID, W, L, G, GS, CG, SHO, SV, IPouts, H, ER, HR, BB, SO, BAOpp, ERA, IBB, WP, HBP, BK, BFP, GF, R, SH, SF, GIDP |
pitchingpost | playerID, yearID, round, teamID, lgID, W, L, G, GS, CG, SHO, SV, IPouts, H, ER, HR, BB, SO, BAOpp, ERA, IBB, WP, HBP, BK, BFP, GF, R, SH, SF, GIDP |
salaries | yearID, teamID, lgID, playerID, salary |
schools | schoolID, schoolName, schoolCity, schoolState, schoolNick |
schoolsplayers | playerID, schoolID, yearMin, yearMax |
seriespost | yearID, round, teamIDwinner, lgIDwinner, teamIDloser, lgIDloser, wins, losses, t ies |
teams | yearID, lgID, teamID, franchID, divID, Rank, G, Ghome, W, L, DivWin, WCWin, LgWi n, WSWin, R, AB, H, 2B, 3B, HR, BB, SO, SB, CS, HBP, SF, RA, ER, ERA, CG, SHO, S V, IPouts, HA, HRA, BBA, SOA, E, DP, FP, name, park, attendance, BPF, PPF, teamI DBR, teamIDlahman45, teamIDretro |
teamsfranchises | franchID, franchName, active, NAassoc |
teamshalf | yearID, lgID, teamID, Half, divID, DivWin, Rank, G, W, L |
tmp_batting | playerID, yearID, stint, teamID, lgID, G, G_batting, AB, R, H, 2B, 3B, HR, RBI, SB, CS, BB, SO, IBB, HBP, SH, SF, GIDP, G_old |
%tables allstarfull
Column | Type | Foreign Keys | Reference Keys |
---|---|---|---|
playerID | TEXT | ||
yearID | INTEGER | ||
gameNum | INTEGER | ||
gameID | TEXT | ||
teamID | TEXT | ||
lgID | TEXT | ||
GP | INTEGER | ||
startingPos | INTEGER |
%tables allstarfull playerID
Table | Name | Type |
---|---|---|
allstarfull | playerID | TEXT |
%tables allstarfull playerID head
0 aaronha01 1 aaronha01 2 aaronha01 3 aaronha01 4 aaronha01 5 aaronha01 Name: playerID, dtype: object
%tables allstarfull playerID unique count
1637
%find_column *player*
Table | Column Name | Type |
---|---|---|
allstarfull | playerID | TEXT |
appearances | playerID | TEXT |
awardsplayers | playerID | TEXT |
awardsshareplayers | playerID | TEXT |
battingpost | playerID | TEXT |
fielding | playerID | TEXT |
fieldingof | playerID | TEXT |
fieldingpost | playerID | TEXT |
master | playerID | TEXT |
pitching | playerID | TEXT |
pitchingpost | playerID | TEXT |
salaries | playerID | TEXT |
schoolsplayers | playerID | TEXT |
tmp_batting | playerID | TEXT |
%find_column HR INTEGER
Table | Column Name | Type |
---|---|---|
battingpost | HR | INTEGER |
pitching | HR | INTEGER |
pitchingpost | HR | INTEGER |
teams | HR | INTEGER |
tmp_batting | HR | INTEGER |
%find_table *batting*
Table | Columns |
---|---|
battingpost | yearID, round, playerID, teamID, lgID, G, AB, R, H, 2B, 3B, HR, RBI, SB, CS, BB, SO, IBB, HBP, SH, SF, GIDP |
tmp_batting | playerID, yearID, stint, teamID, lgID, G, G_batting, AB, R, H, 2B, 3B, HR, RBI, SB, CS, BB, SO, IBB, HBP, SH, SF, GIDP, G_old |
%save_credentials baseball
Save credentials [] successful!
%save_credentials
[ERROR]Please Specify credentials name
df1 = %query select * from allstarfull limit 1;
df1
playerID | yearID | gameNum | gameID | teamID | lgID | GP | startingPos | |
---|---|---|---|---|---|---|---|---|
0 | aaronha01 | 1955 | 0 | NLS195507120 | ML1 | NL | 1 | None |
1 rows × 8 columns
df = %query_from_file myscript.sql