Code Monkey Monday- Transferring an Access Database to MySQL

Why are you using Access? It’s a pain in the butt and I don’t like it. Transfer your database to MySQL for easier compatibility with… everything.

I had to make this transition a few weeks ago, and here’s what I did. Remember that I am not a database expert, but this worked for me.
1. Download MySQL on your computer. Click on the Enterprise Edition link here. Oracle apparently controls MySQL nowadays, so sign in or register with them to get to the download. You can insert fake data in every data field for the registration except the email address. Use a real email address so you can confirm your registration. Once you get to the download page in Oracle, select MySQL and your computer type (i.e. Windows 64 bit). Input that query, then download the “MySQL Installer 5.6.20.0 Package” (or whatever version they’re currently on).
2. Run the installer .msi. Install the “Developer Default” setting unless you know any better. When you start installing, MySQL will probably tell you that you’re missing some pre-requisite programs. Just install them through MySQL like it wants you to. Install the program. After the install, there will be some configuration. On the “MySQL Server Configuration” page, I’d leave the defaults (Port=3306). On the next page, you’ll have to specify a password. Remember that password for future use. I’d leave the defaults on the other pages as well, unless you want to uncheck the box for starting MySQL Server at computer startup.
3. Download the “Access to MySQL” tool from here. The download link is at the top of the page.
4. Install the Access to MySQL program.
5. If your computer is like mine, when you try to run the Access to MySQL program, a box will pop up and complain about drivers. It will say you can only create dump files instead of doing the transition in one step. Whatever, just continue.
6. Click “Next” on the first page. In the Filename field of the second page, input the path to the .mdb file of your Access database. If you have security settings (username/password), enter those. On the third page, create a dump file (probably your only option). Type the name that you want the MySQL database to have and the path that you want the dump file saved at. Click “Next” or “Run” on any remaining pages. This will create a dump file of all of your tables in the Access database that you specified. Now we’ll import that dump file into MySQL.
7. Open MySQL Workbench. Double click on your “Local instance MySQL56″ box on the front page. Enter your password that you just specified during installation.
8. Goto Server->Data Import. Select the bubble for “Import from Self-Contained File” and enter the path to your dump file. Click Start Import at the bottom.
9. If all went well, your Access database will now be available in MySQL under the database name that you specified in step 6.

You can now query your database from Python using my instructions from last week.

Book Review- The Lottery and Other Stories

The Lottery and Other Stories
By Shirley Jackson, 1949

thelottery

Certainly haunting. Mostly still relevant. Illuminates the gossip and crazy people that live in small towns and the inhuman interactions and pace of city life.

It’s a book of 26 short stories, the last of which is “The Lottery” and fairly famous. I read “The Lottery” in a short story class in high school. A lot of the stories ended in a frustrating manner, but Shirley Jackson probably meant for that to happen. She didn’t seem too concerned with happy endings or pleasing the reader.

Forecasting Attendance

I’m starting a project in which I attempt to build a model to forecast the attendance at a sporting event. Historical data will go in and future forecasts will pop out. I’m interested in predicting total attendance at each game.

Here are some variables that I think might effect the attendance at a given game:
-Sport
-Home Team
-Away Team
-Stadium/Location
-Month
-Weekday
-Time of game
-Home Record
-Home Playoff Chance
-Away Record
-Away Playoff Chance
-Number of home game (home openers obviously well attended)
-Weather
-Temperature
-Indoor/outdoor stadium
-Attendance Capacity
-Promotions
-TV Coverage of game
-Is it a holiday or holiday weekend?
-In-division game? Rivalry game?
-Is away team defending divisional/conference/league champion?
-Do teams rarely play? Intra-league game?
-Current injuries to key starters

I’m trying to be general, so that similar variables work across sports. Some of them (indoor/outdoor, for example) obviously don’t make sense in every sport. These variables will probably feed into some form of regression model, so the variables “Home Team” and “Stadium/location” will incorporate a lot of fixed effects of the team: size of fan base, interest in that sport in that city, average cost of tickets, etc.

So, am I missing any variables that would help predict the attendance at a game? Help me out.

