notebook.community

Edit and run



In [1]:

    
%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



In [2]:

    
%db_connect sqlite:///Users/dongweiming/baseball-archive-2012.sqlite









    



Indexing schema. This will take a second...finished!
Refreshing schema. Please wait...done!






    Out[2]:





DB[sqlite][localhost]:None > None@None



In [3]:

    
%tables









    Out[3]:





    
        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



In [4]:

    
%tables allstarfull









    Out[4]:





    
        Column
        Type
        Foreign Keys
        Reference Keys
    
    
        playerID
        TEXT
        
        
    
    
        yearID
        INTEGER
        
        
    
    
        gameNum
        INTEGER
        
        
    
    
        gameID
        TEXT
        
        
    
    
        teamID
        TEXT
        
        
    
    
        lgID
        TEXT
        
        
    
    
        GP
        INTEGER
        
        
    
    
        startingPos
        INTEGER



In [5]:

    
%tables allstarfull playerID









    Out[5]:





    
        Table
        Name
        Type
    
    
        allstarfull
        playerID
        TEXT



In [6]:

    
%tables allstarfull playerID head









    Out[6]:





0    aaronha01
1    aaronha01
2    aaronha01
3    aaronha01
4    aaronha01
5    aaronha01
Name: playerID, dtype: object



In [7]:

    
%tables allstarfull playerID unique count









    Out[7]:





1637



In [8]:

    
%find_column *player*









    Out[8]:





    
        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



In [9]:

    
%find_column HR INTEGER









    Out[9]:





    
        Table
        Column Name
        Type
    
    
        battingpost
        HR
        INTEGER
    
    
        pitching
        HR
        INTEGER
    
    
        pitchingpost
        HR
        INTEGER
    
    
        teams
        HR
        INTEGER
    
    
        tmp_batting
        HR
        INTEGER



In [11]:

    
%find_table *batting*









    Out[11]:





    
        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



In [12]:

    
%save_credentials baseball









    



Save credentials [] successful!



In [13]:

    
%save_credentials









    



[ERROR]Please Specify credentials name



In [16]:

    
df1 = %query select * from allstarfull limit 1;



In [17]:

    
df1









    Out[17]:






  
    
      
      playerID
      yearID
      gameNum
      gameID
      teamID
      lgID
      GP
      startingPos
    
  
  
    
      0
       aaronha01
       1955
       0
       NLS195507120
       ML1
       NL
       1
       None
    
  

1 rows × 8 columns



In [ ]:

    
df = %query_from_file myscript.sql

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

Column	Type	Foreign Keys	Reference Keys
playerID	TEXT
yearID	INTEGER
gameNum	INTEGER
gameID	TEXT
teamID	TEXT
lgID	TEXT
GP	INTEGER
startingPos	INTEGER