Alright, I went to
www.armchairanalysis.com and downloaded the play-by-play data that includes a list of all games (with weather, scores, etc.) and a player roster for the 2008-2010 season (I think it's new for them). The downloads come in three files:
- GameIndex.csv
- PBPData.csv
- Rosters.csv
- PDF explaining the columns of each file
The goal will be to write
Python code to create tables and insert data into
MySQL. For python I will have to also utilize the
MySQL Python Package. First I need to setup my SQL database. I use a Mac, after installing MySQL and starting the server, I opened up a command prompt and did the following:
> mysql -u root
mysql> CREATE USER 'foo'@'localhost' IDENTIFIED BY 'foo';
mysql> SET PASSWORD FOR 'foo'@'localhost' = PASSWORD('som_pass')
mysql> GRANT ALL ON *.* TO 'user'@'localhost' IDENTIFIED BY 'user';
mysql> CREATE DATABASE pbp;
Once I setup the database I wrote code (see
here to format your code in a blog) to create tables and insert CSV data into MySQL. Any comments on how to improve this code would be helpful. I did not provide all the code for the column names (inserted ... where I deleted code) for brevity. I got the column names by copying what was in the PDF document explaining the columns. If anyone knows of a good way to automatically does this based on the first line of the CSV file, let me know.
import MySQLdb as mdb
import sys
class ArmChairSQL:
def createGameIndexTable(self,conn):
try:
cursor = conn.cursor()
cursor.execute("DROP TABLE IF EXISTS game_index;")
cursor.execute("CREATE TABLE IF NOT EXISTS \
game_index(GAME_ID INT(10),\
SEAS INT(4),\
WK INT(2),\
...,\
PFN INT(3))")
conn.commit()
cursor.close()
except mdb.Error, e:
print e
sys.exit(1)
def createPlayByPlayTable(self,conn):
try:
cursor = conn.cursor()
cursor.execute("DROP TABLE IF EXISTS play_by_play;")
cursor.execute("CREATE TABLE IF NOT EXISTS \
play_by_play(GAME_ID INT(3),\
PLAY_ID INT(3),\
DETAIL VARCHAR(150),\
OFF VARCHAR(3),\
DEF VARCHAR(3),\
...,\
BLK_NAM VARCHAR(20))")
conn.commit()
cursor.close()
except mdb.Error, e:
print e
sys.exit(1)
def createPlayerRosterTable(self,conn):
try:
cursor = conn.cursor()
cursor.execute("DROP TABLE IF EXISTS player_roster;")
cursor.execute("CREATE TABLE IF NOT EXISTS \
player_roster(GAME_ID INT(3),\
SEAS INT(4),\
WEEK INT(4),\
DAY CHAR(3),\
TEAM VARCHAR(4),\
POS VARCHAR(4),\
...,\
RATING INT(3))")
conn.commit()
cursor.close()
except mdb.Error, e:
print e
sys.exit(1)
def loadGameIndexData(self,conn,data):
sql = "LOAD DATA LOCAL INFILE '%s' INTO TABLE game_index \
FIELDS TERMINATED BY ',' \
OPTIONALLY ENCLOSED BY '\"' \
LINES TERMINATED BY '\\n' IGNORE 1 LINES;" % data
try:
cursor = conn.cursor()
cursor.execute(sql)
conn.commit()
except mdb.Error, e:
print mdb.Error
sys.exit(1)
def loadPlayByPlayData(self,conn,data):
sql = "LOAD DATA LOCAL INFILE '%s' INTO TABLE play_by_play \
FIELDS TERMINATED BY ',' \
OPTIONALLY ENCLOSED BY '\"' \
LINES TERMINATED BY '\\n' IGNORE 1 LINES;" % data
try:
cursor = conn.cursor()
cursor.execute(sql)
conn.commit()
except mdb.Error, e:
print e
sys.exit(1)
def loadPlayerRosterData(self,conn,data):
sql = "LOAD DATA LOCAL INFILE '%s' INTO TABLE player_roster \
FIELDS TERMINATED BY ',' \
OPTIONALLY ENCLOSED BY '\"' \
LINES TERMINATED BY '\\n' IGNORE 1 LINES;" % data
try:
cursor = conn.cursor()
cursor.execute(sql)
conn.commit()
except mdb.Error, e:
print e
sys.exit(1)
if __name__ == '__main__':
conn = mdb.connect(host='localhost',user='foo',passwd='', db='pbp');
acsql = ArmChairSQL()
print 'Creating and Loading Game Index Data'
acsql.createGameIndexTable(conn)
acsql.loadGameIndexData(conn,'GameIndex.csv')
print 'Creating and Loading Play-by-Play Data'
acsql.createPlayByPlayTable(conn)
acsql.loadPlayByPlayData(conn,'PBPData.csv')
print 'Creating and Loading Roster Data'
acsql.createPlayerRosterTable(conn)
acsql.loadPlayerRosterData(conn,'Rosters.csv')
conn.close()
At the command prompt I execute the file by:
> python LoadArmChairSQL.py
Unfortunately I get the following warnings (I show one for example):
> LoadArmChairSQL.py:330: Warning: Incorrect integer value: '' for column 'TEMP' at row 42
I know it has to do with empty data in the CSV (just commas next to each other) while I'm expecting an integer. Searches on the internet did not provide a simple answer to this, I'm sure there is an approach that does not involve editing the CSV file.
Below is an example of how to see if the data is loaded into the database. The SELECT command extracts all Players Names and Defenses where an interception happened during the regular season in 2010. Phew... I had to search on the internet to figure that out. One thing I want to figure out is how to link the databases since they all use "GAME ID".
> mysql -u foo
mysql> USE pbp;
mysql> SHOW TABLES;
mysql> DESCRIBE game_index;
mysql> SELECT play_by_play.PSR_NAM,play_by_play.DEF FROM play_by_play,game_index WHERE play_by_play.GAME_ID=game_index.GAME_ID AND game_index.SEAS=2010 AND play_by_play.INT_NAM<>'' AND game_index.WK<=17;
