Shurui Liu Shurui Liu - 6 months ago 44
MySQL Question

matplotlib mySQL blank plot

I made this Python script. The idea is using this Python script so that matplotlib can generate a vertical bar chart for me using data from a MySQL database. Another thing I want to do is saving the png file instead of displaying it (I am trying to run this code via putty), which I think I did it correctly. When I run the code, I got some error which I have no idea what it means. Hopefully someone can point out where I did wrong.

#!/usr/bin/python
# matplotlib pyplot module
import MySQLdb
import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt

# connect to MySQL database
conn = MySQLdb.connect(host="localhost", user="xx", passwd="xxxx", db="r1array")

# prepare a cursor
cur = conn.cursor()

# this is the query we will be making
query = """
SELECT TimeStamp,Pac
FROM SolarData
WHERE TimeStamp >= "2014-01-03"
AND TimeStamp < "2014-01-04";
"""

# execute the query
cur.execute(query)

# retrieve the whole result set
data = cur.fetchall()

# close cursor and connection
cur.close()
conn.close()

# unpack data in TimeStamp (x axis) and Pac (y axis)
TimeStamp, Pac = zip(*data)

# graph code
plt.bar(TimeStamp, Pac, align='center')

# set title, X/Y labels
plt.title("PVIC R1 panel")
plt.xlabel("Time of Day")
plt.ylabel("Pac")
fig = plt.gcf()

# plt.xticks(TimeStamp, (hour))
fig.set_size_inches(20.5,10.5)
plt.grid(True)
plt.draw()
fig.savefig('test2.png', dpi=100)


The error message I got is

Traceback (most recent call last):
File "python_sql_image.py", line 41, in <module>
plt.savefig('test.png')
File "/usr/lib/python2.6/site-packages/matplotlib/pyplot.py", line 356, in savefig
return fig.savefig(*args, **kwargs)
File "/usr/lib/python2.6/site-packages/matplotlib/figure.py", line 1032, in savefig
self.canvas.print_figure(*args, **kwargs)
File "/usr/lib/python2.6/site-packages/matplotlib/backend_bases.py", line 1476, in print_figure
**kwargs)
File "/usr/lib/python2.6/site-packages/matplotlib/backends/backend_agg.py", line 358, in print_png
FigureCanvasAgg.draw(self)
File "/usr/lib/python2.6/site-packages/matplotlib/backends/backend_agg.py", line 314, in draw
self.figure.draw(self.renderer)
File "/usr/lib/python2.6/site-packages/matplotlib/artist.py", line 46, in draw_wrapper
draw(artist, renderer, *kl)
File "/usr/lib/python2.6/site-packages/matplotlib/figure.py", line 773, in draw
for a in self.axes: a.draw(renderer)
File "/usr/lib/python2.6/site-packages/matplotlib/artist.py", line 46, in draw_wrapper
draw(artist, renderer, *kl)
File "/usr/lib/python2.6/site-packages/matplotlib/axes.py", line 1735, in draw
a.draw(renderer)
File "/usr/lib/python2.6/site-packages/matplotlib/artist.py", line 46, in draw_wrapper
draw(artist, renderer, *kl)
File "/usr/lib/python2.6/site-packages/matplotlib/axis.py", line 736, in draw
for tick, loc, label in self.iter_ticks():
File "/usr/lib/python2.6/site-packages/matplotlib/axis.py", line 680, in iter_ticks
majorLabels = [self.major.formatter(val, i) for i, val in enumerate(majorLocs)]
File "/usr/lib/python2.6/site-packages/matplotlib/dates.py", line 417, in __call__
return self._formatter(x, pos)
File "/usr/lib/python2.6/site-packages/matplotlib/dates.py", line 293, in __call__
dt = num2date(x, self.tz)
File "/usr/lib/python2.6/site-packages/matplotlib/dates.py", line 249, in num2date
if not cbook.iterable(x): return _from_ordinalf(x, tz)
File "/usr/lib/python2.6/site-packages/matplotlib/dates.py", line 170, in _from_ordinalf
dt = datetime.datetime.fromordinal(ix)
ValueError: ordinal must be >= 1


especially the last line, I don't get this.

ValueError: ordinal must be >= 1


This is the data I called from MySQL database.

