Setup

First you must ensure you have installed a C compiler. If you need to do this follow the instructions here:

Installing a C compiler

Second, you must ensure you have MySQL installed on your PC. If you need to do this follow the instructions here:

Installing mySQL

Third, you need Python 2.7 for a 64bit OS installed. If you need to do this follow the instructions here:

Installing Python 2.7 for 64bit Windows

Installing MySQL for Python 2.7 64bit

To install MySQLdb for Python 2.7 / 64bit you must have Visual Studio 2008 installed, and in particular you need vcsetup.exe for the C compiler. For Python 2.7 it must be the 2008 version because that’s the version the Python 2.7 uses.

Finding an installer for MySQLdb for a 64-bit OS was particularly hard to find. Luckily I stumbled across an unofficial source/s:

http://blog.victorjabur.com/2011/06/08/modules-python-library-compiled-for-windows-32-and-64-unofficial-windows-binaries-for-python/

On Victor Jabur’s blog, scroll down to MySQLdb – Plugin Connector of Database MySQL to Python. Download the MySQL-python-1.2.3.win-amd64-py2.7.exe and execute it. Finally, the installer found my 64-bit version of Python2.7 and installed properly. Note that if you attempt to install a 32bit version of MySQL-python it will not be able to find your 64bit version in the registry and fail.

Running a Python program to Interact with MySQL

With MySQL-python-1.2.3 installed correctly, opening Python2.7 IDLE GUI again and running a Python program will now be able to import the MySQL module.

Getting a Python Program to Connect to MySQL

To get a Python program to connect to MySQL you need the following information:

  • db_host. Usually “localhost”. Check your MySQL Users and Privileges – User Accounts.
  • db_user. Usually “root”. But can be some other user with less privileges. Check your MySQL Users and Privileges – User Accounts.
  • db_pass. Usually “password” but can also be the password for a different user. This is setup initially when the user is created.
  • db_name. The name of the MySQL schema you wish to connect to.
  • The connection object, usually called ‘con’, which must be set to
con = mdb.connect(host='localhost', user='root', passwd='password', db='your_schema')

However one problem I encountered was the following access denied error:

OperationalError: (1045, "Access denied for user 'root'@'fe80::e90f:5f95:f972:1c50%11' (using password: YES)")

The first thing I did was to check my MySQL privileges, so I opened up my MySQL Command Line Client and typed in

>SHOW GRANTS FOR 'root'@'localhost';

The results showed that the root user had all privileges. In my python code I was using root as my user (I.e. the default admin user with all privileges), so privileges was not the issue.

Next, I typed in the following SQL in to the Command Line Client:

SELECT USER, HOST FROM MYSQL.USER;

In the USER column, find the user name you wish to establish your python connection with and also note the HOST name. The solution was that my HOST name was incorrect. I had been using my Computer Name as my HOST, so I opened up my MySQL Workbench and logged in as the root user. Opened up the localhost instance and went to users and privileges. Sure enough the HOST name corresponding to the root user was localhost. So I updated my python code, in particular my connection parameters, to reflect the USER name as db_user and the HOST name as db_host. Here is my connection code:

# Connect to the MySQL instance
db_host = 'localhost'
db_user = 'root'
db_pass = 'password'
db_name = 'securities_master'
con = mdb.connect(host=db_host, user=db_user, passwd=db_pass, db=db_name)