この記事ではデータベース内の気象観測データから下記のような画像データ(Base64エンコード文字列)を生成するスクリプトの実装方法について解説します。
気象観測データは下記画像に示したシステムの気象センサーで収集されたデータベースになります。
- Raspberry Pi zero (headless OS: Raspbian GNU/Linux 10 (buster))
SQLite3データベースで記録
Flaskアプリで観測データのCSVファイルダウンロード機能を提供 - Raspberry Pi 4 (Desktop OS: Debian GNU/Linux 11 (bullseye))
Dockerコンテナ内で稼働するPostgreSQL 12データベースで記録
Flaskアプリでスマホ向け・ブラウザ向けに可視化機能を提供している
上記システムの概要とソースコードは下記GitHubリポジトリでご覧になれます
GitHub(pipito-yukio) ラズベリーパイによる家庭用気象データ監視システム
GitHub(pipito-yukio) 気象データ表示Webアプリケーション (Flaskアプリ)
GitHub(pipito-yukio) UDP Weather Sensor packet monitor for Raspberry pi 4
記事のスクリプトの実行環境と前提条件
- OS: Ubuntu 22.04.3 LTS
- Python仮想環境を作成し必要なライブラリをインストールする
[Python version] python 3.10.12 ※ 3.7以上
1. Python仮想環境の作成とライブラリのインストール
- Python仮想環境作成 (例) 仮想環境名を "py_visualize" とする例
$ python3 -m venv py_visualize
# 仮想環境に入る
$ . py_visualize/bin/activate
- 最低限必要なライブラリ(Pandas, Matplotlib)のインストール
※ SQLite3データベースを使用する場合は追加のライブラリはありません。
$ python3 -m venv py_visualize
(py_visualize) $ pip install pandas matplotlib
- PostgreSQLデータベースを処理するライブラリ(psycopg2-binary)のインストール
※上記ライブラリを使用するとOSへのPostgreSQLクライアントのインストールが不要になります
(py_visualize) $ pip install psycopg2-binary
- SQLAlchemy ORM ライブラリを使用する場合
※ 個別のデータベースライブラリは必要になります。
(py_visualize) $ pip install psycopg2-binary sqlalchemy
2.参考にしたサイト
- pandas API reference: pandas.read_sql
- SQLAlchemy: Engine Configuration
- SQLAlchemy: Session Basics
- SQLAlchemy: Transactions and Connection Management
- Psycopg 2.9.9 documentation
- wiki: Psycopg2 Tutorial
- stackoverflow: StringIO and pandas read_csv
2-1.pandas.read_sql()メソッドが対応するデータベースとORMの組み合わせ
- SQLite3データベースの場合: sqlite3 connection
- SQLite3以外のデータベースはSQLAlchemyを使用する必要があります
※下記は pandas.read_sqlのAPIリファレンスの抜粋
pandas.read_sql(sql, con, ...省略...)
...一部省略...
Parameters:
sql : str or SQLAlchemy Selectable (select or text object)
SQL query to be executed or a table name.
con : SQLAlchemy connectable, str, or sqlite3 connection
Using SQLAlchemy makes it possible to use any DB supported by that library. If a
DBAPI2 object, only sqlite3 is supported. The user is responsible for engine disposal
and connection closure for the SQLAlchemy connectable; str connections are closed
automatically. See here.
3.テーブル定義
3-1.SQLite3データベース
※1 Raspberry Pi zero WHで稼働中のもの
※2 t_weatherテーブルの measurement_timeはINTEGER("unixepoch"+"localtime")の値を格納
CREATE TABLE IF NOT EXISTS t_device(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS t_weather(
did INTEGER NOT NULL,
measurement_time INTEGER NOT NULL,
temp_out real,
temp_in real,
humid real,
pressure real,
PRIMARY KEY (did, measurement_time),
FOREIGN KEY (did) REFERENCES t_devices (id) ON DELETE CASCADE
);
3-2.PostgreSQLデータベース
Raspberry Pi 4 で稼働中のもの
- テーブル定義
CREATE SCHEMA IF NOT EXISTS weather;
CREATE TABLE IF NOT EXISTS weather.t_device(
id INTEGER NOT NULL,
name VARCHAR(20) UNIQUE NOT NULL,
description varchar(128) NOT NULL,
CONSTRAINT pk_device PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS weather.t_weather(
did INTEGER NOT NULL,
measurement_time timestamp NOT NULL,
temp_out REAL,
temp_in REAL,
humid REAL,
pressure REAL
);
ALTER TABLE weather.t_weather ADD CONSTRAINT pk_weather PRIMARY KEY (did, measurement_time);
ALTER TABLE weather.t_weather ADD CONSTRAINT fk_device FOREIGN KEY (did) REFERENCES weather.t_device (id);
4. 指定年月と前年のDataFrameから画像文字列を生成するモジュール
モジュールのソースコードは下記GitHubリポジトリをご覧ください
GitHub: matplotlib_knowhow: pandas-read_sql: plotterweather_flat.py
実際の実装はかなりの分量になるので主要なコードのみを示します。
- インポート, 定数定義, 関数定義(主要部分のみ)
import base64
from io import BytesIO
from datetime import datetime
import matplotlib.dates as mdates
from matplotlib.axes import Axes
from matplotlib.figure import Figure
from matplotlib.patches import Patch
# pandas.DataFrameのインデックス列
COL_TIME: str = 'measurement_time'
# 前年DataFrameの年月日+1年
COL_PREV_PLOT_TIME: str = 'prev_plot_measurement_time'
# 観測データ列
COL_TEMP_OUT: str = 'temp_out'
COL_HUMID: str = "humid"
COL_PRESSURE: str = 'pressure'
def datetime_plus_1_year(prev_datetime: datetime) -> datetime:
"""
前年のdatetimeオブジェクトに1年プラスしたdatetimeオブジェクトを取得する
:param prev_datetime: 前年のdatetimeオブジェクト
@return: 1年プラスしたdatetimeオブジェクト
"""
next_val: datetime = datetime(prev_datetime.year + 1,
prev_datetime.month,
prev_datetime.day,
prev_datetime.hour,
prev_datetime.minute,
prev_datetime.second
)
return next_val
def set_ylim_with_axes(plot_axes: Axes, curr_ser: Series, prev_ser: Series) -> None:
"""
各データの最大値・最小値を設定する
:param plot_axes: プロット領域
:param curr_ser: 最新データ
:param prev_ser: 前年データ
"""
val_min: float = np.min([curr_ser.min(), prev_ser.min()])
val_max: float = np.max([curr_ser.max(), prev_ser.max()])
val_min = np.floor(val_min / 10.) * 10.
val_max = np.ceil(val_max / 10.) * 10.
plot_axes.set_ylim(val_min, val_max)
- 気象データプロット画像生成関数
(1) 指定年月のデータと同じX軸(時間軸)に前年データをプロットするための実装 (★★)
前年の測定時刻に1年プラスすると指定年月のデータと同一時間軸となります
(applyメソッドAPIは https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.apply.html を参照[API document: pandas.DataFrame.apply])
(applyメソッド使用サンプルは https://www.dataquest.io/blog/tutorial-how-to-use-the-apply-method-in-pandas/ を参照[Tutorial: How to Use the Apply Method in Pandas])
def gen_plot_image(df_curr: DataFrame, df_prev: DataFrame,
year_month: str, prev_year_month: str,
logger: Optional[logging.Logger] = None) -> str:
"""
指定年月とその前年の観測データをプロットした画像のBase64エンコード済み文字列を生成する
:param df_curr: 指定年月の観測データのDataFrame
:param df_prev: 前年の年月の観測データのDataFrame
:param year_month: 指定年月 (形式: "%Y-%m")
:param prev_year_month: 前年の年月 (形式: "%Y-%m")
:param logger: application logger
:return: 画像のBase64エンコード済み文字列
"""
#...ラベル、タイトル生成などの実装は省略...
title: str = "xxxxxxxx"
# (1) 前年データをX軸にプロットするために測定時刻列にを1年プラスする ★★
df_prev[COL_PREV_PLOT_TIME] = df_prev[COL_TIME].apply(datetime_plus_1_year)
if logger is not None:
logger.debug(f"{df_prev}")
【参考】追加(★★)した列("prev_plot_measurement_time") ※X軸の単位(年月日)が一致する
DEBUG measurement_time temp_out humid pressure prev_plot_measurement_time
0 2022-07-31 15:01:44 24.4 70.1 1008.9 2023-07-31 15:01:44
1 2022-07-31 15:11:29 24.0 69.8 1008.7 2023-07-31 15:11:29
2 2022-07-31 15:21:13 24.0 69.3 1008.7 2023-07-31 15:21:13
3 2022-07-31 15:30:58 23.9 69.2 1008.9 2023-07-31 15:30:58
4 2022-07-31 15:40:42 23.8 68.9 1009.0 2023-07-31 15:40:42
... ... ... ... ... ...
4574 2022-08-31 14:20:20 17.3 65.1 1002.0 2023-08-31 14:20:20
4575 2022-08-31 14:30:04 17.4 65.3 1002.1 2023-08-31 14:30:04
4576 2022-08-31 14:39:48 17.4 65.7 1002.1 2023-08-31 14:39:48
4577 2022-08-31 14:49:33 17.4 65.5 1002.4 2023-08-31 14:49:33
4578 2022-08-31 14:59:16 17.4 65.5 1002.4 2023-08-31 14:59:16
- (2) グラフ領域(980x640)と各データのプロット領域の生成
※プロット領域は縦に3列(外気温, 室内湿度, 気圧)
# (2) グラフ領域生成
fig: Figure
ax_temp: Axes # 外気温プロット領域
ax_humid: Axes # 室内湿度プロット領域
ax_pressure: Axes # 気圧プロット領域
# PCブラウザはinch指定でdpi=72
fig = Figure(figsize=(9.8, 6.4), constrained_layout=True)
# x軸を共有する3行1列のサブプロット生成
(ax_temp, ax_humid, ax_pressure) = fig.subplots(nrows=3, ncols=1, sharex=True)
# Y方向のグリッド線のみ表示
for ax in [ax_temp, ax_humid, ax_pressure]:
ax.grid(axis='y', linestyle='dashed', linewidth=0.7, alpha=0.75)
- (3) 各測定テータをそれぞれの領域にプロットする
(3-1) 外気温プロット領域: タイトルを設定しX軸ラベルを隠す
(3-2) 室内湿度プロット領域: X軸ラベルを隠す
(3-3) 気圧プロット領域: X軸ラベルに"月/日"を設定し表示する
※凡例ラベル生成と設定は複雑なため省略しています (オリジナルのソースをご覧ください)
# (3) 各測定テータのプロット
# (3-1) 外気温領域のプロット
# Y軸の最小値・最大値設定
set_ylim_with_axes(ax_temp, df_curr[COL_TEMP_OUT], df_prev[COL_TEMP_OUT])
# 指定年月の外気温データをプロット
ax_temp.plot(df_curr[COL_TIME], df_curr[COL_TEMP_OUT], color='C0', marker="")
# 指定年月の平均外気温 (横線)
ax_temp.axhline(df_curr[COL_TEMP_OUT].mean(),
color='C0', linestyle='dashdot', linewidth=1.)
# ...凡例の実装を省略...
# 前年の年月の外気温データをプロット ※X軸のデータは1年プラスした測定日時
ax_temp.plot(df_prev[COL_PREV_PLOT_TIME], df_prev[COL_TEMP_OUT], color='C1', marker="")
# 前年の年月の平均外気温 (横線)
ax_temp.axhline(df_prev[COL_TEMP_OUT].mean(),
color='C1', linestyle='dashdot', linewidth=1.)
# 前年月の外気温プロット
ax_temp.set_ylabel("外気温 (℃)", fontsize=10)
# ...凡例の実装は省略...
# タイトル設定
ax_temp.set_title(title, fontsize=11)
# X軸ラベルを隠す
ax_temp.label_outer()
# (3-2) 湿度領域のプロット
# 湿度は 0%から100%
ax_humid.set_ylim(ymin=0., ymax=100.)
# 最新年月
ax_humid.plot(df_curr[COL_TIME], df_curr[COL_HUMID], color='C0', marker="")
# ...凡例の実装は省略...
ax_humid.axhline(df_curr[COL_HUMID].mean(),
color='C0', linestyle='dashdot', linewidth=1.)
# 前年月
ax_humid.plot(df_prev[COL_PREV_PLOT_TIME], df_prev[COL_HUMID], color='C1', marker="")
ax_humid.axhline(df_prev[COL_HUMID].mean(),
color='C1', linestyle='dashdot', linewidth=1.)
ax_humid.set_ylabel("室内湿度 (％)", fontsize=10)
ax_humid.label_outer()
# (3-3) 気圧領域のプロット
# Y軸の最小値・最大値設定
set_ylim_with_axes(ax_pressure, df_curr[COL_PRESSURE], df_prev[COL_PRESSURE])
# 最新年月
ax_pressure.plot(df_curr[COL_TIME], df_curr[COL_PRESSURE], color='C0', marker="")
# ...凡例の実装は省略...
ax_pressure.axhline(df_curr[COL_PRESSURE].mean(),
color='C0', linestyle='dashdot', linewidth=1.)
# 前年月
ax_pressure.plot(df_prev[COL_PREV_PLOT_TIME], df_prev[COL_PRESSURE], color='C1', marker="")
ax_pressure.axhline(df_prev[COL_PRESSURE].mean(),
color='C1', linestyle='dashdot', linewidth=1.)
ax_pressure.set_ylabel("気 圧 (hPa)", fontsize=10)
# X軸ラベルは"月/日" ※"年"は表示しない
ax_pressure.xaxis.set_major_formatter(mdates.DateFormatter("%m/%d"))
- HTMLの<img src="画像データのbase64文字列"> "src"に設定する画像文字列を生成する
(BytesIOについては https://docs.python.org/ja/3/library/io.html を参照 [io ストリームを扱うコアツール#バッファ付 ストリーム])
(画像をbase64エンコード文字列にする方法は https://www.codegrepper.com/code-examples/whatever/pil+image+base64 を参考にしました [pil image base64])
# 画像をバイトストリームに溜め込みそれをbase64エンコードしてレスポンスとして返す
buf = BytesIO()
fig.savefig(buf, format="png", bbox_inches="tight")
data = base64.b64encode(buf.getbuffer()).decode("ascii")
# base64エンコード文字列
return "data:image/png;base64," + data
5. SQLからDataFrameを取得する方法
5-1. SQLite3データベースの場合
ここから説明するスクリプトのソースコードは下記GitHubリポジトリをご覧ください
GitHub: matplotlib_knowhow: pandas-read_sql: PlotWeatherCompPrevYear_sqlite3.py
[補足情報]
検索SQLの期間の範囲指定部分の記述が他のデータベースと比べて特殊なため、SQLite3データベースの測定時刻フィールド(INTEGER型)のどのような値をセットしたか説明します。
UDPパケットをt_weatherテーブルに登録する処理 (抜粋)
- データ登録関数
(重要) measurement_timeをintに変換
# SQLite3データベース
def insert(device_name, temp_out, temp_in, humid, pressure,
measurement_time=None, logger=None):
conn = get_connection(weather_db, logger=logger)
did = get_did(conn, device_name, logger=logger)
rec = (did,
int(measurement_time),
to_float(temp_out),
to_float(temp_in),
to_float(humid),
to_float(pressure)
)
if logger is not None and isLogLevelDebug:
logger.debug(rec)
try:
with conn:
conn.execute(INSERT_WEATHER, rec)
except sqlite3.Error as err:
if logger is not None:
logger.warning("rec: {}\nerror:{}".format(rec, err))
finally:
close_connection(conn, logger=logger)
(with conn: 動作については https://docs.python.org/ja/3/library/sqlite3.html を参照[sqlite3 接続(connection)コンテキストマネージャの使い方])
上記実装のソースは下記GitHubリポジトリをご覧ください
GitHub: home_weather_sensors/raspi_zero/bin/pigpio/db/weatherdb.py)
- UDPパケットの登録 ※Raspi端末に到着した時刻を測定時刻としています
(重要) timeモジュール- (1) localtime()関数でローカル時刻を取得
- (2) mktime()関数に上記ローカル時刻をセットしてUNIXタイムスタンプを生成
import time
#...省略...
# From ESP output: device_name, temp_out, temp_in, humid, pressure
line = data.decode("utf-8")
record = line.split(",")
# 測定時刻はパケット到着時刻
# タイムスタンプの生成: UTC(+9hr) "localtime" >> "unix epoch"
local_time = time.localtime()
unix_tmstmp = time.mktime(local_time)
# テーブルに登録
wdb.insert(*record, measurement_time=unix_tmstmp, logger=logger)
上記ソースコードは下記GitHubリポジトリをご覧ください
GitHub: home_weather_sensors/raspi_zero/bin/pigpio/UDPClientFromWeatherSensor.py)
- 選択クエリー(QUERY_RANGE_DATA) ※SQLite3専用
※1 引数(PEP 249: qmark): デバイス名, 検索開始日時, 検索終了日時※含まない
※2 測定時刻は SQLite3の datetime関数 に "unixepoch"と"localtime"を指定する
※3 範囲指定で SQLite3の strftime関数 を使用する必要がある
※3-1 strftime('%s',,): seconds since 1970-01-01 (unix timestamp)
※3-2 strftime(,,'-9 hours'): 逆にUTCから9時間(JST日本時間)を引く必要がある
SELECT
datetime(measurement_time, 'unixepoch', 'localtime') as measurement_time
,temp_out, humid, pressure
FROM
t_weather
WHERE
did=(SELECT id FROM t_device WHERE name=?)
AND (
measurement_time >= strftime('%s', ? ,'-9 hours')
AND
measurement_time < strftime('%s', ? ,'-9 hours')
)
ORDER BY measurement_time;
(datetime, strftime関数は https://www.sqlite.org/lang_datefunc.html を参照[Date And Time Functions])
(parameter styleは https://peps.python.org/pep-0249/ を参照[PEP 249 – Python Database API Specification v2.0])
5-1-1. 指定範囲(開始日と翌日)の日付を生成する関数定義
※ SQLite3データベース以外でも同一の関数を使います
- (1) 指定された年月から翌月の1日を取得する ※範囲条件のtoDate(含まない)を生成
def next_year_month(s_year_month: str) -> str:
"""
年月文字列の次の月を計算する
:param s_year_month: 年月文字列
:return: 翌年月叉は翌年月日
:raise ValueError:
"""
date_parts: List[str] = s_year_month.split('-')
date_parts_size = len(date_parts)
if date_parts_size < 2 or date_parts_size > 3:
raise ValueError
year, month = int(date_parts[0]), int(date_parts[1])
month += 1
if month > 12:
year += 1
month = 1
if date_parts_size == 2:
result = f"{year:04}-{month:02}"
else:
day = int(date_parts[2])
result = f"{year:04}-{month:02}-{day:02}"
return result
- (2) 指定された年月から1年前の年月を取得する
def previous_year_month(s_year_month: str) -> str:
"""
1年前の年月を取得する
:param s_year_month: 妥当性チェック済みの年月文字列 "YYYY-MM"
:return: 1年前の年月
"""
s_year, s_month = s_year_month.split('-')
# 1年前
prev_year: int = int(s_year) - 1
return f"{prev_year}-{s_month}"
- (3) 文字列のファイル保存 ※生成したBase64エンコード済み文字列の保存に使用
def save_text(file, contents):
with open(file, 'w') as fp:
fp.write(contents)
5-1-2. データベース接続取得関数
def get_connection(db_file_path, auto_commit=False, read_only=False, logger=None):
try:
if read_only:
db_uri = "file://{}?mode=ro".format(db_file_path)
connection = sqlite3.connect(db_uri, uri=True)
else:
connection = sqlite3.connect(db_file_path)
if auto_commit:
connection.isolation_level = None
except Error as e:
if logger is not None:
logger.error(e)
raise e
return connection
(connectionの取得方法については https://docs.python.org/ja/3/library/sqlite3.html#sqlite3-connection-context-manager を参照[sqlite3 --- SQLite データベースに対する DB-API 2.0 インターフェース])
5-1-3(1) 指定した年月のデータ取得(Dataframe)関数
- pandas.read_sql(選択クエリー, sqlite3DB接続,SQLパラメータ, parse_dates)
# インデックス列: 測定時刻
COL_TIME: str = "measurement_time"
def get_dataframe(connection,
device_name: str, year_month: str,
logger: Optional[logging.Logger] = None) -> DataFrame:
from_date: str = year_month + "-01"
exclude_to_date: str = next_year_month(from_date)
query_params: Tuple = (
device_name, from_date, exclude_to_date,
)
df: pd.DataFrame = pd.read_sql(
QUERY_RANGE_DATA, connection, params=query_params, parse_dates=[COL_TIME]
)
return df
5-1-3(2) 指定した年月と前年の年月のDataFrame取得関数
※1 当該年月のDataframe、前年のDataframe、前年の年月をタプルで返却
※2 前年の年月は凡例のラベルに使用
def get_all_df(connection,
device_name: str, curr_year_month: str,
logger: Optional[logging.Logger] = None
) -> Tuple[Optional[DataFrame], Optional[DataFrame], Optional[str]]:
# 今年の年月テータ取得
df_curr: DataFrame = get_dataframe(connection, device_name, curr_year_month, logger=logger)
if df_curr is not None and df_curr.shape[0] == 0:
return None, None, curr_year_month
# 前年の年月テータ取得
# 前年計算
prev_ym: str = previous_year_month(curr_year_month)
df_prev: DataFrame = get_dataframe(connection, device_name, prev_ym, logger=logger)
return df_curr, df_prev, prev_ym
5-1-4. スクリプトメイン処理
- 画像生成(HTMLファイル)に必要なパラメータの定義
- 気象データを格納しているデータベースファイルのパス
- センサーデバイス名
- 処理年月
import argparse
import logging
import os
from typing import List, Optional, Tuple
import sqlite3
from sqlite3 import Error
import pandas as pd
from pandas.core.frame import DataFrame
from plotter.plotterweather import gen_plot_image
# スクリプト名
script_name = os.path.basename(__file__)
# ログフォーマット
LOG_FMT = '%(levelname)s %(message)s'
# 出力画層用HTMLテンプレート
OUT_HTML = """
<!DOCTYPE html>
<html lang="ja">
<body>
<img src="{}"/>
</body>
</html>
"""
# ...一部省略...
if __name__ == '__main__':
logging.basicConfig(format=LOG_FMT)
app_logger = logging.getLogger(__name__)
app_logger.setLevel(level=logging.DEBUG)
parser: argparse.ArgumentParser = argparse.ArgumentParser()
# SQLite3 データベースパス: ~/db/weather.db
parser.add_argument("--sqlite3-db", type=str, required=True,
help="QLite3 データベースパス")
# デバイス名: esp8266_1
parser.add_argument("--device-name", type=str, required=True,
help="device name in t_device.")
# 最新の検索年月
parser.add_argument("--year-month", type=str, required=True,
help="2023-04")
args: argparse.Namespace = parser.parse_args()
# データベースパス
db_path: str = os.path.expanduser(args.sqlite3_db)
if not os.path.exists(db_path):
app_logger.warning("database not found!")
exit(1)
# デバイス名
param_device_name: str = args.device_name
# 最新年月
param_year_month = args.year_month
- 画像生成処理
(1) SQLite3データベース接続取得
(2) 指定年月と前年の年月DataFrame取得
(3) 両方のDataFrameにデータが存在したときに画像生成しHTMLテンプレートに画像を埋め込んでファイル保存
conn = None
try:
conn = get_connection(db_path, read_only=True)
app_logger.info(f"connection: {conn}")
curr_df: Optional[DataFrame]
prev_df: Optional[DataFrame]
prev_year_month: Optional[str]
curr_df, prev_df, prev_year_month = get_all_df(
conn, args.device_name, param_year_month, logger=app_logger)
if curr_df is not None and prev_df is not None:
img_src: str = gen_plot_image(
curr_df, prev_df, param_year_month, prev_year_month, logger=app_logger)
# プロット結果をPNG形式でファイル保存
script_names: List[str] = script_name.split(".")
save_name = f"{script_names[0]}.html"
save_path = os.path.join("output", save_name)
app_logger.info(save_path)
html: str = OUT_HTML.format(img_src)
save_text(save_path, html)
else:
app_logger.warning("該当レコードなし")
except Exception as err:
app_logger.warning(err)
exit(1)
finally:
if conn is not None:
conn.close()
5-1-5. スクリプト実行とログ出力
- 実機 Raspberry Pi zero から気象データベースを検証用PCにコピーし、レコードを確認
※実機はGUIのないHeadless OSなので実機上では実行できない
~/db$ scp pi@raspi-zero:~/db/weather.db .
weather.db 100% 6852KB 3.6MB/s 00:01
~/db$ LANG=C ls -l --time-style long-iso
total 6856
-rw-rw-r-- 1 yukio yukio 306 2023-10-23 17:27 MinMaxRec.sql
-rw-r--r-- 1 yukio yukio 7016448 2023-10-29 11:43 weather.db
~/db$ sqlite3 weather.db < MinMaxRec.sql
1|2021-07-30 01:26:51|-11.1|29.9|58.8|999.0
1|2023-10-29 11:39:33|14.5|18.7|59.1|1014.0
- python仮想環境に入り PlotWeatherCompPrevYear_sqlite3.py を実行
[引数] --sqlite3-db ~/db/weather.db --device-name esp8266_1 --year-month 2023-08
$ . ~/py_venv/py_visualize/activate
(py_visualize) $ python PlotWeatherCompPrevYear_sqlite3.py --sqlite3-db ~/db/weather.db --device-name esp8266_1 --year-month 2023-08
INFO connection: <sqlite3.Connection object at 0x7f2ca4251140>
INFO query_params: ('esp8266_1', '2023-08-01 00:00:00', '2023-09-01 00:00:00')
INFO measurement_time temp_out humid pressure
0 2023-08-01 00:07:04 21.6 58.7 1006.8
1 2023-08-01 00:16:48 21.7 59.1 1006.8
2 2023-08-01 00:26:33 21.8 58.7 1006.9
3 2023-08-01 00:36:17 21.7 58.5 1007.0
4 2023-08-01 00:46:01 21.4 59.5 1007.0
... ... ... ... ...
4579 2023-08-31 23:20:51 24.5 74.6 1008.5
4580 2023-08-31 23:30:36 24.5 74.6 1008.5
4581 2023-08-31 23:40:20 24.3 75.9 1008.4
4582 2023-08-31 23:50:04 24.2 76.7 1008.3
4583 2023-08-31 23:59:48 24.2 77.0 1008.1
[4584 rows x 4 columns]
INFO query_params: ('esp8266_1', '2022-08-01 00:00:00', '2022-09-01 00:00:00')
INFO measurement_time temp_out humid pressure
0 2022-08-01 00:01:44 24.4 70.1 1008.9
1 2022-08-01 00:11:29 24.0 69.8 1008.7
2 2022-08-01 00:21:13 24.0 69.3 1008.7
3 2022-08-01 00:30:58 23.9 69.2 1008.9
4 2022-08-01 00:40:42 23.8 68.9 1009.0
... ... ... ... ...
4574 2022-08-31 23:20:20 17.3 65.1 1002.0
4575 2022-08-31 23:30:04 17.4 65.3 1002.1
4576 2022-08-31 23:39:48 17.4 65.7 1002.1
4577 2022-08-31 23:49:33 17.4 65.5 1002.4
4578 2022-08-31 23:59:16 17.4 65.5 1002.4
[4579 rows x 4 columns]
DEBUG measurement_time temp_out humid pressure prev_plot_measurement_time
0 2022-08-01 00:01:44 24.4 70.1 1008.9 2023-08-01 00:01:44
1 2022-08-01 00:11:29 24.0 69.8 1008.7 2023-08-01 00:11:29
2 2022-08-01 00:21:13 24.0 69.3 1008.7 2023-08-01 00:21:13
3 2022-08-01 00:30:58 23.9 69.2 1008.9 2023-08-01 00:30:58
4 2022-08-01 00:40:42 23.8 68.9 1009.0 2023-08-01 00:40:42
... ... ... ... ... ...
4574 2022-08-31 23:20:20 17.3 65.1 1002.0 2023-08-31 23:20:20
4575 2022-08-31 23:30:04 17.4 65.3 1002.1 2023-08-31 23:30:04
4576 2022-08-31 23:39:48 17.4 65.7 1002.1 2023-08-31 23:39:48
4577 2022-08-31 23:49:33 17.4 65.5 1002.4 2023-08-31 23:49:33
4578 2022-08-31 23:59:16 17.4 65.5 1002.4 2023-08-31 23:59:16
[4579 rows x 5 columns]
INFO output/PlotWeatherCompPrevYear_sqlite3.html
※出力結果: output/PlotWeatherCompPrevYear_sqlite3.htmlそのままブラウザで確認できます。
5-2. PostgreSQLデータベース と SQLAlchemy ライブラリを使用した場合
ここから説明するスクリプトのソースコードは下記GitHubリポジトリをご覧ください
GitHub: matplotlib_knowhow: pandas-read_sql: PlotWeatherCompPrevYear_sqlalchemy.py
- 選択クエリー(QUERY_RANGE_DATA)
※ 引数(PEP 249: pyformat) (デバイス名, 検索開始日時, 検索終了日時※含まない)
SELECT
measurement_time, temp_out, humid, pressure
FROM
weather.t_weather
WHERE
did=(SELECT id FROM weather.t_device WHERE name=%(deviceName)s)
AND (
measurement_time >= %(fromDate)s
AND
measurement_time < %(toDate)s
)
ORDER BY measurement_time;
5-2-1. SQLAlchemyデータベースエンジン生成用URL取得関数
- SQLAlchemy関連のインポート
import sqlalchemy.orm.scoping as scoping
from sqlalchemy import create_engine
from sqlalchemy.engine import Engine
from sqlalchemy.engine.url import URL
from sqlalchemy.orm import scoped_session, sessionmaker
- 以下は公式ドキュメントに掲載されているのコード例
(sqlalchemy.URLについては https://docs.sqlalchemy.org/en/20/core/engines.html を参照 [Creating URLs Programmatically])
from sqlalchemy import URL
url_object = URL.create(
"postgresql+pg8000",
username="dbuser",
password="kx@jj5/g", # plain (unescaped) text
host="pghost10",
database="appdb",
)
- SQLAclchemy の URLを生成するための設定ファイル(conf/db_sensors.json)
※1 DBへの接続設定をJSONファイルで保持すると保守性が増します。
※2 {hostname} 部分はあとから実際に接続するホスト名に置き換えられます。
{
"drivername": "postgresql+psycopg2",
"host": "{hostname}.local",
"port": "5432",
"database": "sensors_pgdb",
"username": "developer",
"password": "yourpasswd"
}
- 以下は私の環境のhosts(/etc/hosts)で、PostgreSQLサーバーは開発用PCとRaspberryPi 4で稼働しています
# Development PC
192.168.0.101 dell-t7500.local
# Rasberry pi zero
192.168.0.12 raspi-zero raspi-zero.local
# Rasberry pi 4 Model B: PostgreSQL for docker
192.168.0.16 raspi-4 raspi-4.local
- SQLAlchemy用接続URLを生成する関数
設定ファイルをJSONにすることでURLのコンストラクタに、キーワード引数展開で渡せます
※1 引数 hostname を省略すると、socketモジュールから開発PCのホスト名を取得(socket.gethostname())
※2 引数 hostnameに"raspi-4"と指定すると、RaspberryPi 4(上記 "raspi-4")を設定
def get_engine_url(conf_path: str, hostname: str = None) -> URL:
"""
SQLAlchemyの接続URLを取得する
:param conf_path: 接続設定ファイルパス (JSON形式)
:param hostname: ホスト名 ※未設定なら実行PCのホスト名
:return: SQLAlchemyのURL用辞書オブジェクトDB_HEALTHCARE_CONF
"""
with open(conf_path, 'r') as fp:
db_conf: json = json.load(fp)
if hostname is None:
hostname = socket.gethostname()
# host in /etc/hostname: "hostname.local"
db_conf["host"] = db_conf["host"].format(hostname=hostname)
return URL.create(**db_conf)
以下のような接続用URL文字列が生成されます
postgresql+psycopg2://developer:***@Dell-T7500.local:5432/sensors_pgdb
5-2-2(1) 指定した年月のデータ取得(Dataframe)関数
- pandas.read_sql(選択クエリー, SQLAlchemy Session, SQLパラメータ, parse_dates)
SQLAlchemy(psycopg2)における選択クエリーとSQLパラメータの組み合わせ
| paramstyle (PEP 249) | クエリー | params | 実行結果 |
|---|---|---|---|
| pyformat (採用) | QUERY | Dict | 正常 |
| pyformat | text(QUERY) | Dict | psycopg2.errors.SyntaxError |
| named | QUERY | Dict | psycopg2.errors.SyntaxError |
| named | text(QUERY) | Dict | 正常 |
| qmark | text(QUERY) | Tuple | WARNING List argument must consist only of tuples or dictionaries |
| qmark | QUERY | Tuple | psycopg2.errors.SyntaxError |
PEP 249 – Python Database API Specification v2.0
| paramstyle | 記述例 |
|---|---|
| pyformat | WHERE name=%(deviceName)s |
| named | WHERE name=:deviceName |
| qmark | WHERE name=? |
[補足情報]
SQLAlchemyの sqlalchemy.orm.scoped_session を使用した理由は、Webアプリケーションでも実行可能なモジュールを作ることです。
バッチ環境での実行のみを前提にするのであれば通常はsqlalchemy.orm.Sessionを使用します。
Webアプリケーション(Flaskを想定)では1リクエスト=1スレッドになるので scoped_session の使用が必須となります。
※1 下記コードはバッチ実行でもWebアプリケーションでも正常に処理されます。
※2 scoped_sessionについては下記公式ドキュメントをご覧ください。
SQLAlchemy: Contextual/Thread-local Sessions
def get_dataframe(scoped_sess: scoped_session,
device_name: str, year_month: str,
logger: Optional[logging.Logger] = None) -> DataFrame:
from_date: str = year_month + "-01"
exclude_to_date = next_year_month(from_date)
query_params: Dict = {
'deviceName': device_name, 'fromDate': from_date, 'toDate': exclude_to_date
}
if logger is not None:
logger.info(f"query_params: {query_params}")
try:
with scoped_sess.connection() as conn:
df: pd.DataFrame = pd.read_sql(
QUERY_RANGE_DATA, conn,
params=query_params,
parse_dates=[COL_TIME]
)
if logger is not None:
logger.info(f"{df}")
return df
finally:
scoped_sess.close()
5-2-2(2) 指定した年月と前年の年月のDataFrame取得関数
def get_all_df(cls_sess: scoping.scoped_session,
device_name: str, curr_year_month: str,
logger: Optional[logging.Logger] = None
) -> Tuple[Optional[DataFrame], Optional[DataFrame], Optional[str]]:
sess: scoped_session = cls_sess()
if logger is not None:
logger.info(f"scoped_sess: {sess}")
df_prev: Optional[DataFrame]
try:
# 今年の年月テータ取得
df_curr: DataFrame = get_dataframe(sess, device_name, curr_year_month, logger=logger)
if df_curr is not None and df_curr.shape[0] == 0:
return None, None, curr_year_month
# 前年の年月テータ取得
# 前年計算
prev_ym: str = previous_year_month(curr_year_month)
df_prev: DataFrame = get_dataframe(sess, device_name, prev_ym, logger=logger)
return df_curr, df_prev, prev_ym
finally:
cls_sess.remove()
5-1-3. スクリプトメイン処理
- 画像生成に必要なパラメータ
- センサーデバイス名
- 処理年月
- データベースサーバーのホスト名 ※任意
※ スクリプトメインのインポートはSQLite3のスクリプトと同一のため省略
if __name__ == '__main__':
logging.basicConfig(format=LOG_FMT)
app_logger = logging.getLogger(__name__)
app_logger.setLevel(level=logging.DEBUG)
parser: argparse.ArgumentParser = argparse.ArgumentParser()
# デバイス名: esp8266_1
parser.add_argument("--device-name", type=str, required=True,
help="device name in t_device.")
# 最新の検索年月
parser.add_argument("--year-month", type=str, required=True,
help="2023-04")
# データベースサーバーのホスト名 ※任意 (例) raspi-4
parser.add_argument("--db-host", type=str, help="Other database hostname.")
args: argparse.Namespace = parser.parse_args()
# デバイス名
param_device_name: str = args.device_name
# 比較最新年月
param_year_month = args.year_month
- SQLAlchemy接続URL・データベースエンジン取得・セッション生成
※ レコード有りの場合の画像生成処理は [5-1-4. スクリプトメイン処理] と同一につき割愛
# データベース接続URL生成
connUrl: URL = get_engine_url(DB_CONF)
app_logger.info(f"connUrl: {connUrl}")
Cls_sess: Optional[scoping.scoped_session] = None
try:
# ★ SQLAlchemyデータベースエンジン
db_engine: Engine = create_engine(connUrl, echo=False)
eng_autocommit = db_engine.execution_options(isolation_level="AUTOCOMMIT")
app_logger.info(f"db_engine: {db_engine}")
# https://docs.sqlalchemy.org/en/20/orm/session_basics.html
# Session Basics
sess_factory = sessionmaker(bind=eng_autocommit)
app_logger.info(f"sess_factory: {sess_factory}")
# Sessionクラスは sqlalchemy.orm.scoping.scoped_session
Cls_sess: scoping.scoped_session = scoped_session(sess_factory)
app_logger.info(f"Session class: {Cls_sess}")
# ★ ここまでが scoped_sessionクラスオブジェクトの生成処理
curr_df: Optional[DataFrame]
prev_df: Optional[DataFrame]
prev_year_month: Optional[str]
curr_df, prev_df, prev_year_month = get_all_df(
Cls_sess, args.device_name, param_year_month, logger=app_logger)
if curr_df is not None and prev_df is not None:
# レコード有りの場合の処理は省略
else:
app_logger.warning("該当レコードなし")
except Exception as err:
app_logger.warning(err)
exit(1)
- セッション(scoped_sessionのクラスオブジェクト)のクリーンアップ
finally:
if Cls_sess is not None:
Cls_sess.remove()
5-2-4. スクリプトの実行とログ出力
- 生成されるHTMLは [5-1-4. スクリプトメイン処理] 同一になります
※ Raspi-zero (SQLiteデータベース) と Raspi-4 (PostgreSQL) は同一データを受信している
(py_visualize) $ python PlotWeatherCompPrevYear_sqlalchemy.py \
--device-name esp8266_1 --year-month 2023-08 --db-host raspi-4
INFO connUrl: postgresql+psycopg2://developer:***@raspi-4.local:5432/sensors_pgdb
INFO db_engine: Engine(postgresql+psycopg2://developer:***@raspi-4.local:5432/sensors_pgdb)
INFO sess_factory: sessionmaker(class_='Session', bind=Engine(postgresql+psycopg2://developer:***@raspi-4.local:5432/sensors_pgdb), autoflush=True, expire_on_commit=True)
INFO Session class: <sqlalchemy.orm.scoping.scoped_session object at 0x7f6611f45300>
INFO scoped_sess: <sqlalchemy.orm.session.Session object at 0x7f6611f45360>
INFO query_params: {'deviceName': 'esp8266_1', 'fromDate': '2023-08-01', 'toDate': '2023-09-01'}
INFO measurement_time temp_out humid pressure
0 2023-08-01 00:07:04 21.6 58.7 1006.8
1 2023-08-01 00:16:48 21.7 59.1 1006.8
2 2023-08-01 00:26:33 21.8 58.7 1006.9
3 2023-08-01 00:36:17 21.7 58.5 1007.0
4 2023-08-01 00:46:01 21.4 59.5 1007.0
... ... ... ... ...
4579 2023-08-31 23:20:51 24.5 74.6 1008.5
4580 2023-08-31 23:30:36 24.5 74.6 1008.5
4581 2023-08-31 23:40:20 24.3 75.9 1008.4
4582 2023-08-31 23:50:04 24.2 76.7 1008.3
4583 2023-08-31 23:59:48 24.2 77.0 1008.1
[4584 rows x 4 columns]
INFO query_params: {'deviceName': 'esp8266_1', 'fromDate': '2022-08-01', 'toDate': '2022-09-01'}
INFO measurement_time temp_out humid pressure
0 2022-08-01 00:01:44 24.4 70.1 1008.9
1 2022-08-01 00:11:29 24.0 69.8 1008.7
2 2022-08-01 00:21:13 24.0 69.3 1008.7
3 2022-08-01 00:30:58 23.9 69.2 1008.9
4 2022-08-01 00:40:42 23.8 68.9 1009.0
... ... ... ... ...
4574 2022-08-31 23:20:20 17.3 65.1 1002.0
4575 2022-08-31 23:30:04 17.4 65.3 1002.1
4576 2022-08-31 23:39:48 17.4 65.7 1002.1
4577 2022-08-31 23:49:33 17.4 65.5 1002.4
4578 2022-08-31 23:59:16 17.4 65.5 1002.4
[4579 rows x 4 columns]
DEBUG measurement_time temp_out humid pressure prev_plot_measurement_time
0 2022-08-01 00:01:44 24.4 70.1 1008.9 2023-08-01 00:01:44
1 2022-08-01 00:11:29 24.0 69.8 1008.7 2023-08-01 00:11:29
2 2022-08-01 00:21:13 24.0 69.3 1008.7 2023-08-01 00:21:13
3 2022-08-01 00:30:58 23.9 69.2 1008.9 2023-08-01 00:30:58
4 2022-08-01 00:40:42 23.8 68.9 1009.0 2023-08-01 00:40:42
... ... ... ... ... ...
4574 2022-08-31 23:20:20 17.3 65.1 1002.0 2023-08-31 23:20:20
4575 2022-08-31 23:30:04 17.4 65.3 1002.1 2023-08-31 23:30:04
4576 2022-08-31 23:39:48 17.4 65.7 1002.1 2023-08-31 23:39:48
4577 2022-08-31 23:49:33 17.4 65.5 1002.4 2023-08-31 23:49:33
4578 2022-08-31 23:59:16 17.4 65.5 1002.4 2023-08-31 23:59:16
[4579 rows x 5 columns]
INFO fig: Figure(980x640)
DEBUG data.len: 261568
INFO output/PlotWeatherCompPrevYear_sqlalchemy.html
5-3. PostgreSQLデータベース で psycopg2ライブラリのみを使用する場合
ここから説明するスクリプトのソースコードは下記GitHubリポジトリをご覧ください
GitHub: matplotlib_knowhow: pandas-read_sql: PlotWeatherCompPrevYear_read_sql_psycopg2.py
[補足情報]
SQLAlchemyライブラリを使用しない場合、基本的に pandas.read_sql() は使用できないため下記のように工夫します。
- SQLクエリーを Python Database API 処理し、レコードを StringIO オブジェクト(CSVカンマ形式) としてメモリに保持する
- pandas.read_csv() 関数に StringIOオブジェクトを渡してDataFrameを取得する
※1 基本的にと書いたのは実行してもエラーにならないからです。ただし下記のようにウォーニングが標準出力に吐き出されます。 処理実行のたびに出力されるので大量のログがアプリケーションログに出力されます。
※2 バージョンアップ時にサポートされなくなる可能性も有りますので、pandasライブラリで推奨しない実装は採用すべきではありません。
※3 「psycopg2ライブラリだけでDataFrameが取得できます」といった日本語のサイトを見かけましたので注意が必要です。
[ソースのパス]/PlotWeatherCompPrevYear_read_sql_psycopg2.py:184: UserWarning:
pandas only supports SQLAlchemy connectable (engine/connection) or
database string URI or sqlite3 DBAPI2 connection.
Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
df: DataFrame = pd.read_sql(
-
選択クエリー(QUERY_RANGE_DATA)
※ 5-2 の選択クエリーと同なじなので省略 -
psycopg2データベース接続オブジェクトの生成
(psycopg2.connect()関数 については https://www.psycopg.org/docs/extras.html#index-3 を参照 [psycopg2.extras – Miscellaneous goodies for Psycopg 2])
(psycopg2.connect()関数のサンプルは https://www.postgresqltutorial.com/postgresql-python/connect/ を参考にしました [PostgreSQL Python: Connect To PostgreSQL Database Server])
conn = psycopg2.connect(
host="localhost",
database="suppliers",
user="postgres",
password="Abcd1234")
- PostgreSQLサーバー接続オブジェクトを生成用設定ファイル(conf/db_sensors_psycopg.json)
※ {hostname} 部分はあとから実際に接続するホスト名に置き換えられます。
{
"host": "{hostname}.local",
"port": "5432",
"database": "sensors_pgdb",
"user": "developer",
"password": "yourpasswd"
}
- PostgreSQLデータベース取得用クラス定義
class PgDatabase(object):
def __init__(self, conf_path: str, hostname: str = None, logger: logging.Logger = None):
self.logger = logger
with open(conf_path, 'r') as fp:
db_conf = json.load(fp)
if hostname is None:
hostname = socket.gethostname()
db_conf["host"] = db_conf["host"].format(hostname=hostname)
# default connection is itarable curosr
self.conn = psycopg2.connect(**db_conf)
# Dictinaly-like cursor connection.
# self.conn = psycopg2.connect(**dbconf, cursor_factory=psycopg2.extras.DictCursor)
if self.logger is not None:
self.logger.debug(self.conn)
def get_connection(self):
return self.conn
def close(self):
if self.conn is not None:
if self.logger is not None:
self.logger.debug(f"Close {self.conn}")
self.conn.close()
- WeatherDAOクラスの定義と月間データ取得メソッドの接続処理
※ getMonthData()メソッドは同一接続部複数回の呼び出しを想定しているので connection.close()は実行しません。
class WeatherDao:
def __init__(self, conn: connection, logger: Optional[logging.Logger] = None):
self.conn = conn
self.logger = logger
def getMonthData(self,
device_name: str,
year_month: str,
) -> Tuple[int, Optional[StringIO]]:
from_date: str = year_month + "-01"
exclude_to_date = next_year_month(from_date)
query_params: Dict = {
'deviceName': device_name, 'fromDate': from_date, 'toDate': exclude_to_date
}
with self.conn.cursor() as cursor:
cursor.execute(QUERY_RANGE_DATA, query_params)
tuple_list = cursor.fetchall()
record_count: int = len(tuple_list)
if self.logger is not None:
self.logger.debug(f"tuple_list.size {record_count}")
if record_count == 0:
return 0, None
return record_count, _csv_to_stringio(tuple_list)
(with conn: コンテキスト については https://www.psycopg.org/docs/connection.html#connection.cursor_factory を参照 [The connection class])
5-3-1(1) 指定した年月のデータ取得(Dataframe)関数
- データベースから取得した全レコードをCSV形式でStringIOバッファに蓄積する関数の定義
※Tupleのリストになります [ (測定時刻1,外気温1,室内湿度1,気圧1,), (2レコード目), ...]
# 取得カラム: 測定時刻,外気温,湿度,気圧
COL_TIME: str = "measurement_time"
COL_TEMP_OUT: str = "temp_out"
COL_HUMID: str = "humid"
COL_PRESSURE: str = "pressure"
HEADER_WEATHER: str = f'"{COL_TIME}","{COL_TEMP_OUT}","{COL_HUMID}","{COL_PRESSURE}"'
# ...
def _csv_to_stringio(tuple_list: List[Tuple[str, float, float, float]]) -> StringIO:
str_buffer = StringIO()
# ヘッダー出力
str_buffer.write(HEADER_WEATHER + "\n")
# レコード出力
for (m_time, temp_out, humid, pressure) in tuple_list:
line = f'"{m_time}",{temp_out},{humid},{pressure}\n'
str_buffer.write(line)
# StringIO need Set first position
str_buffer.seek(0)
return str_buffer
- DataFrameの生成に pandas.read_csv() 関数を使用する
※read_csv関数では CSVファイルの他 StringIOオブジェクト (下記Parameters)も受け入れます- daoオブジェクトのgetManthData()メソッドで取得した全レコードをCSVバッファに蓄積
- pandas.read_csv()関数にCSVバッファを設定しDataFrameを生成する
(pandas.read_csv関数は https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html を参照 [API dodument: pandas.read_csv])
Parameters:
filepath_or_buffer : str, path object or file-like object
# ...一部省略...
If you want to pass in a path object, pandas accepts any os.PathLike.
By file-like object, we refer to objects with a read() method,
such as a file handle (e.g. via builtin open function) or StringIO.
(StringIOをpandas.read_csvにセットする実装は https://stackoverflow.com/questions/34447448/stringio-and-pandas-read-csv を参考にしました [Stackoverflow: StringIO and pandas read_csv])
def get_dataframe(dao: WeatherDao,
device_name: str, year_month: str,
logger: Optional[logging.Logger] = None) -> Optional[pd.DataFrame]:
record_count: int
csv_buffer: StringIO
record_count, csv_buffer = dao.getMonthData(device_name, year_month)
if logger is not None:
logger.info(f"{device_name}[{year_month}]: {record_count}")
# 件数なし
if record_count == 0:
return None
df: DataFrame = pd.read_csv(
csv_buffer,
header=0,
parse_dates=[COL_TIME],
names=[COL_TIME, COL_TEMP_OUT, COL_HUMID, COL_PRESSURE]
)
if logger is not None:
logger.info(f"{df}")
return df
5-3-1(2) 指定した年月と前年の年月のDataFrame取得関数
def get_all_df(conn: connection,
device_name: str, curr_year_month,
logger: Optional[logging.Logger] = None
) -> Tuple[Optional[DataFrame], Optional[DataFrame], Optional[str]]:
dao = WeatherDao(conn, logger=logger)
try:
# 今年の年月テータ取得
df_curr: Optional[pd.DataFrame] = get_dataframe(
dao, device_name, curr_year_month, logger=logger)
if df_curr is None:
return None, None, None
# 前年の年月テータ取得
# 前年計算
prev_ym: str = previous_year_month(curr_year_month)
df_prev: Optional[DataFrame] = get_dataframe(
dao, device_name, prev_ym, logger=logger)
return df_curr, df_prev, prev_ym
except Exception as err:
logger.warning(err)
raise err
5-3-2. スクリプトメイン処理
※1 パラメータ入力部分は 5-2-3 のSQLAlchmy版と同一のため割愛します
※2 両方のDataFrameがある場合の画像生成は5-1-3の実装と同一のため割愛します
- データベースオブジェクト生成・DataFame生成・画像HTML保存
※内部でデータベース接続オブジェクトを保持
# database
db: Optional[PgDatabase] = None
try:
db = PgDatabase(DB_CONF, args.db_host, logger=app_logger)
db_conn: connection = db.get_connection()
curr_df: Optional[DataFrame]
prev_df: Optional[DataFrame]
prev_year_month: Optional[str]
curr_df, prev_df, prev_year_month = get_all_df(
db_conn, args.device_name, param_year_month, logger=app_logger)
if curr_df is not None and prev_df is not None:
# レコード有りの場合の処理は省略
else:
app_logger.warning("該当レコードなし")
except psycopg2.Error as db_err:
app_logger.error(f"type({type(db_err)}): {db_err}")
exit(1)
except Exception as exp:
app_logger.error(exp)
exit(1)
- データベース接続のクリーアップ
※内部で保持しているデータベース接続オブジェクトをクローズ
finally:
if db is not None:
db.close()
5-3-2. スクリプトの実行とログ出力
5-2-3 の DataFrameの出力はSQLAlchemy版と同一です ※Raspi-4サーバーを参照
(py_visualize) $ python PlotWeatherCompPrevYear_psycopg2.py \
--device-name esp8266_1 --year-month 2023-08 --db-host raspi-4
DEBUG <connection object at 0x7f6444e64cc0;
dsn: 'user=developer password=xxx dbname=sensors_pgdb host=raspi-4.local port=5432', closed: 0>
DEBUG tuple_list.size 4584
INFO esp8266_1[2023-08]: 4584
INFO measurement_time temp_out humid pressure
0 2023-08-01 00:07:04 21.6 58.7 1006.8
1 2023-08-01 00:16:48 21.7 59.1 1006.8
2 2023-08-01 00:26:33 21.8 58.7 1006.9
3 2023-08-01 00:36:17 21.7 58.5 1007.0
4 2023-08-01 00:46:01 21.4 59.5 1007.0
... ... ... ... ...
4579 2023-08-31 23:20:51 24.5 74.6 1008.5
4580 2023-08-31 23:30:36 24.5 74.6 1008.5
4581 2023-08-31 23:40:20 24.3 75.9 1008.4
4582 2023-08-31 23:50:04 24.2 76.7 1008.3
4583 2023-08-31 23:59:48 24.2 77.0 1008.1
[4584 rows x 4 columns]
DEBUG tuple_list.size 4579
INFO esp8266_1[2022-08]: 4579
INFO measurement_time temp_out humid pressure
0 2022-08-01 00:01:44 24.4 70.1 1008.9
1 2022-08-01 00:11:29 24.0 69.8 1008.7
2 2022-08-01 00:21:13 24.0 69.3 1008.7
3 2022-08-01 00:30:58 23.9 69.2 1008.9
4 2022-08-01 00:40:42 23.8 68.9 1009.0
... ... ... ... ...
4574 2022-08-31 23:20:20 17.3 65.1 1002.0
4575 2022-08-31 23:30:04 17.4 65.3 1002.1
4576 2022-08-31 23:39:48 17.4 65.7 1002.1
4577 2022-08-31 23:49:33 17.4 65.5 1002.4
4578 2022-08-31 23:59:16 17.4 65.5 1002.4
[4579 rows x 4 columns]
DEBUG measurement_time temp_out humid pressure prev_plot_measurement_time
0 2022-08-01 00:01:44 24.4 70.1 1008.9 2023-08-01 00:01:44
1 2022-08-01 00:11:29 24.0 69.8 1008.7 2023-08-01 00:11:29
2 2022-08-01 00:21:13 24.0 69.3 1008.7 2023-08-01 00:21:13
3 2022-08-01 00:30:58 23.9 69.2 1008.9 2023-08-01 00:30:58
4 2022-08-01 00:40:42 23.8 68.9 1009.0 2023-08-01 00:40:42
... ... ... ... ... ...
4574 2022-08-31 23:20:20 17.3 65.1 1002.0 2023-08-31 23:20:20
4575 2022-08-31 23:30:04 17.4 65.3 1002.1 2023-08-31 23:30:04
4576 2022-08-31 23:39:48 17.4 65.7 1002.1 2023-08-31 23:39:48
4577 2022-08-31 23:49:33 17.4 65.5 1002.4 2023-08-31 23:49:33
4578 2022-08-31 23:59:16 17.4 65.5 1002.4 2023-08-31 23:59:16
[4579 rows x 5 columns]
INFO fig: Figure(980x640)
DEBUG data.len: 261568
INFO output/PlotWeatherCompPrevYear_psycopg2.html
DEBUG Close <connection object at 0x7f6444e64cc0;
dsn: 'user=developer password=xxx dbname=sensors_pgdb host=raspi-4.local port=5432', closed: 0>
以上 SQLite3データベースとPostgreSQLデータベースの処理の違いを比較しました。
SQLite3データベースのテーブル定義では時間フィールドの型と格納するデータの種類には要注意です。かなりハマりました。
全てのソースコードとSQLite3データベースファイル(サンプル用)、実行ログ・出力HTMLは下記GitHubリポジトリで公開しています。