2024年4月7日 / 最終更新日時 : 2024年4月7日 reafnexadmin 気象

PostgreSQLデータベースを検索する

この記事は、SDGsの目標１３「気候変動に具体的な対策を」をテーマに、気象データの利用方法の普及と気象関連ソリューション開発を促進するためのナレッジを提供することを目的として作成しています。

今回は、PostgreSQLデータベースに登録されているアメダス観測データをPythonで検索する方法について解説していきます。

PostgreSQLデータベースにアメダス観測データを登録する方法については、以下で詳しく解説していますので参考にしてください。

PostgreSQLにデータをインポートする

PostgreSQLデータベースを検索する

今回使用する環境については、以下のとおりです。

ＯＳAlmaLinux release 9.3 (Shamrock Pampas Cat)
データベースpsql (PostgreSQL) 15.6
Pythonpip 20.2.4
Python 3.12.1
環境一覧

Pythonのモジュールをインストールする

初めにPythonの仮想環境を作成します。

[workuser@devsrv01 ~]$ python3.12 -m venv ~/venv/test_env01
[workuser@devsrv01 ~]$ ls ~/venv
test_env01

作成したPythonの仮想環境を活性化します。

[workuser@devsrv01 ~]$ source ~/venv/test_env01/bin/activate
(test_env01) [workuser@devsrv01 ~]$

活性化したPythonの仮想環境に必要なモジュールをインストールしていきます。pipのアップグレードについて示唆されていますが、今回は無視します。

(test_env01) [workuser@devsrv01 ~]$ pip3.12 install psycopg2-binary pandas

・・・<途中省略>・・・

[notice] A new release of pip is available: 23.2.1 -> 24.0
[notice] To update, run: pip install --upgrade pip

Pythonの仮想環境にインストールしたモジュールのリストを表示します。

(test_env01) [workuser@devsrv01 ~]$ pip3.12 list
Package         Version
--------------- -----------
numpy           1.26.4
pandas          2.2.1
pip             23.2.1
psycopg2-binary 2.9.9
python-dateutil 2.9.0.post0
pytz            2024.1
six             1.16.0
tzdata          2024.1

python-binaryは、PostgreSQLデータベースに接続して操作するためのPythonモジュールです。また、今回はデータベースから検索したデータをデータフレームに格納するので、pandasもインストールしておきます。

コードを作成する

環境の準備ができたらコードを書いていきます。今回のコードの全体は、以下のとおりです。

import psycopg2
import pandas as pd
import os

# PostgreSQL接続情報
ip_add    = '192.168.11.220'
db_name   = 'test_database'
user_name = 'workuser'

# 環境変数からパスワードを取得する
authstr = os.environ.get('DB_PASSWORD')

# データベース接続文字列の作成
dsn = f"host={ip_add} dbname={db_name} user={user_name} password={authstr}"

#初期化
conn = None

# データベース操作
try:
    # データベースに接続する
    conn = psycopg2.connect(dsn)
    print("データベースに接続しました。")

    # DBインターフェースのインスタンスを作成する
    cur = conn.cursor()

    # DBインターフェースを介してSQLを実行する
    cur.execute('SELECT * FROM obsdata.amedas')

    # DBインターフェースを介してカラム名を取得する
    cols = [col.name for col in cur.description]

    # 検索した全データをPandasデータフレームに格納する
    df = pd.DataFrame(cur.fetchall(), columns = cols)

except psycopg2.Error as e:
    print("データベース接続エラー:", e)

finally:
    # 接続を閉じる
    if conn is not None:
        conn.close()
        print("接続を閉じました。")

print(df)

作成したコードの内容についてポイントを解説します。

まず初めに、PostgreSQLデータベースを操作するためのpsycopg2、データフレームのpandasとosをインポートします。

import psycopg2
import pandas as pd
import os

次に、PostgreSQLデータベースの接続情報を設定します。データベース接続には、接続先、データベース名、ユーザ名とパスワードが必要です。

パスワードは、セキュリティの観点から環境変数に設定したものを利用するようにします。

設定した接続先、データベース名、ユーザ名とパスワードからデータベース接続文字列を作成します。

# PostgreSQL接続情報
ip_add    = '192.168.11.220'
db_name   = 'test_database'
user_name = 'workuser'

# 環境変数からパスワードを取得する
authstr = os.environ.get('DB_PASSWORD')

# データベース接続文字列の作成
dsn = f"host={ip_add} dbname={db_name} user={user_name} password={authstr}"

