Connecting to mysql from python the odbc way from mac

This post just describes how to set up python to talk to a mysql database on a mac. Written for reference, and for co-workers who will shortly need to do something like this.

I wanted to be able, in a python script, to talk to a remote MySQL database from my mac. As I went about this I found the documentation a little scattered -- I couldnt find a single page giving the instructions, and so I've just written up the steps here.

I assume that you have python installed already.

Step 1: Install pyodbc

First step is to install pyodbc if you havent got it installed already. I downloaded pyodbc from http://code.google.com/p/pyodbc/downloads/list (I downloaded the source distribution because there were no binaries for mac. The source distribution is the one that ends in .zip)

After unzipping this, going into the directory that is created by the unzip and typing python setup.py install at the command prompt should install everything without you needing to do anything else.

Step 2: Check that you have an odbc manager installed

pyodbc wont do all the work for you -- it relies on an odbc manager to ferry things back and forth to databases. On a Mac you should have an odbc manager installed already by default. My machine has one called iodbc: if I type iodbctest at the command prompt* I get the following:

iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0607.1008

Enter ODBC connect string (? shows list):

If I then type '?' as suggested, I get an empty list... which explains that there are no drivers installed. What is a driver? Its a database-specific connector that lets the odbc manager talk to a particular database (MySQL, Postgresql, SQLServer, etc). So the last link required in the communication chain is a mysql driver.

* Apparently you can get to iodbc through Applications->Utilities as well if you want to look for it in the Mac GUI.

Step 3: Install your odbc drivers

OK, so we now need to install specific odbc drivers for each database we want to connect to. In this case, I'm just going to install MySQL. I grab the odbc driver from http://dev.mysql.com/downloads/connector/odbc (picking the Mac .dmg file just to make the installation easy). A few clicks on the .dmg file and the driver is installed.

I can check that it is installed by running iodbctest again from the command prompt. This time when I type '?' to see what drivers are installed I get:

Enter ODBC connect string (? shows list): ?

DSN | Driver
------------------------------------------------------------------------------
myodbc | MySQL ODBC 5.1 Driver

OK, so that completes the pipeline. Now in python I can use pyodbc, which talks to iodbc (or whatever ODBC manager you have installed), which uses the mysql odbc driver to talk to a MySQL database.

So lets do that and check that it works

Step 4: connect to your database from python

OK, so now we should be able to connect to your database in python.

But one last thing you need to do is to work out what connection string you need to connect to your database. A helpful resource with example connection strings can be found at http://www.connectionstrings.com/. Here is what I did in python to connect to my database:

>>> import pyodbc
>>> connstr="Driver={MySQL ODBC 5.1 Driver};Server=your.server.;Port=3306;Database=databasename;User=username; Password=nottelling;Option=3;"
>>> conn=pyodbc.connect(connstr)
>>>

and from here you can just play with the conn object as you like in python! So, for example, to look at a table:

>>> res = conn.execute("select * from sometable")
>>>for r in res:
>>>    print r.next()