라즈베리파이로 온도/습도, 진동, 잡음 측정 및 기록 장치 만들기 #02

웹으로 출력하는 것 역시 간단하다.

먼저 DB 에서 시간 단위로 데이터를 추출해서 csv 포맷으로 저장한다. 여기서는 90일, 30일, 10일, 5일, 3일, 1일 단위로 데이터를 추출하는 것을 보이고 있다.

저장된 csv 파일을 불러들여서 가로축은 시간, 세로축은 CPU온도, 온도, 습도, 진동, 잡음 .. 으로 그래프를 그리고 png 파일로 저장하고, 저장된 png 파일을 볼 수 있도록 간단한 html 파일을 자동으로 생성되도록 한다.

코드 아래쪽에 http://IOT-DEVICE-IP-ADDRESS 라는 문구가 있는데, 그곳에 도메인이나 ip 를 적고, 그 주소로 접속하면 90일 누적 데이터부터 하루치 데이터까지 볼 수 있다.

라즈베리파이에서 운영하는 것이므로, ~pi 계정을 그대로 쓰고 있는 것이고, 웬만하면 바꾸는 게 좋다.

그리고 설마, DB 를 env 로 비밀번호를 PASSWORD 로 쓰지는 않겠지 ? 반드시 다른 것을 쓰자 … 이건 그냥 내가 허접하게 작성한 예일 뿐이다.

이 코드 역시 crontab 에 넣어서 30분이나 1시간마다 한번씩 돌리면 30분, 1시간 간격으로 웹페이지를 갱신해서 보여준다.

import datetime, time, warnings, pymysql, csv, sys, os
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.font_manager as fm
from matplotlib import style, rc


timestr = time.strftime("%Y%m%d-%H%M")

query = "SELECT * FROM Reading order by date DESC LIMIT 2880;"

warnings.filterwarnings(action='ignore') 
connect = pymysql.connect(host='localhost', user='env', password='PASSWORD', db='env', charset='utf8mb4')
cur = connect.cursor()
cur.execute(query)
rows = cur.fetchall()

fp = open('/home/pi/data/ENVIRONMENT.csv', 'w+')
file = csv.writer(fp)
file.writerows(rows)
fp.close()
connect.close()

# database query End

# database query for 90 days

query = "SELECT * from Reading WHERE date > DATE_ADD(now(), INTERVAL - 2160 HOUR);"

warnings.filterwarnings(action='ignore') 
connect = pymysql.connect(host='localhost', user='env', password='PASSWORD', db='env', charset='utf8mb4')
cur = connect.cursor()

cur.execute(query)
rows = cur.fetchall()

fp = open('/home/pi/data/ENVIRONMENT-90D.csv', 'w+')
file = csv.writer(fp)
file.writerows(rows)
fp.close()
connect.close()

# database query for 30 days

query = "SELECT * from Reading WHERE date > DATE_ADD(now(), INTERVAL - 720 HOUR);"

warnings.filterwarnings(action='ignore') 
connect = pymysql.connect(host='localhost', user='env', password='PASSWORD', db='env', charset='utf8mb4')
cur = connect.cursor()

cur.execute(query)
rows = cur.fetchall()

fp = open('/home/pi/data/ENVIRONMENT-30D.csv', 'w+')
file = csv.writer(fp)
file.writerows(rows)
fp.close()
connect.close()

# database query for 10 days

query = "SELECT * from Reading WHERE date > DATE_ADD(now(), INTERVAL - 240 HOUR);"

warnings.filterwarnings(action='ignore') 
connect = pymysql.connect(host='localhost', user='env', password='PASSWORD', db='env', charset='utf8mb4')
cur = connect.cursor()

cur.execute(query)
rows = cur.fetchall()

fp = open('/home/pi/data/ENVIRONMENT-10D.csv', 'w+')
file = csv.writer(fp)
file.writerows(rows)
fp.close()
connect.close()

# database query for 5 days

query = "SELECT * from Reading WHERE date > DATE_ADD(now(), INTERVAL - 120 HOUR);"

warnings.filterwarnings(action='ignore') 
connect = pymysql.connect(host='localhost', user='env', password='PASSWORD', db='env', charset='utf8mb4')
cur = connect.cursor()