データベース接続で例外処理が発生した場合に対応するため、try-except構文で記述します。

PostgreSQLデータベースへの接続はpsycopg2.connect()にデータベース接続文字列dsnを渡してセッションを開始します。

データベースの操作は、cursorと呼ばれるインターフェースにより実行されます。

データベースのセッションconnにカーソルcursorを結合して、DBインターフェースのインスタンスcurを作成します。

cur.execute()にSQL文を渡して処理を実行します。検索結果は、cur.fetchall()で取得することができます。cur.fetchall()で取得した検索結果を、Pandasのデータフレームに入力しています。

#初期化
conn = None

# データベース操作
try:
    # データベースに接続する
    conn = psycopg2.connect(dsn)
    print("データベースに接続しました。")

    # DBインターフェースのインスタンスを作成する
    cur = conn.cursor()

    # DBインターフェースを介してSQLを実行する
    cur.execute('SELECT * FROM obsdata.amedas')

    # DBインターフェースを介してカラム名を取得する
    cols = [col.name for col in cur.description]

    # 検索した全データをPandasデータフレームに格納する
    df = pd.DataFrame(cur.fetchall(), columns = cols)

次に、例外処理が発生したときの処理を記述します。以下の例では、エラーメッセージを出力させています。

これにより、データベース接続や処理で例外処理が発生した場合でも、スクリプトは正常に終了するようになります。

except psycopg2.Error as e:
    print("データベース接続エラー:", e)

最後に、すべての処理が終わったときに共通で実行する処理を記述します。以下の例では、データベースとのセッションが残っている場合は切断する処理を行っています。

finally:
    # 接続を閉じる
    if conn is not None:
        conn.close()
        print("接続を閉じました。")

データベースを検索してみる

では、環境変数にパスワードを設定したのち、作成したスクリプトでデータベース検索してみます。

(test_env01) [workuser@devsrv01 PostgreSQL]$ export DB_PASSWORD=********
(test_env01) [workuser@devsrv01 PostgreSQL]$ python3.12 main.py
データベースに接続しました。
接続を閉じました。
                   ymdi tempc precip  prec_flag wspd wdir dewpc relhum
0   2024-02-01 01:00:00   9.3      0          1  1.1    北   1.9     60
1   2024-02-01 02:00:00   8.9      0          0  1.5   北西   2.3     63
2   2024-02-01 03:00:00   8.9      0          0  2.5  北北西   0.9     57
3   2024-02-01 04:00:00   8.6      0          0  2.2  北北西   1.3     60
4   2024-02-01 05:00:00   8.2      0          0  1.3    北   1.6     63
..                  ...   ...    ...        ...  ...  ...   ...    ...
691 2024-02-29 20:00:00   8.3      0          0  2.8   北東   5.9     85
692 2024-02-29 21:00:00   7.6    0.5          0  1.2  北北東     7     96
693 2024-02-29 22:00:00   7.2      1          0    2  東北東   6.6     96
694 2024-02-29 23:00:00   7.2    0.5          0  2.2    北   6.6     96
695 2024-03-01 00:00:00     7      2          0  3.7   北東   6.7     98

[696 rows x 8 columns]

アメダス観測データの検索結果がPandasデータフレームで表示されました。

まとめ

いかがでしたでしょうか。

今回は、PythonでPostgreSQLデータベースからデータを検索し、その結果をPandasデータフレームに代入する手順について解説しました。

気象データサイエンスの現場において、データベースを活用した処理は避けて通ることはできないくらい重要かつ基本的な事項となります。

表計算ソフトでもある程度の処理はできますが、大規模なデータを扱うとなれば限界があります。

データベースを利用することで、大規模なデータの集合体から必要な情報を効率的に抽出することができ、さらにリアルタイムでデータベースに記録されるデータにも機動的に対応することができます。

気象データサイエンティストの基本テクニックとして、参考になれば幸いです。

気象データ利用のお悩みについてご相談ください

気象データの利用に関するお悩みは、こちらからお問い合わせください。
気象システムの設計製造及び運用保守にかかわる現役システムエンジニアが、気象予報士の視点で気象データを活用したビジネスアイディアの創出と課題解決のお手伝いをさせていただきます。
気象や波浪などの数値予報モデルを活用した気象予報システム開発や運用についても是非ご相談ください。
問い合わせはこちら
カテゴリー
気象、気象データサイエンス
ＩＴ関連

前の記事

PostgreSQLにデータをインポートする
2024年3月24日
FastAPI

次の記事

データベースを組み合わせたFastAPIの実装
2024年4月14日