In [13]:
import MySQLdb
import sys
connection = MySQLdb.connect(host='power', user='root', passwd='pet=81zi', db='test')
cursor = connection.cursor()
%pylab
figWidth, figHeight = 18, 10
In [14]:
SQL = """SELECT MIN( `DateTime` ),
Convert(MIN( `DateTime` ),Date) AS 'Datum',
Convert(MIN( `DateTime` ),Time) AS 'Uhrzeit',
AVG( `Voltage` ) AS 'Voltage',
AVG( `Current`*1000 ) AS 'Current',
AVG( `Phase` ) AS 'Phase',
COUNT( `DateTime` ) AS 'Count',
AVG( `Voltage`*`Current` ) AS 'Leistung'
FROM `PandasTest`
WHERE (CONVERT(`DateTime`, DATE) > DATE_SUB(CURRENT_DATE,INTERVAL 21 DAY))
GROUP BY Convert(`DateTime`,Date), Hour( `DateTime` ) , Minute( `DateTime` )
ORDER BY `DateTime`"""
In [15]:
SQL = """SELECT MIN( `DateTime` ),
Convert(MIN( `DateTime` ),Date) AS 'Datum',
Convert(MIN( `DateTime` ),Time) AS 'Uhrzeit',
AVG( `Voltage` ) AS 'Voltage',
AVG( `Current`*1000 ) AS 'Current',
AVG( `Phase` ) AS 'Phase',
COUNT( `DateTime` ) AS 'Count',
AVG( `Voltage`*`Current` ) AS 'Leistung'
FROM `PandasTest`
WHERE (CONVERT(`DateTime`, DATE) > '2013-11-19')
GROUP BY Convert(`DateTime`,Date), Hour( `DateTime` ) , Minute( `DateTime` )
ORDER BY `DateTime`"""
In [16]:
cursor.execute(SQL)
rows = cursor.fetchall()
nprows = np.array(rows)
fig = plt.figure()
figure(num=None, figsize=(figWidth, figHeight), dpi=300, facecolor='w', edgecolor='k')
fig.autofmt_xdate()
plot(nprows[:,0],nprows[:,5],alpha = 0.80, color = "#A60628", label = "Phase")
# plt.fill_between(nprows[:,0],nprows[:,5], alpha = .4, facecolor = ["#A60628"])
plt.ylabel(u"Phase $[\%]$")
plt.xlabel(r'Uhrzeit $[HH:MM:SS]$')
plt.title( "Phase %s \n Arbeitsplatz (BW)" % "der letzten Tage")
plt.legend()
plt.grid(True)
In [17]:
fig = plt.figure()
figure(num=None, figsize=(figWidth, figHeight), dpi=300, facecolor='w', edgecolor='k')
fig.autofmt_xdate()
plot(nprows[:,0],nprows[:,3],alpha = 0.80, color = "#A60628", label = "Voltage")
plt.ylabel(u"Voltage $[V]$")
plt.xlabel(r'Uhrzeit $[HH:MM:SS]$')
plt.title( "Spannung %s \n Arbeitsplatz (BW)" % "der letzten Tage")
plt.legend()
plt.grid(True)
In [18]:
fig = plt.figure()
figure(num=None, figsize=(figWidth, figHeight), dpi=300, facecolor='w', edgecolor='k')
fig.autofmt_xdate()
plot(nprows[:,0],nprows[:,4],alpha = 0.80, color = "#A60628", label = "Current")
plt.ylabel(u"Current $[mA]$")
plt.xlabel(r'Uhrzeit $[HH:MM:SS]$')
plt.title( "Strom %s \n Arbeitsplatz (BW)" % "der letzten Tage")
plt.legend()
plt.grid(True)
In [19]:
fig = plt.figure()
figure(num=None, figsize=(figWidth, figHeight), dpi=300, facecolor='w', edgecolor='k')
fig.autofmt_xdate()
plot(nprows[:,0],nprows[:,7],alpha = 0.80, color = "#A60628", label = "Power")
plt.ylim(25, 65)
plt.ylabel(u"Power $[W]$")
plt.xlabel(r'Uhrzeit $[HH:MM:SS]$')
plt.title( "Leistung %s \n Arbeitsplatz (BW)" % "der letzten Tage")
plt.legend()
plt.grid(True)
Nachfolgend werden für jeden Tag alle Werte aus der Datenbank geladen und anschließend pro Tag ein Plot erstellt.
In [21]:
SQL = """SELECT MIN( `DateTime` ),
Convert(MIN( `DateTime` ),Date) AS 'Datum',
Convert(MIN( `DateTime` ),Time) AS 'Uhrzeit',
AVG( `Voltage` ) AS 'Voltage',
AVG( `Current`*1000 ) AS 'Current',
AVG( `Phase` ) AS 'Phase',
COUNT( `DateTime` ) AS 'Count',
AVG( `Voltage`*`Current` ) AS 'Leistung'
FROM `PandasTest`
WHERE (CONVERT(`DateTime`, DATE) = '%s')
AND (CONVERT(`DateTime`, Time) > '07:00:00')
AND (CONVERT(`DateTime`, Time) < '21:00:00')
GROUP BY Convert(`DateTime`,Date), Hour( `DateTime` ) , Minute( `DateTime` )
ORDER BY `DateTime`"""
def printDays(days):
"""plots Diagramms for each Day"""
for day in days:
try:
SQL_Day = SQL % day
print "Now plotting: %s" % day
#print SQL_Day
cursor.execute(SQL_Day)
rows = cursor.fetchall()
nprows = np.array(rows)
fig = plt.figure()
figure(num=None, figsize=(figWidth, figHeight), dpi=300, facecolor='w', edgecolor='k')
fig.autofmt_xdate()
plot(nprows[:,0],nprows[:,4],alpha = 0.80, color = "#A60628", label = "Current")
plt.ylabel(u"Current $[mA]$")
plt.xlabel(r'Uhrzeit $[HH:MM:SS]$')
plt.title( "Strom vom %s \n Arbeitsplatz (BW)" % day)
plt.legend()
plt.grid(True)
fig = plt.figure()
figure(num=None, figsize=(figWidth, figHeight), dpi=300, facecolor='w', edgecolor='k')
fig.autofmt_xdate()
plot(nprows[:,0],nprows[:,3],alpha = 0.80, color = "#A60628", label = "Voltage")
plt.ylabel(u"Voltage $[V]$")
plt.xlabel(r'Uhrzeit $[HH:MM:SS]$')
plt.title( "Spannung vom %s \n Arbeitsplatz (BW)" % day)
plt.legend()
plt.grid(True)
fig = plt.figure()
figure(num=None, figsize=(figWidth, figHeight), dpi=300, facecolor='w', edgecolor='k')
fig.autofmt_xdate()
plot(nprows[:,0],nprows[:,5],alpha = 0.80, color = "#A60628", label = "Phase")
plt.ylabel(u"Phase $[\%]$")
plt.xlabel(r'Uhrzeit $[HH:MM:SS]$')
plt.title( "Phase %s vom \n Arbeitsplatz (BW)" % day)
plt.legend()
plt.grid(True)
fig = plt.figure()
figure(num=None, figsize=(figWidth, figHeight), dpi=300, facecolor='w', edgecolor='k')
fig.autofmt_xdate()
plot(nprows[:,0],nprows[:,7],alpha = 0.80, color = "#A60628", label = "Power")
#plt.ylim(25, 65)
plt.ylabel(u"Power $[W]$")
plt.xlabel(r'Uhrzeit $[HH:MM:SS]$')
plt.title( "Leistung vom %s \n Arbeitsplatz (BW)" % day)
plt.legend()
plt.grid(True)
print "Finished sucessfully"
except:
print "Fault in %s" % day
printDays(['2013-11-12','2013-11-13','2013-11-14','2013-11-15','2013-11-16','2013-11-17','2013-11-18','2013-11-19',
'2013-11-20','2013-11-21','2013-11-22','2013-11-23','2013-11-24','2013-11-25'])
In [128]:
SQL = """SELECT Count(`DateTime`) AS `Anzahl`,
AVG(`Voltage`) AS `Voltage`
FROM `PandasTest`
WHERE `Voltage` > 0
GROUP BY Round(`Voltage`,1)"""
cursor.execute(SQL)
rows = cursor.fetchall()
nprows = np.array(rows)
fig = plt.figure()
figure(num=None, figsize=(figWidth, figHeight), dpi=300, facecolor='w', edgecolor='k')
plt.bar( nprows[:,1], nprows[:,0],width=0.1, color="#A60628",
label = "number of measure points \nmeasured variable: Voltage", alpha = 0.95)
#plot(nprows[:,1],nprows[:,0],alpha = 0.80, color = "#A60628", label = "Phase")
# plt.fill_between(nprows[:,0],nprows[:,5], alpha = .4, facecolor = ["#A60628"])
plt.ylabel(u"number of points")
plt.xlabel(r'Voltage $[V]$')
plt.xlim( 230,240);
plt.title( "statistic distribution of voltage")
plt.legend()
plt.grid(True)
mean = 0
number =0
for line in nprows:
mean = mean + line[0]*line[1]
number = number + line[0]
mean = mean/number
print "The mean value: %s" % mean
In [129]:
SQL = """SELECT Count(`DateTime`) AS `Anzahl`,
Round(AVG(`Current`),2) AS `Current`
FROM `PandasTest`
WHERE `Current` > 0
GROUP BY Round(`Current`,2)"""
cursor.execute(SQL)
rows = cursor.fetchall()
nprows = np.array(rows)
#print nprows
fig = plt.figure()
figure(num=None, figsize=(figWidth, figHeight), dpi=300, facecolor='w', edgecolor='k')
plt.bar( nprows[:,1], nprows[:,0],width=0.01, color="#A60628",
label = "number of measure points \nmeasured variable: Current", alpha = 0.95)
#plot(nprows[:,1],nprows[:,0],alpha = 0.80, color = "#A60628", label = "Phase")
# plt.fill_between(nprows[:,0],nprows[:,5], alpha = .4, facecolor = ["#A60628"])
plt.ylabel(u"number of points")
plt.xlabel(r'Current $[A]$')
# plt.xlim( 2.3,2.35)
plt.ylim( 0,800)
plt.title( "statistic distribution of current")
plt.legend()
plt.grid(True)
mean = 0
number =0
for line in nprows:
mean = mean + line[0]*line[1]
number = number + line[0]
mean = mean/number
print "The mean value: %s" % mean
In [130]:
SQL = """SELECT Count(`DateTime`) AS `Anzahl`,
Round(AVG(`Phase`),2) AS `Phase`
FROM `PandasTest`
WHERE `Phase` > 0
GROUP BY Round(`Phase`,2)"""
cursor.execute(SQL)
rows = cursor.fetchall()
nprows = np.array(rows)
#print nprows
fig = plt.figure()
figure(num=None, figsize=(figWidth, figHeight), dpi=300, facecolor='w', edgecolor='k')
plt.bar( nprows[:,1], nprows[:,0],width=0.01, color="#A60628",
label = "number of measure points \nmeasured variable: Phase", alpha = 0.95)
#plot(nprows[:,1],nprows[:,0],alpha = 0.80, color = "#A60628", label = "Phase")
# plt.fill_between(nprows[:,0],nprows[:,5], alpha = .4, facecolor = ["#A60628"])
plt.ylabel(u"number of points")
plt.xlabel(r'Phase $[\%]$')
# plt.xlim( 2.3,2.35)
#plt.ylim( 0,800)
plt.title( "statistic distribution of phase")
plt.legend()
plt.grid(True)
mean = 0
number =0
for line in nprows:
mean = mean + line[0]*line[1]
number = number + line[0]
mean = mean/number
print "The mean value: %s" % mean
In [ ]: