Thursday, April 7, 2011

How to Put Retrosheet on Your Windows Computer...For Dummies

I have never taken a database class and have no idea what I'm doing as far as programming goes. So naturally, I decided to figure out how to put retrosheet on my computer and learn how to manipulate it. What could go wrong? These are the steps I would have taken If I knew what to do in the first place. If they don't work for you, I probably can't help. These steps are for Windows. I will hopefully do one for Mac as well, although the "Saberizing a Mac" series at Beyond the Boxscore has a lot of help there.

Download and Install MySQL
Go here and click download next to Windows (x86, 32-bit), MSI Installer.
Click  "No thanks, just take me to the downloads!"
Click one of the HTTP links next to the city names. "To make this download faster, please choose a mirror site close to you from the list below."
Click Save File.
When the file finished downloading, open it and click run.
Once the Setup Wizard opens, click Next.
Click I accept and Next
Click Typical
Click Install

Once the Install is finished, another popup will come up. Click Next a few times to get rid of it. Then click finish, making sure the "Launch the MySQL Instance Configuration Wizard" box is checked.

When the Configuration Wizard comes up, click Next through Detailed Configuration, Developer Machine, Multifunctional Database, C: Installation Path, Decision Support (DSS)/OLAP, Enable TCP/IP Networking, Port 3306, Enable Strict Mode, Standard Character Set, Install As Windows Service. You can choose a password if you like.

Then click execute.
Click Finish.

Congratulations, MySQL is installed! But you're not done.

Download and Install SQLyog
Go here:
Click the green arrow next to the file. I downloaded SQLyog-8.8.2-0Community.exe since the 9.0 edition is still in testing mode.
Click Save File
Once the file downloads, open it and click Run.
Click Next, I accept, Next, Next, Install

Once the Install is finished, click Continue...
The Connect to MySQL Host pops up.
Click New...
Name your new connection whatever you please (I chose "New Connection")
If you put in a password when you installed MySQL, put that in the Password box.
Click Connect.

Okay! Now you just need the data.

Download Retrosheet data.
Go here.
Click the "Click Here to download the Full Retrosheet database" link.
Click Open with WinZip and OK.
This file is 453 MB, so you may have to wait a little bit.
Once the download is complete, WinZip should open up with a file retrosheet.sql. Click Extract and put it wherever you want on your computer (just remember where). Then click Extract again. Now you should have the file on your computer and you just need to add it to SQLyog.

Add Retrosheet to SQLyog
Right click on root@localhost in the left bar and click Create Database
Name Database Retrosheet.
Click Create.
Right click the newly created retrosheet database, go to Import, Restore from SQL dump.
Find retrosheet.sql file by clicking on the "..." and looking for the folder where you extracted the Retrosheet.sql file. It will be under home\www\\baseball folders there.
Click Execute and then click Yes. Now go get some food or go outside or something. This may take a while.

Once the database is fully executed, click Done and you're finished! Now you just need to figure out how to write queries and manipulate the database. If you click on the + next to Tables on the left hand side of the screen under Retrosheet and click on events, you can click on the "2 Table Data" tab on the bottom part of your screen. This is where all of the play by play data is. I'll put up another post to describe what is in here in the future. Here are some useful queries.

Another good database to have is the Lahman Baseball Databank. You can go here and donwload the most current SQL file. Right now, this is Follow the same procedure as with Retrosheet to add this database to SQLyog. I have heard that this database will no longer be updated, so you may need to look somewhere else for it beyond the beginning of the 2011 season.

There is also a PitchF/X database, but I don't know much about it. I tend to go to sites like Brooks Baseball, Texas Leaguers and Fangraphs for that information.

Hopefully this helps someone out there who would love to look at this stuff but doesn't even know how to get started. That's how I felt initially and that is the purpose of this post.

1 comment:

  1. awesome blog! I'm new to mysql. Is the retrosheet data actually on my computer?