Hideo Nomo Has a Posse

from py_mlb import player, team, league
l = league.League()
team = l.teams['sea']
team.fetchRoster()
 
for player_id, player in team.roster.iteritems():
    print player.name_full

DROP TABLE IF EXISTS `game`;
CREATE TABLE `game` (
	id varchar(30) DEFAULT NULL,
	home varchar(30) DEFAULT NULL,
	away varchar(30) DEFAULT NULL,
	date date DEFAULT NULL
) ENGINE=InnoDB;
 
DROP TABLE IF EXISTS `hitchart`;
CREATE TABLE `hitchart` (
	game varchar(30) DEFAULT NULL,
	des varchar(25) DEFAULT NULL,
	x decimal(6,2) DEFAULT NULL,
	y decimal(6,2) DEFAULT NULL,
	batter int(6) DEFAULT NULL,
	pitcher int(6) DEFAULT NULL,
	type enum('O','H','E') DEFAULT NULL,
	team enum('A', 'H') DEFAULT NULL,
	inning tinyint(1) DEFAULT NULL
) ENGINE=InnoDB;
 
DROP TABLE IF EXISTS `player`;
CREATE TABLE `player`(
	team varchar(3) DEFAULT NULL,
	id int(6) DEFAULT NULL,
	pos varchar(2) DEFAULT NULL,
	type varchar(10) DEFAULT NULL,
	first_name varchar(25) DEFAULT NULL,
	last_name varchar(25) DEFAULT NULL,
	jersey_number varchar(10) DEFAULT NULL,
	height varchar(5) DEFAULT NULL,
	weight varchar(3) DEFAULT NULL,
	bats varchar(10) DEFAULT NULL,
	throws varchar(10) DEFAULT NULL,
	dob varchar(15) DEFAULT NULL
) ENGINE=InnoDB;
 
DROP TABLE IF EXISTS `atbat`;
CREATE TABLE `atbat` (
	game varchar(30) DEFAULT NULL,
	inning int DEFAULT NULL,
	num int UNSIGNED DEFAULT NULL,
	b int DEFAULT NULL,
	s int DEFAULT NULL,
	o int DEFAULT NULL,
	batter int DEFAULT NULL,
	stand enum('R','L'),
	b_height varchar(5),
	pitcher int DEFAULT NULL,
	p_throws enum('R', 'L'),
	des varchar(1000) DEFAULT NULL,
	event varchar(100) DEFAULT NULL,
	brief_event varchar(100) DEFAULT NULL,
	score varchar(1) DEFAULT NULL,
	home_team_runs tinyint DEFAULT NULL,
	away_team_runs tinyint DEFAULT NULL
) ENGINE=InnoDB;
 
DROP TABLE IF EXISTS `pitch`;
CREATE TABLE `pitch` (
	game varchar(30) DEFAULT NULL,
	id int DEFAULT NULL,
	atbat int UNSIGNED NOT NULL,
	des varchar(50) DEFAULT NULL,
	type enum('S', 'B', 'X') DEFAULT NULL,
	x float DEFAULT NULL,
	y float DEFAULT NULL,
	sv_id varchar(25) DEFAULT NULL,
	start_speed float DEFAULT NULL,
	end_speed float DEFAULT NULL,
	sz_top float DEFAULT NULL,
	sz_bot float DEFAULT NULL,
	pfx_x float DEFAULT NULL,
	pfx_z float DEFAULT NULL,
	px float DEFAULT NULL,
	pz float DEFAULT NULL,
	x0 float DEFAULT NULL,
	y0 float DEFAULT NULL,
	z0 float DEFAULT NULL,
	vx0 float DEFAULT NULL,
	vy0 float DEFAULT NULL,
	vz0 float DEFAULT NULL,
	ax float DEFAULT NULL,
	ay float DEFAULT NULL,
	az float DEFAULT NULL,
	break_y float DEFAULT NULL,
	break_angle float DEFAULT NULL,
	break_length float DEFAULT NULL,
	pitch_type varchar(5) DEFAULT NULL,
	type_confidence float DEFAULT NULL,
	spin_dir float DEFAULT NULL,
	spin_rate float DEFAULT NULL,
	on_1b int(6) DEFAULT NULL,
	on_2b int(6) DEFAULT NULL,
	on_3b int(6) DEFAULT NULL,
	score tinyint DEFAULT NULL
) ENGINE=InnoDB;
 