Life Tips- Don’t Touch the Poison Ivy

Seriously. I’m just getting over a two-week itchy hell. Probably pulled some poison ivy vines in my new garden bed (we moved in August), got the oil on my gloves, and then touched my wrists, stomach, and legs. After a week of itchiness, I gave up and went to the medical center to get steroids to stop the allergic response. I’m just now starting to feel better. For about a week, I couldn’t sleep for more than an hour at a time because the itchiness would wake me up whenever my legs touched each other.

poison-ivy-plant

If you live in Bloomington, be sure to know what poison ivy looks like. A good primer.

Code Monkey Monday- Querying a MySQL Database with Python

1. Set up a MySQL Database on your computer.
2. Install the Python library MySQL-python from http://www.lfd.uci.edu/~gohlke/pythonlibs/
3. In your code, add the import statement “import MySQLdb” at the top.
4. Find the database host, username, password, and database of interest.
5. Set up a database connection in Python with a line similar to
“db_connection = MySQLdb.connect(host=’localhost’, user=’root’, passwd=’FluffyBunnies1234′, db=’MyAwesomeDatabase’)”
Here, my host is “localhost” because the database is stored locally on my desktop at work. I access the database from the “root” user account. You can also connect to external databases as long as you frame the connection string properly.
6. Set up a database cursor that you will use to execute queries. “cursor = db_connection.cursor()”
7. Structure up your SQL query. The query should be a string. All white space is treated equivalently, so you can have a space or a newline between parts of the SQL. Example “SQL = SELECT alpha, beta FROM parameter_table WHERE alpha>.5 ORDER BY beta ASC”.
8. Execute the query with the cursor. “cursor.execute(SQL)”
9. Fetch the output. “output = cursor.fetchall()”. If your query will return too many results to grab all at once, you can instead use the function fetchone() instead.
10. Your “output” variable will be a list of lists, where each inner list represents one line returned by query and each element of the inner list represents a column referenced in the SELECT statement. If my query above returned 500 parameter combinations, then I would have a list of 500 lists, where each of the 500 inner lists has two elements: alpha and beta.

Hope this gets you started. For a full documentation of the MySQLdb class, you could start here. Pay attention to make sure you’re reading the parts about the MySQLdb wrapper.

Book Review- Million Dollar Arm

Million Dollar Arm
J.B. Bernstein, 2014

milliondollar

Good opportunity to learn some cultural differences between India and the U.S. while reading a sports book. India is crazy.

I haven’t seen the movie yet, but the book is decent. The author, a sports agent, isn’t particularly likeable, but seems to be good at what he does. An underlying theme is the author’s growth as a human being as he goes from helping rich athletes become richer to actually trying to change peoples’ lives and as he goes from pick-up artist to family man. He starts a reality-show-like contest in India to identify athletes who may have the ability to throw a major-league fastball. Long story short: there aren’t many options in crazy India and getting the few options that are there to work out is a ton of work—both physically and culturally.

Two Links Tuesday- September 16, 2014

10 Questions to Ask in a Job Interview: I especially like ‘What is the history of this position? Is it newly created? If not, why did the previous person leave it?’ and ‘What’s the company culture like? Do co-workers eat lunch together? Do you have regular team events?’

95 Student Discounts: Scroll to the bottom to see the fast food savings you could reap. I had no idea.

Book Review- A Funny Thing Happened on the Way to the Future

A Funny Thing Happened on the Way to the Future
Michael J. Fox, 2010

funny thing happened

Short book/audiobook of Michael J. Fox’s insights into receiving an education on the job. We listened to the audiobook on the way to vacation. It was written as an extended graduation speech to graduates, and this book contains a few nuggets of wisdom about succeeding in the face of adversity and seizing opportunities. Fox is known as an optimist, but fancies himself a realist in the book and describes how he reacted and thrived in the face of early-onset Parkinson’s disease. The book is a little interesting, but not life-altering.

While advertised/packaged as a gift for graduating high-school seniors, Maria and I think it is more relevant as a gift to high-school underclassmen. Get ‘em early.