Monday, May 26, 2008

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.

1 comment:

Robocop said...

Great, tanx a lot!

Is it possible to read from multy mysql db (and graph a multy y-axis)