cur.execute(query)
rows = cur.fetchall()

fp = open('/home/pi/data/ENVIRONMENT-05D.csv', 'w+')
file = csv.writer(fp)
file.writerows(rows)
fp.close()
connect.close()

# database query for 3 days

query = "SELECT * from Reading WHERE date > DATE_ADD(now(), INTERVAL - 72 HOUR);"

warnings.filterwarnings(action='ignore') 
connect = pymysql.connect(host='localhost', user='env', password='PASSWORD', db='env', charset='utf8mb4')
cur = connect.cursor()

cur.execute(query)
rows = cur.fetchall()

fp = open('/home/pi/data/ENVIRONMENT-03D.csv', 'w+')
file = csv.writer(fp)
file.writerows(rows)
fp.close()
connect.close()

# database query for 1 days

query = "SELECT * from Reading WHERE date > DATE_ADD(now(), INTERVAL - 24 HOUR);"

warnings.filterwarnings(action='ignore') 
connect = pymysql.connect(host='localhost', user='env', password='PASSWORD', db='env', charset='utf8mb4')
cur = connect.cursor()

cur.execute(query)
rows = cur.fetchall()

fp = open('/home/pi/data/ENVIRONMENT-01D.csv', 'w+')
file = csv.writer(fp)
file.writerows(rows)
fp.close()
connect.close()

# database query for Graph End

# Graph 만들기

style.use('ggplot')

D2CODING = fm.FontProperties(fname='/usr/share/fonts/truetype/naver-d2coding/D2Coding-Ver1.3.2-20180524-all.ttc')
UnDotum = fm.FontProperties(fname='/usr/share/fonts/truetype/unfonts-core/UnDotum.ttf')

font_path = '/usr/share/fonts/truetype/naver-d2coding/D2Coding-Ver1.3.2-20180524-all.ttc'
font_name = fm.FontProperties(fname=font_path).get_name()
fontprop = fm.FontProperties(fname=font_path, size = 10)

# Graph 만들기

# for 90D
df = pd.read_csv('/home/pi/data/ENVIRONMENT-90D.csv', delimiter=",", index_col=1, parse_dates=[0], dayfirst=False,  names=['date', 'CPU 온도', '온도', '습도', '진동', '잡음'])

df.plot()
df.plot(linewidth=0.5)
plt.ylim([-10,110])
plt.grid(True)
plt.title('90일 ', fontproperties = fontprop)
plt.xticks(rotation=15, fontsize = 6)
plt.legend(prop={'family':font_name, 'size':10}, loc = "upper left")
plt.savefig('/home/pi/public_html/ENVIRONMENT-90D.png')

# for 30D
df = pd.read_csv('/home/pi/data/ENVIRONMENT-30D.csv', delimiter=",", index_col=1, parse_dates=[0], dayfirst=False,  names=['date', 'CPU 온도', '온도', '습도', '진동', '잡음'])
df.plot()
df.plot(linewidth=0.5)
plt.ylim([-10,110])
plt.grid(True)
plt.title('30일 ', fontproperties = fontprop)
plt.xticks(rotation=15, fontsize = 6)
plt.legend(prop={'family':font_name, 'size':10}, loc = "upper left")
plt.savefig('/home/pi/public_html/ENVIRONMENT-30D.png')

# for 10D
df = pd.read_csv('/home/pi/data/ENVIRONMENT-10D.csv', delimiter=",", index_col=1, parse_dates=[0], dayfirst=False,  names=['date', 'CPU 온도', '온도', '습도', '진동', '잡음'])
df.plot()
df.plot(linewidth=0.5)
plt.ylim([-10,110])
plt.grid(True)
plt.title('10일 ', fontproperties = fontprop)
plt.xticks(rotation=15, fontsize = 6)
plt.legend(prop={'family':font_name, 'size':10}, loc = "upper left")
plt.savefig('/home/pi/public_html/ENVIRONMENT-10D.png')

