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]:

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