How to connect to a MySQL database using Python (MySQLdb): part 1

At some point, you may wish to connect to a MySQL database remotely using a Python script.  I wished to be able to do so to do the following:

1.  grab coordinates from a samples database to plot points on a map via Cartopy and Matplotlib.

2. insert temperature and humidity readings into my MySQL database from an Arduino project hooked up to a remote machine.

N.B I am using Ubuntu 64bit desktop v14.04, and Python2.7 and I am going to assume that you have access to a MySQL database with remote access enabled.

I will not show you how to setup MySQL in this tutorial, for that see here

I am going to show you how this can be done using the MySQLdb module in python.

Step 1 – Install MySQLdb

To install MySQLdb in Ubuntu, the following should work:

1. Be sure you have pip installed on your machine using this command in terminal:

  • sudo easy_install pip

2. If you already have pip installed, it’d be a good idea to upgrade it now:

  • sudo pip install pip --upgrade

3. Build the dependencies for python-mysqldb libraries:

  • sudo apt-get build-dep python-mysqldb

4. Install the Python MySQL libraries:

  • sudo pip install MySQL-python

Step 2 – Write your connection script

In the following example, where there is a series of x’s e.g. xxxxx replace with the details relating to your database, and ‘table’ with the name of your target database table:

#import the MySQLdb module
import MySQLdb

#establish a connection to the database
db=MySQLdb.connect(passwd="xxxxx",db="xxxxx",host="xxxxx",port=xxxxx,user="xxxxx")

#define your cursor
cursor = db.cursor()

#define and execute your MySQL query string
cursor.execute("SELECT * from table")

#grab the rows in the query result and add to a variable
rows = cursor.fetchall()

#for each item in the rows variable, print item 
for row in rows: print row

#release the database connection
db.close()

 

If you run this script, you should see the whole table be printed to a console window.

Now that you can access a MySQL database using python, you can grab this data and do what you like with it.  Stay tuned for part 2.