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

In the previous part of this tutorial I showed you how to connect to a remote MySQL database using a Python script.  Today I am going to briefly follow this up on how to subsequently use Python to do something useful with this data by combining the things we also leant from the Python Cartopy tutorial.

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 am also going to assume that you know the basics of MySQL admin i.e. creating tables and inserting entries from the phpmyadmin pages.  In addition you will require a table in your MySQL database with the following structure with a least one entry inserted.

Table Structure
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(10)     | NO   | PRI | NULL    | auto_increment |
| place_name | text        | NO   |     | NULL    |                |
| lat        | varchar(25) | NO   |     | NULL    |                |
| lon        | varchar(25) | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

Step 1 – Install the required Python modules

MySQLdb – see here for instructions on installation

Matplotlib and Cartopy – see here for instructions on installation

Step 2 – Write your script

Using the code below (an amalgamation of the code used part one of this tutorial and the Cartopy) we can select data from our MySQL table and then plot it as points on a map of the UK.

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

#import Cartopy, the mapping module
import cartopy.crs as ccrs

#import MatPlotLib module
import matplotlib.pyplot as plt

#create a list variable
data_list = []
 
#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 row in the rows variable, append the row to the data_list variable
for row in rows: data_list.append(row)
 
#release the database connection
db.close()

#set up the map and projection
ax = plt.axes(projection=ccrs.PlateCarree())
ax.coastlines(resolution='10m')
ax.stock_img()
ax.set_extent([-12, 2, 48, 62])

#for each item in the data_list variable plot on the map
for item in data_list:
   #grab lat lon and convert to float type
   lon = float(item[2])
   lat = float(item[3])
 
   #grab place name
   name = item[1]
 
   #plot marker for each item
   plt.plot(lat, lon, color='blue', marker='o',transform=ccrs.PlateCarree())
 
   #plot label for each item
   plt.text(lat - 0.25, lon - 0.25, name, horizontalalignment='right',transform=ccrs.PlateCarree())

#show the plot
plt.show()

If everything has worked correctly you should be presented with something looking like the following map with our database entries plotted as points!

uk_gps_plots