PythonでPostgreSQLを操作する(psycopg2)

PythonでPostgreSQLを操作する(psycopg2)
スポンサーリンク
スポンサーリンク

ここではPythonでPostgreSQLを操作する方法を解説します。
PostgreSQLにデータを保存することで大規模なデータを簡単に扱うことができるようになります。

PostgreSQLはデータベース(DB)の一種で、リレーショナルデータベース(RDB)に分類されます。
DBの分類は他にNoSQL(ノーエスキューエル)があり、MongoDBやなどがNoSQLに分類されます。

RDBはトランザクションを重要視しておりデータの整合性を保つことができます。
一方でNoSQLはデータの整合性を弱める代わりに、スケーラビリティや読み書き性能を高めたデータベースです。

RDBのMySQLをPythonで扱う場合はこちらを参考にしてください。
・関連記事:PythonでMySQLを操作する(PyMySQL)
・関連記事:PythonでMySQLを操作する(mysql-connector-python)

NoSQLのMongoDBの扱い方はこちらにまとめています。
・関連記事:PythonでMongoDBを操作する(PyMongo)

スポンサーリンク

使用ライブラリ

PythonからPostgreSQLに接続するためのライブラリはいくつかありますが、ここでは psycopg2 (サイコピージー)を使用します。

 使用ライブラリ:psycopg2(公式ドキュメント
 インストール :必要 

PythonではPEP249 (DB-API 2.0)で、DB接続やSQLの実行、結果の取り出し方法などのAPIの仕様を定めています。多くのモジュールがこれに沿って実装されているため、どのライブラリを使用しても基本は同じメソッドで実行する事ができるようになっています。

ライブラリのインストール

2022年10月時点の最新バージョンは2.9.4で、要求仕様は以下となります。
・Python3.6~3.10
・PostgreSQL server 7.4 ~ 15
・PostgreSQL client library 9.1~

pip コマンドで psycopg2 ライブラリをインストールします。
・関連記事:Pythonライブラリのインストール(pipの使い方)

Successfully installed psycopg2-*.*.* と表示されればインストールは成功です。


インストールに失敗する場合は、以下のバイナリを利用する方法を試してみてください。

PostgreSQLを準備する

PostgreSQLの事前準備について解説します。
すでにPostgreSQLを導入していて、作成済のデータベースを利用する場合は飛ばしてください。

PostgreSQLのインストール(Windows)

PostgreSQLがインストールされていない場合はPCにインストールします。

公式サイトからWindows版のインストーラをダウンロードしてインストールします。

インストール手順の詳細はこちらの記事で解説しています。
・関連記事:【Windows11対応】PostgreSQLのインストール手順

データベースを作成する

SQL Shell (psql) を起動して、PostgreSQLサーバに接続します。

パスを通している場合は、コマンドプロンプトを開きPostgreSQLに接続します。
コマンドプロンプトで接続するには以下のように入力します。

または

「ユーザ postgres のパスワード: 」と表示されるので、インストール時に設定したパスワードを入力します。

下図のようにメッセージが表示され、PostgreSQLを操作できる状態になります。

まず、データベースを作成します。
create database データベース名; と入力し正常に処理されると、CREATE DATABASE と表示されます。

\l と入力するとデータベース一覧を表示する事ができます。

mydbが作成されている事が確認できます。

テーブルを作成する

次にテーブルを作成します。

先程作成したデータベースに接続するため、\c データベース名;と入力します。

FATAL: データベース"データベース名"は存在しませんと表示された場合は、指定したデータベース名が存在していないため、作成済のデータベース名を指定してください。

以下のクエリを入力してテーブルを作成します。
id は自動インクリメントを使用するため、SERIAL型を指定しています。(MySQLのようにAUTO_INCREMENTは指定できないためです)

CREATE TABLE とメッセージが表示されれば正常にテーブルが作成されています。

作成済のテーブルを確認する場合は、\dt で一覧を表示する事ができます。

ライブラリのインポート

psycopg2ライブラリをインポートします。

レコードを挿入する(INSERT)

レコードを挿入するにはcursorオブジェクトの execute メソッドでINSERTクエリを実行します。
クエリの文末にデリミタ(;)は不要です。

cursorオブジェクト.execute(INSERTクエリ) 

結果を確認するために、PostgreSQLで以下のようにselectクエリを実行してみます。

上記のようにテーブルが表示されれば、正常に登録されています。

with文を使わずに以下のように記述することも可能です。
ただしWith文を使用しない場合は、自動的に接続を閉じてくれないため close() メソッドで明示的に閉じる必要があります。


PostgreSQLでテーブルの全レコードを削除するには、以下のようにTRUNCATEクエリを実行します。

複数レコードを一括で挿入する(INSERT)

レコードを一括で挿入するには、cursorオブジェクトの executemany() メソッドでINSERTクエリを実行します。
第二引数に挿入するデータのリストもしくはタプルを指定します。

cursorオブジェクト.executemany(INSERTクエリ, 挿入データのリストまたはタプル)

複数レコードを一括で挿入するサンプルプログラムです。

結果を確認するために、MySQLで以下のようにselectクエリを実行してみます。

上記のようにテーブルが表示されれば、正常に登録されています。

データを取得する(SELECT)

SQL実行結果からデータを取得するには fetchメソッドを使用します。
fetchメソッドには、fetchone():1件ずつ取得, fetchall():全件取得, fetchmany():指定行数取得があります。

データを1件ずつ取得する

データを1件ずつ取得するには、cursorオブジェクトのexecuteメソッドでSQLのSELECTクエリを実行します。

cursorオブジェクト.execute(SELECTクエリ) 
cursorオブジェクト.fetchone()

以下の例は、WHERE句で条件を指定して1件文のレコードを抽出して、fetchone()でデータを取得しています。

取得結果はタプルになります。
(3, ‘very-secret1’)

with文を使わずに以下のように記述することもできます。

全データを取得する

複数レコードを取得するSELECTクエリを実行し、fetchall()で全データを取得します。

 cursorオブジェクト.execute(SELECTクエリ) 
 cursorオブジェクト.fetchall()

出力結果は要素がタプルのリストになります。

[(3, ‘very-secret1’), (4, ‘very-secret2’), (5, ‘very-secret3’), (6, ‘very-secret4’), (7, ‘very-secret5’)]

with文を使用しない方法は以下になります。

辞書型で取得する

辞書型で取得することもできます。

psycopg2.extrasDictCursor オブジェクトをインポートして、cursor()メソッドの引数にで cursor_factory パラメータに DictCursor を指定することで、辞書型で取得できるようになります。

取得結果は以下のようになります。

[[3, ‘very-secret1’], [4, ‘very-secret2’], [5, ‘very-secret3’], [6, ‘very-secret4’], [7, ‘very-secret5’]]

データフレームに変換

fetchall() で取得した結果はデータフレームに変換する事ができます。(pandas のインストール・インポートが必要です)
・参考記事:PandasでSeries, DataFrameを作成する

データフレームは以下のようになります。

データフレーム

列名を取得してデータフレームの列名に反映する

列名は cursor オブジェクトの description で取得できるので、これをデータフレームの columns プロパティに指定する事で、列名を付与する事ができます。

データフレーム+列名

データの更新(UPDATE)

登録済みのデータを更新するには、cursorオブジェクトの execute メソッドでUPDATEクエリを実行します。

cursorオブジェクト.execute(UPDATEクエリ)

次の例では、usersテーブル id=3 の email「webmaster1@python.org」を「master@python-work.com」に更新します。

PostgreSQLコンソールで email が変更されていることが確認できます。
更新されたデータは一番下に表示されます。

データの削除(DELETE)

データを削除するには、cursorオブジェクトの execute メソッドでDELETEクエリを実行します。
cursor.rowcount で削除した行数を取得できます。

cursorオブジェクト.execute(DELETEクエリ)
 1 rows deleted

PostgreSQLのコンソールでデータが削除されていることを確認できます。


PythonでPostgreSQLを操作する方法を解説しました。

・関連記事:【Windows11対応】PostgreSQLのインストール手順
・関連記事:PythonでMySQLを操作する(PyMySQL)
・関連記事:PythonでMySQLを操作する(mysql-connector-python)
・関連記事:PythonでMongoDBを操作する(PyMongo)