A Python-MySQL Project

I assigned myself a project for the weekend, writing a little program that I could use to disable comments on blog posts based on their age. Python’s already installed on the Mac, so I figured it’d be pretty easy to use that to make updates to my WordPress database.

The first thing needed is the connecter to allow Python to talk to a MySQL datbase, mysql-python. The setup program for the newest version, 1.2.2, didn’t want to run for me and the hints I found around the ‘net didn’t seem to apply, so I downloaded version 1.2.1_p2. There was still some work to do to get it to install though.

To install the connector, MySQL needed to be installed locally first. Sun now owns MySQL but fortunately there’s a Community version available for download free of charge for personal use.

Once that’s installed, it’s back to the MySQL-Python connector installation. Several sites had notes which helped me find that setup.py tries to call mysql_config but can’t find it, so I edited the line from ...popen("mysql_config... to ...popen("/usr/local/mysql/bin/mysql_config...

The setup program gave me another error, this time because I don’t have gcc installed – to complete the build it needs to be able to call the C compiler. Back when I set up my PowerBook I’d not installed that part of the developer tools, so I had to dig out my OS X disks and do that. Then finally the setup worked cleanly:

python setup.py clean
python setup.py build
python setup.py install

Now that all the bits are in the right places, I’m finally ready to start getting some code going. I added permission to my databases, creating a new user that has remote access, and used that.

# use the connector library
import MySQLdb
# connect to the database, using the correct username and password
conn = MySQLdb.connect (host = "vorefamily.net", user = "my_db_username", passwd = "my_db_password", db = "my_database") # look in your wp-config.php file to find the right database
# update the fields, using an SQL call
conn.query("""UPDATE wp_posts SET comment_status = 'closed' WHERE year(post_date) < 2008 """) # close the database connection conn.close ()

It's not long or fancy, but it does exactly what was needed at the moment. I'll build more of a wrapper around it later, to make it more flexible, add error checking etc.

An evening of forensic work while watching a movie with my family, followed by some light development this morning... success. And, perhaps more importantly, it gets my system back to a place where I can start digging into larger projects I've been thinking about.