CREATE INDEX player_id ON player(id);
CREATE INDEX game_id ON game(id);
CREATE INDEX game_id ON atbat(game);
CREATE INDEX game_id ON pitch(game);
CREATE INDEX game_id ON hitchart(game);

#!/usr/bin/env python
from xml.dom.minidom import parse
import glob
import MySQLdb
import sys, getopt
import os
 
YEAR = None
 
try:
	opts, args = getopt.getopt(sys.argv[1:], "y:")
except getopt.GetoptError, e:
	raise SystemExit
 
for opt, arg in opts:
	if opt == "-y":
		YEAR = int(arg)
 
if YEAR is None:
	print "please run like parse.py -y 2009"
	raise SystemExit
 
def nodeToTable(table, node, extra = {}):
	values = []
	keys = node.attributes.keys()
 
	for val in node.attributes.values():
		values.append(val.value)
 
	for key in extra.keys():
		keys.insert(0, key)
 
	for value in extra.values():
		values.insert(0, value)
 
	query = "INSERT INTO %s (%s) VALUES(%s)" %(table, ','.join(keys),','.join(['%s'] * len(values)))
	cursor.execute(query, values)
 
db = MySQLdb.connect(host="localhost", read_default_file="/home/wells/.my.cnf", db="pbp")
cursor = db.cursor()
 
for month in glob.glob("./data/year_%d/*" % YEAR):
	for day in glob.glob("%s/*" % month):
		for game in glob.glob("%s/*" % day):
			gid = os.path.basename(game)
 
			cursor.execute("SELECT * FROM game WHERE id = '%s'" % gid)
			if cursor.rowcount == 0:
				print "adding game %s" % gid
				xml = parse("%s/boxscore.xml" % game)
				box = xml.getElementsByTagName("boxscore").item(0)
				home = box.attributes["home_fname"].value
				away = box.attributes["away_fname"].value
				date = box.attributes["date"].value
				cursor.execute("INSERT INTO game (id, home, away, date) VALUES ('%s', '%s', '%s', STR_TO_DATE('%s', '%%M %%d, %%Y'))" % (gid, home, away, date))
 
				xml = parse("%s/inning/inning_hit.xml" % game)
				for hip in xml.getElementsByTagName("hip"):
					nodeToTable("hitchart", hip, {'game': gid})
 
				for ifile in glob.glob("%s/inning/*.xml" % game):
					xml = parse(ifile)
					for inning in xml.getElementsByTagName("inning"):
						for atbat in xml.getElementsByTagName("atbat"):
							nodeToTable('atbat', atbat, {'game': gid, 'inning': inning.getAttribute("num")})
							for pitch in atbat.getElementsByTagName("pitch"):
								nodeToTable('pitch', pitch, {'atbat': atbat.getAttribute("num"), 'game': gid})
 
			for batter in glob.glob("%s/batters/*.xml" % game):
				xml = parse(batter)
				for player in xml.getElementsByTagName("Player"):
					cursor.execute("SELECT id FROM player WHERE id = %d" % int(player.attributes["id"].value))
					if cursor.rowcount == 0:
						nodeToTable("player", player)
 
			for pitcher in glob.glob("%s/pitchers/*.xml" % game):
				xml = parse(pitcher)
				for player in xml.getElementsByTagName("Player"):
					cursor.execute("SELECT id FROM player WHERE id = %d" % int(player.attributes["id"].value))
					if cursor.rowcount == 0:
						nodeToTable("player", player)
 
db.commit()
cursor.close()
db.close()