Using matplotlib to plot data from a MySQL table

matplotlib is a Python 2D plotting library and can be easily used to plot data directly from a MySQL database. You will need to install the MySQL support for Python if you don't already have it.

The following code plotmysql.py makes a simple 2D plot using data from a MySQL database:

import sys
from pylab import *
import MySQLdb

db = MySQLdb.connect(sys.argv[1], sys.argv[2], sys.argv[3],
                    sys.argv[4])
cursor = db.cursor()

query = sys.argv[5]
cursor.execute(query)
result = cursor.fetchall()

t = []
s = []

for record in result:
  t.append(record[0])
  s.append(record[1])

plot(t, s, 'ko')
axis([min(t), max(t), min(s), max(s)])
title(query)
grid(True)

F = gcf()
DPI = F.get_dpi()
F.savefig('plot.png',dpi = (80))

The resulting file is a png image called plot.png. To run the code, type:

python plotmysql.py [host] [user] [password] [database] [query]

The five required arguments here are the hostname of the MySQL database, username, passowrd, database and the query. For example:

python plotmysql.py localhost testuser testpass testdb "select x, y from data"

will plot column y versus column x from the table called data in the database called testdb. This query is obviously very simple, however a plot can of course be made from any query no matter how complex, provided there are 2 values per row in the set of results.