라즈베리파이로_환경_측정_02
문서의 이전 판입니다!
라즈베리파이로 온도/습도, 진동, 잡음 측정 및 기록 장치 만들기 #02
웹으로 출력하는 것 역시 간단하다.
먼저 DB 에서 시간 단위로 데이터를 추출해서 csv 포맷으로 저장한다. 여기서는 90일, 30일, 10일, 5일, 3일, 1일 단위로 데이터를 추출하는 것을 보이고 있다.
저장된 csv 파일을 불러들여서 가로축은 시간, 세로축은 CPU온도, 온도, 습도, 진동, 잡음 .. 으로 그래프를 그리고 png 파일로 저장하고, 저장된 png 파일을 볼 수 있도록 간단한 html 파일을 자동으로 생성되도록 한다.
코드 아래쪽에 http://IOT-DEVICE-IP-ADDRESS 라는 문구가 있는데, 그곳에 도메인이나 ip 를 적고, 그 주소로 접속하면 90일 누적 데이터부터 하루치 데이터까지 볼 수 있다.
라즈베리파이에서 운영하는 것이므로, ~pi 계정을 그대로 쓰고 있는 것이고, 웬만하면 바꾸는 게 좋다.
# -*- coding: utf-8 -*-
# 필요 패키지 설치
# sudo apt install build-essential python3-dev python3-pip libatlas-base-dev libcups2-dev libglib2.0-dev libsmbclient-dev libgirepository1.0-dev libcairo2-dev
# pip 설정
# /usr/bin/python3 -m pip install --upgrade pip setuptools wheel
# /usr/bin/python3 -m pip install --upgrade pip
# pip3 list --outdated --format=freeze | grep -v '^\-e' | cut -d = -f 1 | xargs -n1 pip3 install -U
# pip3 install Adafruit_DHT datetime pymysql pandas numpy
# DB 생성 qyerry 문 - localhost
# sudo mysql -u root -p
# CREATE USER 'env'@'localhost' IDENTIFIED BY 'PASSWORD';
# CREATE dataBASE env;
# GRANT ALL PRIVILEGES ON *.* TO 'env'@'localhost';
# FLUSH PRIVILEGES;
# QUIT
# DB 생성 - localhost
# mysql -u env -p
# USE env;
# CREATE TABLE Reading (id INT AUTO_INCREMENT NOT NULL, date DATETIME NOT NULL, temp1 DECIMAL(5,2) NOT NULL, temp2 DECIMAL(5,2) NOT NULL, humidity DECIMAL(5,2) NOT NULL, vibration DECIMAL(5,2) NOT NULL, PRIMARY KEY (id));
import datetime, time, warnings, pymysql, csv, sys, os
import pandas as pd
import numpy as np
import telegram
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
라즈베리파이로_환경_측정_02.1654672403.txt.gz · 마지막으로 수정됨: 2022/06/08 16:13 저자 akpil