# for 05D
df = pd.read_csv('/home/pi/data/ENVIRONMENT-05D.csv', delimiter=",", index_col=1, parse_dates=[0], dayfirst=False,  names=['date', 'CPU 온도', '온도', '습도', '진동', '잡음'])
df.plot
df.plot(linewidth=0.5)
plt.ylim([-10,110])
plt.grid(True)
plt.title('05일 ', fontproperties = fontprop)
plt.xticks(rotation=15, fontsize = 6)
plt.legend(prop={'family':font_name, 'size':10}, loc = "upper left")
plt.savefig('/home/pi/public_html/ENVIRONMENT-05D.png')

# for 03D
df = pd.read_csv('/home/pi/data/ENVIRONMENT-03D.csv', delimiter=",", index_col=1, parse_dates=[0], dayfirst=False,  names=['date', 'CPU 온도', '온도', '습도', '진동', '잡음'])
df.plot()
df.plot(linewidth=0.5)
plt.ylim([-10,110])
plt.grid(True)
plt.title('03일 ', fontproperties = fontprop)
plt.xticks(rotation=15, fontsize = 6)
plt.legend(prop={'family':font_name, 'size':10}, loc = "upper left")
plt.savefig('/home/pi/public_html/ENVIRONMENT-03D.png')

# for 01D
df = pd.read_csv('/home/pi/data/ENVIRONMENT-01D.csv', delimiter=",", index_col=1, parse_dates=[0], dayfirst=False,  names=['date', 'CPU 온도', '온도', '습도', '진동', '잡음'])
df.plot()
df.plot(linewidth=0.5)
plt.ylim([-10,110])
plt.grid(True)
plt.title('01일 ', fontproperties = fontprop)
plt.xticks(rotation=15, fontsize = 6)
plt.legend(prop={'family':font_name, 'size':10}, loc = "upper left")
plt.savefig('/home/pi/public_html/ENVIRONMENT-01D.png')

# Graph 만들기 - without limit

# for 90D
df = pd.read_csv('/home/pi/data/ENVIRONMENT-90D.csv', delimiter=",", index_col=1, parse_dates=[0], dayfirst=False,  names=['date', 'CPU 온도', '온도', '습도', '진동', '잡음'])

df.plot()
df.plot(linewidth=0.5)
plt.grid(True)
plt.title('90일 ', fontproperties = fontprop)
plt.xticks(rotation=15, fontsize = 6)
plt.legend(prop={'family':font_name, 'size':10}, loc = "upper left")
plt.savefig('/home/pi/public_html/ENVIRONMENT-90D-wol.png')

# for 30D
df = pd.read_csv('/home/pi/data/ENVIRONMENT-30D.csv', delimiter=",", index_col=1, parse_dates=[0], dayfirst=False,  names=['date', 'CPU 온도', '온도', '습도', '진동', '잡음'])
df.plot()
df.plot(linewidth=0.5)
plt.grid(True)
plt.title('30일 ', fontproperties = fontprop)
plt.xticks(rotation=15, fontsize = 6)
plt.legend(prop={'family':font_name, 'size':10}, loc = "upper left")
plt.savefig('/home/pi/public_html/ENVIRONMENT-30D-wol.png')

# for 10D
df = pd.read_csv('/home/pi/data/ENVIRONMENT-10D.csv', delimiter=",", index_col=1, parse_dates=[0], dayfirst=False,  names=['date', 'CPU 온도', '온도', '습도', '진동', '잡음'])
df.plot()
df.plot(linewidth=0.5)
plt.grid(True)
plt.title('10일 ', fontproperties = fontprop)
plt.xticks(rotation=15, fontsize = 6)
plt.legend(prop={'family':font_name, 'size':10}, loc = "upper left")
plt.savefig('/home/pi/public_html/ENVIRONMENT-10D-wol.png')

# for 05D
df = pd.read_csv('/home/pi/data/ENVIRONMENT-05D.csv', delimiter=",", index_col=1, parse_dates=[0], dayfirst=False,  names=['date', 'CPU 온도', '온도', '습도', '진동', '잡음'])
df.plot
df.plot(linewidth=0.5)
plt.grid(True)
plt.title('05일 ', fontproperties = fontprop)
plt.xticks(rotation=15, fontsize = 6)
plt.legend(prop={'family':font_name, 'size':10}, loc = "upper left")
plt.savefig('/home/pi/public_html/ENVIRONMENT-05D-wol.png')

