#! /usr/bin/env python # vim: set fileencoding=utf-8 : # Copyright (C) 2010, Christopher S. Swingley # Licensed under the terms of the GNU General Public License, v3 usage = """Usage: cat 40man_names | %prog [options] Searches the bdb database trying to match first and last names with playerid. Returns a list of playerid's for each name """ import optparse, sys import psycopg2 parser = optparse.OptionParser(usage=usage) parser.add_option("-v", "--verbose", action="store_true", dest="verbose", default=True, help="be more verbose") parser.add_option("-q", "--quiet", action="store_false", dest="verbose", default=True, help="be quiet") (options, args) = parser.parse_args() # if options.verbose: # print "options =", options, "args =", args def unnone(x): if x: return int(x) else: return 0 connection = psycopg2.connect(host='HOSTNAME', database='DATABASE', user='USERNAME', port=5432) cursor = connection.cursor() player_data = {} for line in sys.stdin.readlines(): line = line.strip() (names) = line.split() namefirst = names[0] namelast = names[-1] query = """SELECT playerid, namefirst, namelast, birthyear FROM master WHERE namefirst ~ %s AND namelast=%s ORDER BY birthyear DESC LIMIT 1;""" params = (namefirst, namelast) cursor.execute(query, params) if cursor.rowcount: rows = cursor.fetchall() for row in rows: (playerid, namefirst, namelast, birthyear) = row player_data[playerid] = {} player_data[playerid]['namefirst'] = namefirst player_data[playerid]['namelast'] = namelast player_data[playerid]['birthyear'] = birthyear player_data[playerid]['age'] = 2010 - birthyear new_query = """SELECT sum(g), pos FROM fielding WHERE playerid=%s AND yearid IN (%s, %s-1) AND pos != 'OF' GROUP BY pos ORDER BY sum(g) DESC LIMIT 1;""" params = (playerid, 2009, 2009) cursor.execute(new_query, params) if cursor.rowcount: new_rows = cursor.fetchall()[0] (g, pos) = new_rows pos = pos.strip() player_data[playerid]['pos'] = pos else: player_data[playerid]['pos'] = "XX" if pos != 'P': new_query = """SELECT sum(g), sum(ab), sum(r), sum(h), sum("2b"), sum("3b"), sum(hr), sum(rbi), sum(sb), sum(cs), sum(bb), sum(so), sum(ibb), sum(hbp), sum(sh), sum(sf), sum(gidp) FROM batting WHERE playerid=%s AND yearid IN (%s, %s-1) GROUP BY playerid;""" params = (playerid, 2009, 2009) cursor.execute(new_query, params) if cursor.rowcount: new_rows = cursor.fetchall()[0] (g, ab, r, h, doubles, triples, hr, rbi, sb, cs, bb, so, ibb, hbp, sh, sf, gidp) = new_rows (g, ab, r, h, doubles, triples, hr, rbi, sb, cs, bb, so, ibb, hbp, sh, sf, gidp) = map(unnone, (g, ab, r, h, doubles, triples, hr, rbi, sb, cs, bb, so, ibb, hbp, sh, sf, gidp)) pa = ab + sb + bb + ibb + hbp + sh tb = h + doubles + (triples * 2) + (hr * 3) player_data[playerid]['g'] = g player_data[playerid]['ab'] = ab player_data[playerid]['r'] = r player_data[playerid]['h'] = h if ab > 0: player_data[playerid]['ba'] = h/float(ab) player_data[playerid]['slg'] = tb/float(ab) player_data[playerid]['rc'] = int((h+bb)*tb/(ab+bb+ibb)) else: player_data[playerid]['ba'] = 0.00 player_data[playerid]['slg'] = 0.00 player_data[playerid]['rc'] = 0 if pa > 0: player_data[playerid]['obp'] = (h + bb + ibb + hbp) / float(pa) player_data[playerid]['ops'] = player_data[playerid]['obp'] + player_data[playerid]['slg'] player_data[playerid]['kr'] = so / float(pa) player_data[playerid]['bbr'] = (bb + ibb) / float(pa) player_data[playerid]['hrr'] = hr / float(pa) else: player_data[playerid]['obp'] = 0.00 player_data[playerid]['ops'] = 0.00 player_data[playerid]['kr'] = 0.00 player_data[playerid]['bbr'] = 0.00 player_data[playerid]['hrr'] = 0.00 else: new_query = """SELECT sum(g), sum(w), sum(l), sum(gs), sum(cg), sum(sho), sum(sv), sum(ipouts), sum(h), sum(er), sum(hr), sum(bb), sum(so), sum(ibb), sum(wp), sum(hbp), sum(bk), sum(r) FROM pitching WHERE playerid=%s AND yearid IN (%s, %s-1) GROUP BY playerid;""" params = (playerid, 2009, 2009) cursor.execute(new_query, params) if cursor.rowcount: new_rows = cursor.fetchall()[0] (g, w, l, gs, cg, sho, sv, ipouts, h, er, hr, bb, so, ibb, wp, hbp, bk, r) = new_rows (g, w, l, gs, cg, sho, sv, ipouts, h, er, hr, bb, so, ibb, wp, hbp, bk, r) = map(unnone, (g, w, l, gs, cg, sho, sv, ipouts, h, er, hr, bb, so, ibb, wp, hbp, bk, r)) ip = ipouts / float(3) player_data[playerid]['g'] = g player_data[playerid]['w'] = w player_data[playerid]['ip'] = ip player_data[playerid]['l'] = l player_data[playerid]['sv'] = sv if g > 0: player_data[playerid]['wp'] = w/float(g) player_data[playerid]['lp'] = l/float(g) player_data[playerid]['wf'] = (w-l)/float(g) else: player_data[playerid]['wp'] = 0.00 player_data[playerid]['lp'] = 0.00 player_data[playerid]['wf'] = 0.00 if ip > 0: player_data[playerid]['era'] = er/float(ip)*9 player_data[playerid]['k9'] = so/float(ip)*9 player_data[playerid]['bb9'] = (bb+ibb)/float(ip)*9 player_data[playerid]['hr9'] = hr/float(ip)*9 else: player_data[playerid]['era'] = 0.00 player_data[playerid]['k9'] = 0.00 player_data[playerid]['bb9'] = 0.00 player_data[playerid]['hr9'] = 0.00 cursor.close() connection.close() playerids = player_data.keys() playerids.sort() batting = open('batting.csv', 'w') batting.write("%-25s, %3s,%3s, %5s, %5s, %5s, %5s, %3s, %5s, %5s, %5s\n" % ("name", "age", "g", "ba", "obp", "slg", "ops", "rc", "hrr", "kr", "bbr")) for playerid in playerids: if player_data[playerid]['pos'] != 'P': batting.write("%-25s, %3s, %3d, %0.3f, %0.3f, %0.3f, %4.3f, %3d, %0.3f, %0.3f, %0.3f\n" % ( player_data[playerid]['namefirst'] + " " + player_data[playerid]['namelast'] + " (" + player_data[playerid]['pos'].strip() + ")", player_data[playerid]['age'], player_data[playerid]['g'], player_data[playerid]['ba'], player_data[playerid]['obp'], player_data[playerid]['slg'], player_data[playerid]['ops'], player_data[playerid]['rc'], player_data[playerid]['hrr'], player_data[playerid]['kr'], player_data[playerid]['bbr'])) batting.close() pitching = open('pitching.csv', 'w') pitching.write("%-25s, %3s,%3s,%5s ,%3s,%3s,%3s, %5s, %5s, %5s, %5s, %5s, %5s, %5s\n" % ("name", "age", "g", "ip", "w", "l", "sv", "wp", "lp", "wf", "era", "k9", "bb9", "hr9")) for playerid in playerids: if player_data[playerid]['pos'] == 'P': pitching.write("%-25s, %3s, %3d, %5.2f, %3d, %3d, %3d, %5.2f, %5.2f, %5.2f, %5.2f, %5.2f, %5.2f, %5.2f\n" % ( player_data[playerid]['namefirst'] + " " + player_data[playerid]['namelast'] + " (" + player_data[playerid]['pos'].strip() + ")", player_data[playerid]['age'], player_data[playerid]['g'], player_data[playerid]['ip'], player_data[playerid]['w'], player_data[playerid]['l'], player_data[playerid]['sv'], player_data[playerid]['wp'], player_data[playerid]['lp'], player_data[playerid]['wf'], player_data[playerid]['era'], player_data[playerid]['k9'], player_data[playerid]['bb9'], player_data[playerid]['hr9'])) pitching.close()