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 as ccrs

#import MatPlotLib module
import matplotlib.pyplot as plt

#create a list variable
data_list = []
#establish a connection to the database

#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

#set up the map and projection
ax = plt.axes(projection=ccrs.PlateCarree())
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

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


C.H.I.P: the World’s first $9 computer

There’s a really cool Kickstarter project to be found here.   C.H.I.P.  What’s interesting about C.H.I.P is that it promises to be the World’s first $9 computer.  What’s really cool about this is that it will be able to run a version of Linux (Python and bash scripting anyone?).  Not only that but it also features, GPIOs (input output pins for all you people like me who are interested in hooking up various motors and sensors to your projects), blue tooth and wi-fi (built in).  For $9.  The only existing product that I was tempted to buy to get these features (but glad I didn’t now) is the Arudino based LightBlue Bean.  A $9 price tag means this becomes available to virtually everyone to tinker with and learn.  How awesome is that?


How to edit a batch of entries in a MySQL database using an update query!

This morning I uploaded some sample information to my logging in database at work.  Due to hidden differences in character encoding from the spreadsheet I was copying from I ended up with some extra characters in my database field; this made it more difficult to read the sample numbers.

It looked something like this:


When it should have looked like this:


If I didn’t know about update queries I would have had to edit each of the 31 entries individually.

Luckily there is a much easier way.  Use an update query.

N.B. For this tutorial i’m going to assume that you can at least sign into the phpmyadmin pages of your database.  If you don’t know what that is, then this tutorial is too advanced for you.

In the phpmyadmin pages of my website is a helpful box for inputting what is known as an “SQL String”.  This is simply a set of commands in SQL language that tells the database what to do with it’s contents.

Step-1 Select your database:

Select your database on the left hand side of the phpmyadmin screen.  In our example it’s called “newdb”.







Step-2 Select the SQL tab:

Select the SQL tab along the top menu of the main body of phpMyAdmin.mysql_tut_2








Step-3 Type in the query string:

Type the following into the “Run SQL query/queries on database newdb:” box:

UPDATE table_name SET field_name = REPLACE(field_name, 'thing_you_want_changing','thing_you_want_it_changing_to') WHERE field_name LIKE '%+AF8-%';

N.B. substitute table_name, field_name, thing_you_want_changing, and thing_you_want_it_changing_to with what you need to.

Then press “go” in the bottom left of the SQL box.

If the query has worked, you should get a message along the lines of “31 rows affected. ( Query took 0.0220 sec )”.  After running this query for my example, all of the records I wanted correcting were corrected!

Hey presto, you now know how to use a simple update query in MySQL, making use of the “REPLACE” function and the logical operators “WHERE” and “LIKE” to boot.  This is just one example, there are many other things you can do with update queries including dozens of logical operators and functions; depending on what needs doing!


Raspberry Pi A+ 3D model

I needed a Raspberry Pi A+ 3D model in order to build a case for it, which will be able to be mounted as a microscope camera.  All the other models are available on the SketchUp 3D Warehouse, except for this one.  So I decided to make one and then share it.  See a 3D model below.

The model is available on the SketchUp 3D Warehouse here.

One day, I’ll have the time to finish this idea! (promise), until then enjoy the model in your own projects!


Data mining!


There be gold in them there spreadsheets!  Coming soon, a new tutorial series on mining all those random spreadsheets for data with the intention of transferring your data to a more manageable system (MySQL + PHP + JQuery).  Why cut and paste when you can get Python and MySQL to do the hard work?

Featured in the picture is a new type of Raspberry Pi case available from SmartiPi, which is among other things compatible with Lego.  The case I have was an award for backing the designer’s Kickstarter project, though for those who missed it, they’re now available for pre-order.   Data miner not included; he was bought from Boswells in Oxford, who let you choose your Lego figures…



Palaeopi Upgrade

It’s now official.  Palaeopi is now powered by a Raspberry Pi 2.  I had a few hiccups.  Before you transfer from your old Pi, make sure to upgrade through the following commands:

sudo apt-get update

sudo apt-get upgrade

Also, during boot up my Pi was hanging.  I was finding that the default governer, which is something to do with dynamic clock speed was causing the system to freeze.  My clock speed was set to the old setting for a model B rev 1.2 @ 900MhZ.  To overcome this, you can override this on start up by pressing ‘shift’.  Log in as usual and then type “raspi-config” and go to the over clocking settings.  In there, there is an option for the new Pi2.  After this, I got normal behaviour on boot up.

Looking forward to trying out some parallel programming in Python and having a faster server!

Raspberry Pi 2 Out Today

I had a surprise this morning.  The Raspberry Pi foundation has finally released the Raspberry Pi 2.  It features, x6 the performance with a quad core ARM7 SOC with 1GB ram!

It also has all the things which made the Pi B+ such a cool upgrade.  I’m talking about the 40 GPIOs, 4 USB ports and reduced power consumption.  Check out the picture below, a wonderful piece of British engineering!



What this means is the Pi can now be used comfortably as a desktop.  It also makes it more attractive for people interested experimenting with building low power clusters.  Not only this, but children (the next generation of coders and makers) will now be able to write programs in python utilising parallel processing!

I know what I want for my next birthday!  *hint*