# for 03D
df = pd.read_csv('/home/pi/data/ENVIRONMENT-03D.csv', delimiter=",", index_col=1, parse_dates=[0], dayfirst=False,  names=['date', 'CPU 온도', '온도', '습도', '진동', '잡음'])
df.plot()
df.plot(linewidth=0.5)
plt.grid(True)
plt.title('03일 ', fontproperties = fontprop)
plt.xticks(rotation=15, fontsize = 6)
plt.legend(prop={'family':font_name, 'size':10}, loc = "upper left")
plt.savefig('/home/pi/public_html/ENVIRONMENT-03D-wol.png')

# for 01D
df = pd.read_csv('/home/pi/data/ENVIRONMENT-01D.csv', delimiter=",", index_col=1, parse_dates=[0], dayfirst=False,  names=['date', 'CPU 온도', '온도', '습도', '진동', '잡음'])
df.plot()
df.plot(linewidth=0.5)
plt.grid(True)
plt.title('01일 ', fontproperties = fontprop)
plt.xticks(rotation=15, fontsize = 6)
plt.legend(prop={'family':font_name, 'size':10}, loc = "upper left")
plt.savefig('/home/pi/public_html/ENVIRONMENT-01D-wol.png')

file = open('/home/pi/public_html/index.html', 'w', encoding = 'UTF-8')
file.write('<!doctype html>')
file.write('<html><head><meta charset="utf-8"><meta https-equiv="refresh" content="120"><title>환경 측정 데이터</title></head>')
file.write('<body>')

file.write('<h1>환경 측정 데이터</h1><br>')
file.write('<h2>측정시간 : ' + timestr + '</h2>')

file.write('<table border = "1">')

file.write('<tr>')

file.write('<td>')
file.write('<img src=http://IOT-DEVICE-IP-ADDRESS/~pi/ENVIRONMENT-01D.png>')
file.write('</td>')

file.write('<td>')
file.write('<img src=http://IOT-DEVICE-IP-ADDRESS/~pi/ENVIRONMENT-03D.png>')
file.write('</td>')

file.write('<td>')
file.write('<img src=http://IOT-DEVICE-IP-ADDRESS/~pi/ENVIRONMENT-05D.png>')
file.write('</td>')

file.write('</tr>')

file.write('<tr>')

file.write('<td>')
file.write('<img src=http://IOT-DEVICE-IP-ADDRESS/~pi/ENVIRONMENT-10D.png>')
file.write('</td>')

file.write('<td>')
file.write('<img src=http://IOT-DEVICE-IP-ADDRESS/~pi/ENVIRONMENT-30D.png>')
file.write('</td>')

file.write('<td>')
file.write('<img src=http://IOT-DEVICE-IP-ADDRESS/~pi/ENVIRONMENT-90D.png>')
file.write('</td>')

file.write('</tr>')

file.write('</table')


file.write('<table border = "1">')

file.write('<tr>')

file.write('<td>')
file.write('<img src=http://IOT-DEVICE-IP-ADDRESS/~pi/ENVIRONMENT-01D-wol.png>')
file.write('</td>')

file.write('<td>')
file.write('<img src=http://IOT-DEVICE-IP-ADDRESS/~pi/ENVIRONMENT-03D-wol.png>')
file.write('</td>')

file.write('<td>')
file.write('<img src=http://IOT-DEVICE-IP-ADDRESS/~pi/ENVIRONMENT-05D-wol.png>')
file.write('</td>')

file.write('</tr>')

file.write('<tr>')

file.write('<td>')
file.write('<img src=http://IOT-DEVICE-IP-ADDRESS/~pi/ENVIRONMENT-10D-wol.png>')
file.write('</td>')

file.write('<td>')
file.write('<img src=http://IOT-DEVICE-IP-ADDRESS/~pi/ENVIRONMENT-30D-wol.png>')
file.write('</td>')

file.write('<td>')
file.write('<img src=http://IOT-DEVICE-IP-ADDRESS/~pi/ENVIRONMENT-90D-wol.png>')
file.write('</td>')

file.write('</tr>')


file.write('</table')

file.write('/<body></html>')

file.close()

---

2022.06.08 akpil