TimeStamp | Pac |
+---------------------+------+
| 2014-01-03 07:55:02 | 0 |
| 2014-01-03 08:00:02 | 0 |
| 2014-01-03 08:05:02 | 2 |
| 2014-01-03 08:10:02 | 11 |
| 2014-01-03 08:15:03 | 72 |
| 2014-01-03 08:20:04 | 162 |
| 2014-01-03 08:25:04 | 307 |
| 2014-01-03 08:30:04 | 464 |
| 2014-01-03 08:40:01 | 665 |
| 2014-01-03 08:45:01 | 764 |
| 2014-01-03 08:50:02 | 844 |
| 2014-01-03 08:55:01 | 931 |
| 2014-01-03 09:00:02 | 1017 |
| 2014-01-03 09:05:02 | 1098 |
| 2014-01-03 09:10:01 | 1176 |
| 2014-01-03 09:15:02 | 1272 |
| 2014-01-03 09:20:01 | 1351 |
| 2014-01-03 09:25:02 | 1432 |
| 2014-01-03 09:30:01 | 1528 |
| 2014-01-03 09:35:02 | 1653 |
| 2014-01-03 09:40:02 | 1738 |
| 2014-01-03 09:45:01 | 1821 |
| 2014-01-03 09:50:02 | 1895 |
| 2014-01-03 09:55:01 | 2015 |
| 2014-01-03 10:00:02 | 2153 |
| 2014-01-03 10:05:01 | 2300 |
| 2014-01-03 10:10:02 | 2482 |
| 2014-01-03 10:15:02 | 2635 |
| 2014-01-03 10:20:01 | 2750 |
| 2014-01-03 10:25:02 | 2923 |
| 2014-01-03 10:30:01 | 3059 |
| 2014-01-03 10:35:02 | 3201 |
| 2014-01-03 10:40:01 | 3275 |
| 2014-01-03 10:45:02 | 3360 |


What I need is a bar chart. Right now I am able to get an image but it's just a wrong image.
bad bar plot

Answer

If you're on a recent version of pandas, you should use the read_sql function...

import matplotlib.pyplot as plt
import pandas
import MySQLdb

# connect to MySQL database 
conn = MySQLdb.connect(host="localhost", user="xx", passwd="xxxx", db="r1array")


# this is the query we will be making 
query = """ 
SELECT TimeStamp, Pac  
FROM SolarData  
WHERE TimeStamp >= "2014-01-03"
  AND TimeStamp < "2014-01-04"; 
"""

df = pandas.read_sql(query, conn, index_col=['TimeStamp'])
fig, ax = plt.subplots()
df.plot(ax=ax)
conn.close()

Setting your TimeStamp column as the index of the dataframe will let pandas know that it goes on the x-axis.

I converted a small subset of data you posted to a CSV, and all other things equal, I get this: time series plot

response to comments:

Since you really want a bar plot, here's a solution to the problem you're having. The default width for bar plots in matplotlib is 0.8. You can find this by looking at the docstring for pyplot.bar. Point is, 0.8 of what? Well when you have a time axis, that's 0.8 days, or a little over 19 hours. So just set that width kwarg to something more sensible.

import numpy as np
import matplotlib.pyplot as plt
import matplotlib.dates as mdates


Pac = [
    0,0,2,11,72,162,307,464,665,764,844,931,1017,1098,1176,1272,1351,1432,
    1528,1653,1738,1821,1895,2015,2153,2300,2482,2635,2750,2923,3059,3201,
    3275,3360
]
TimeStamp = mdates.num2date(mdates.datestr2num([
    '2014-01-03 07:55:02', '2014-01-03 08:00:02', '2014-01-03 08:05:02', '2014-01-03 08:10:02', 
    '2014-01-03 08:15:03', '2014-01-03 08:20:04', '2014-01-03 08:25:04', '2014-01-03 08:30:04', 
    '2014-01-03 08:40:01', '2014-01-03 08:45:01', '2014-01-03 08:50:02', '2014-01-03 08:55:01', 
    '2014-01-03 09:00:02', '2014-01-03 09:05:02', '2014-01-03 09:10:01', '2014-01-03 09:15:02', 
    '2014-01-03 09:20:01', '2014-01-03 09:25:02', '2014-01-03 09:30:01', '2014-01-03 09:35:02', 
    '2014-01-03 09:40:02', '2014-01-03 09:45:01', '2014-01-03 09:50:02', '2014-01-03 09:55:01', 
    '2014-01-03 10:00:02', '2014-01-03 10:05:01', '2014-01-03 10:10:02', '2014-01-03 10:15:02', 
    '2014-01-03 10:20:01', '2014-01-03 10:25:02', '2014-01-03 10:30:01', '2014-01-03 10:35:02', 
    '2014-01-03 10:40:01', '2014-01-03 10:45:02'
]))

fig = plt.figure()
ax = fig.add_subplot()
ax.bar(TimeStamp, Pac, align='center', width=0.002)

And that gives me:

enter image description here

Comments