{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": false }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/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\n", " from pkg_resources import resource_stream\n" ] } ], "source": [ "%load_ext idb" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": false }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "Indexing schema. This will take a second...finished!\n", "Refreshing schema. Please wait...done!\n" ] }, { "data": { "text/plain": [ "DB[sqlite][localhost]:None > None@None" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%db_connect sqlite:///Users/dongweiming/baseball-archive-2012.sqlite" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TableColumns
allstarfullplayerID, yearID, gameNum, gameID, teamID, lgID, GP, startingPos
appearancesyearID, 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
awardsmanagersmanagerID, awardID, yearID, lgID, tie, notes
awardsplayersplayerID, awardID, yearID, lgID, tie, notes
awardssharemanagersawardID, yearID, lgID, managerID, pointsWon, pointsMax, votesFirst
awardsshareplayersawardID, yearID, lgID, playerID, pointsWon, pointsMax, votesFirst
battingpostyearID, round, playerID, teamID, lgID, G, AB, R, H, 2B, 3B, HR, RBI, SB, CS, BB,
SO, IBB, HBP, SH, SF, GIDP
fieldingplayerID, yearID, stint, teamID, lgID, POS, G, GS, InnOuts, PO, A, E, DP, PB, WP
, SB, CS, ZR
fieldingofplayerID, yearID, stint, Glf, Gcf, Grf
fieldingpostplayerID, yearID, teamID, lgID, round, POS, G, GS, InnOuts, PO, A, E, DP, TP, PB
, SB, CS
halloffamehofID, yearid, votedBy, ballots, needed, votes, inducted, category
hofoldhofID, yearid, votedBy, ballots, votes, inducted, category
managersmanagerID, yearID, teamID, lgID, inseason, G, W, L, rank, plyrMgr
managershalfmanagerID, yearID, teamID, lgID, inseason, half, G, W, L, rank
masterlahmanID, 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
pitchingplayerID, 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
pitchingpostplayerID, 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
salariesyearID, teamID, lgID, playerID, salary
schoolsschoolID, schoolName, schoolCity, schoolState, schoolNick
schoolsplayersplayerID, schoolID, yearMin, yearMax
seriespostyearID, round, teamIDwinner, lgIDwinner, teamIDloser, lgIDloser, wins, losses, t
ies
teamsyearID, 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
teamsfranchisesfranchID, franchName, active, NAassoc
teamshalfyearID, lgID, teamID, Half, divID, DivWin, Rank, G, W, L
tmp_battingplayerID, 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
" ], "text/plain": [ "+---------------------+----------------------------------------------------------------------------------+\n", "| Table | Columns |\n", "+---------------------+----------------------------------------------------------------------------------+\n", "| allstarfull | playerID, yearID, gameNum, gameID, teamID, lgID, GP, startingPos |\n", "| appearances | yearID, teamID, lgID, playerID, G_all, G_batting, G_defense, G_p, G_c, G_1b, G_2 |\n", "| | b, G_3b, G_ss, G_lf, G_cf, G_rf, G_of, G_dh, G_ph, G_pr |\n", "| awardsmanagers | managerID, awardID, yearID, lgID, tie, notes |\n", "| awardsplayers | playerID, awardID, yearID, lgID, tie, notes |\n", "| awardssharemanagers | awardID, yearID, lgID, managerID, pointsWon, pointsMax, votesFirst |\n", "| awardsshareplayers | awardID, yearID, lgID, playerID, pointsWon, pointsMax, votesFirst |\n", "| battingpost | yearID, round, playerID, teamID, lgID, G, AB, R, H, 2B, 3B, HR, RBI, SB, CS, BB, |\n", "| | SO, IBB, HBP, SH, SF, GIDP |\n", "| fielding | playerID, yearID, stint, teamID, lgID, POS, G, GS, InnOuts, PO, A, E, DP, PB, WP |\n", "| | , SB, CS, ZR |\n", "| fieldingof | playerID, yearID, stint, Glf, Gcf, Grf |\n", "| fieldingpost | playerID, yearID, teamID, lgID, round, POS, G, GS, InnOuts, PO, A, E, DP, TP, PB |\n", "| | , SB, CS |\n", "| halloffame | hofID, yearid, votedBy, ballots, needed, votes, inducted, category |\n", "| hofold | hofID, yearid, votedBy, ballots, votes, inducted, category |\n", "| managers | managerID, yearID, teamID, lgID, inseason, G, W, L, rank, plyrMgr |\n", "| managershalf | managerID, yearID, teamID, lgID, inseason, half, G, W, L, rank |\n", "| master | lahmanID, playerID, managerID, hofID, birthYear, birthMonth, birthDay, birthCoun |\n", "| | try, birthState, birthCity, deathYear, deathMonth, deathDay, deathCountry, death |\n", "| | State, deathCity, nameFirst, nameLast, nameNote, nameGiven, nameNick, weight, he |\n", "| | ight, bats, throws, debut, finalGame, college, lahman40ID, lahman45ID, retroID, |\n", "| | holtzID, bbrefID |\n", "| pitching | playerID, yearID, stint, teamID, lgID, W, L, G, GS, CG, SHO, SV, IPouts, H, ER, |\n", "| | HR, BB, SO, BAOpp, ERA, IBB, WP, HBP, BK, BFP, GF, R, SH, SF, GIDP |\n", "| pitchingpost | playerID, yearID, round, teamID, lgID, W, L, G, GS, CG, SHO, SV, IPouts, H, ER, |\n", "| | HR, BB, SO, BAOpp, ERA, IBB, WP, HBP, BK, BFP, GF, R, SH, SF, GIDP |\n", "| salaries | yearID, teamID, lgID, playerID, salary |\n", "| schools | schoolID, schoolName, schoolCity, schoolState, schoolNick |\n", "| schoolsplayers | playerID, schoolID, yearMin, yearMax |\n", "| seriespost | yearID, round, teamIDwinner, lgIDwinner, teamIDloser, lgIDloser, wins, losses, t |\n", "| | ies |\n", "| teams | yearID, lgID, teamID, franchID, divID, Rank, G, Ghome, W, L, DivWin, WCWin, LgWi |\n", "| | n, WSWin, R, AB, H, 2B, 3B, HR, BB, SO, SB, CS, HBP, SF, RA, ER, ERA, CG, SHO, S |\n", "| | V, IPouts, HA, HRA, BBA, SOA, E, DP, FP, name, park, attendance, BPF, PPF, teamI |\n", "| | DBR, teamIDlahman45, teamIDretro |\n", "| teamsfranchises | franchID, franchName, active, NAassoc |\n", "| teamshalf | yearID, lgID, teamID, Half, divID, DivWin, Rank, G, W, L |\n", "| tmp_batting | playerID, yearID, stint, teamID, lgID, G, G_batting, AB, R, H, 2B, 3B, HR, RBI, |\n", "| | SB, CS, BB, SO, IBB, HBP, SH, SF, GIDP, G_old |\n", "+---------------------+----------------------------------------------------------------------------------+" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%tables" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ColumnTypeForeign KeysReference Keys
playerIDTEXT
yearIDINTEGER
gameNumINTEGER
gameIDTEXT
teamIDTEXT
lgIDTEXT
GPINTEGER
startingPosINTEGER
" ], "text/plain": [ "+-------------------------------------------------------+\n", "| allstarfull |\n", "+-------------+---------+--------------+----------------+\n", "| Column | Type | Foreign Keys | Reference Keys |\n", "+-------------+---------+--------------+----------------+\n", "| playerID | TEXT | | |\n", "| yearID | INTEGER | | |\n", "| gameNum | INTEGER | | |\n", "| gameID | TEXT | | |\n", "| teamID | TEXT | | |\n", "| lgID | TEXT | | |\n", "| GP | INTEGER | | |\n", "| startingPos | INTEGER | | |\n", "+-------------+---------+--------------+----------------+" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%tables allstarfull" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TableNameType
allstarfullplayerIDTEXT
" ], "text/plain": [ "+-------------+----------+------+--------------+----------------+\n", "| Table | Name | Type | Foreign Keys | Reference Keys |\n", "+-------------+----------+------+--------------+----------------+\n", "| allstarfull | playerID | TEXT | | |\n", "+-------------+----------+------+--------------+----------------+" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%tables allstarfull playerID" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "0 aaronha01\n", "1 aaronha01\n", "2 aaronha01\n", "3 aaronha01\n", "4 aaronha01\n", "5 aaronha01\n", "Name: playerID, dtype: object" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%tables allstarfull playerID head" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "1637" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%tables allstarfull playerID unique count" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TableColumn NameType
allstarfullplayerIDTEXT
appearancesplayerIDTEXT
awardsplayersplayerIDTEXT
awardsshareplayersplayerIDTEXT
battingpostplayerIDTEXT
fieldingplayerIDTEXT
fieldingofplayerIDTEXT
fieldingpostplayerIDTEXT
masterplayerIDTEXT
pitchingplayerIDTEXT
pitchingpostplayerIDTEXT
salariesplayerIDTEXT
schoolsplayersplayerIDTEXT
tmp_battingplayerIDTEXT
" ], "text/plain": [ "+--------------------+-------------+------+\n", "| Table | Column Name | Type |\n", "+--------------------+-------------+------+\n", "| allstarfull | playerID | TEXT |\n", "| appearances | playerID | TEXT |\n", "| awardsplayers | playerID | TEXT |\n", "| awardsshareplayers | playerID | TEXT |\n", "| battingpost | playerID | TEXT |\n", "| fielding | playerID | TEXT |\n", "| fieldingof | playerID | TEXT |\n", "| fieldingpost | playerID | TEXT |\n", "| master | playerID | TEXT |\n", "| pitching | playerID | TEXT |\n", "| pitchingpost | playerID | TEXT |\n", "| salaries | playerID | TEXT |\n", "| schoolsplayers | playerID | TEXT |\n", "| tmp_batting | playerID | TEXT |\n", "+--------------------+-------------+------+" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%find_column *player*" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TableColumn NameType
battingpostHRINTEGER
pitchingHRINTEGER
pitchingpostHRINTEGER
teamsHRINTEGER
tmp_battingHRINTEGER
" ], "text/plain": [ "+--------------+-------------+---------+\n", "| Table | Column Name | Type |\n", "+--------------+-------------+---------+\n", "| battingpost | HR | INTEGER |\n", "| pitching | HR | INTEGER |\n", "| pitchingpost | HR | INTEGER |\n", "| teams | HR | INTEGER |\n", "| tmp_batting | HR | INTEGER |\n", "+--------------+-------------+---------+" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%find_column HR INTEGER" ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TableColumns
battingpostyearID, round, playerID, teamID, lgID, G, AB, R, H, 2B, 3B, HR, RBI, SB, CS, BB,
SO, IBB, HBP, SH, SF, GIDP
tmp_battingplayerID, 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
" ], "text/plain": [ "+-------------+----------------------------------------------------------------------------------+\n", "| Table | Columns |\n", "+-------------+----------------------------------------------------------------------------------+\n", "| battingpost | yearID, round, playerID, teamID, lgID, G, AB, R, H, 2B, 3B, HR, RBI, SB, CS, BB, |\n", "| | SO, IBB, HBP, SH, SF, GIDP |\n", "| tmp_batting | playerID, yearID, stint, teamID, lgID, G, G_batting, AB, R, H, 2B, 3B, HR, RBI, |\n", "| | SB, CS, BB, SO, IBB, HBP, SH, SF, GIDP, G_old |\n", "+-------------+----------------------------------------------------------------------------------+" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%find_table *batting*" ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Save credentials [] successful!\n" ] } ], "source": [ "%save_credentials baseball" ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "[ERROR]Please Specify credentials name\n" ] } ], "source": [ "%save_credentials " ] }, { "cell_type": "code", "execution_count": 16, "metadata": { "collapsed": false }, "outputs": [], "source": [ "df1 = %query select * from allstarfull limit 1;" ] }, { "cell_type": "code", "execution_count": 17, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
playerIDyearIDgameNumgameIDteamIDlgIDGPstartingPos
0 aaronha01 1955 0 NLS195507120 ML1 NL 1 None
\n", "

1 rows \u00d7 8 columns

\n", "
" ], "text/plain": [ " playerID yearID gameNum gameID teamID lgID GP startingPos\n", "0 aaronha01 1955 0 NLS195507120 ML1 NL 1 None\n", "\n", "[1 rows x 8 columns]" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df1" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [ "df = %query_from_file myscript.sql" ] } ], "metadata": { "kernelspec": { "display_name": "IPython (Python 2)", "name": "python2" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 2 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython2", "version": "2.7.5" }, "signature": "sha256:b9d6c79cc4882d426ebcd2a2218a90d6ffce840a24292d6630cc7dcff6c920f2" }, "nbformat": 4, "nbformat_minor": 0 }