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）

使用ライブラリ
ライブラリのインストール
PostgreSQLを準備する
ライブラリのインポート
レコードを挿入する（INSERT）
複数レコードを一括で挿入する（INSERT）
データを取得する（SELECT）
データの更新（UPDATE）
データの削除（DELETE）

使用ライブラリ

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の使い方)

python -m pip install psycopg2

1	python -m pip install psycopg2

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

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

python -m pip install psycopg2-binary

1	python -m pip install psycopg2-binary

PostgreSQLを準備する

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

PostgreSQLのインストール（Windows）

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

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

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

データベースを作成する

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

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

C:\>psql -h localhost -p 5432 -U postgres

1	C:\>psql -h localhost -p 5432 -U postgres

または

C:\>psql -U postgres

1	C:\>psql -U postgres

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

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

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

postgres=# create database mydb;
CREATE DATABASE

1 2	postgres=# create database mydb; CREATE DATABASE

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

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

テーブルを作成する

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

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

postgres=# ¥c mydb
データベース"mydb"にユーザ"postgres"として接続しました。

1 2	postgres=# ¥c mydb データベース"mydb"にユーザ"postgres"として接続しました。

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

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

CREATE TABLE users (
id SERIAL,
email varchar(255) NOT NULL,
password varchar(255) NOT NULL,
PRIMARY KEY (id)
) ;

CREATE TABLE users (

id SERIAL,

email varchar(255) NOT NULL,

password varchar(255) NOT NULL,

PRIMARY KEY (id)

) ;

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

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

mydb=# \dt
            リレーション一覧
 スキーマ | 名前  |  タイプ  |  所有者
----------+-------+----------+----------
 public   | users | テーブル | postgres
(1 行)

mydb=# \dt

リレーション一覧

スキーマ | 名前 | タイプ | 所有者

----------+-------+----------+----------

public | users | テーブル | postgres

(1 行)

ライブラリのインポート

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

import psycopg2

1	import psycopg2

レコードを挿入する（INSERT）

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

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

import psycopg2

# データベースに接続
connection = psycopg2.connect(host='localhost',
                              user='postgres',
                              password='PostgreSQLのパスワード',
                              database='mydb')

with connection:
    with connection.cursor() as cursor:
        # レコードを挿入
        sql = "INSERT INTO users (email, password) VALUES (%s, %s)"
        cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

    # コミットしてトランザクション実行
    connection.commit()

import psycopg2

# データベースに接続

connection = psycopg2.connect(host='localhost',

user='postgres',

password='PostgreSQLのパスワード',

database='mydb')

with connection:

with connection.cursor() as cursor:

# レコードを挿入

sql = "INSERT INTO users (email, password) VALUES (%s, %s)"

cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

# コミットしてトランザクション実行

connection.commit()

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

mydb=# select * from users;
 id |        email         |  password
----+----------------------+-------------
  1 | webmaster@python.org | very-secret
(1 行)

mydb=# select * from users;

id | email | password

----+----------------------+-------------

1 | webmaster@python.org | very-secret

(1 行)

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

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

# レコードを挿入
cursor = connection.cursor()
sql = "INSERT INTO users (email, password) VALUES (%s, %s)"
cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

# コミットしてトランザクション実行
connection.commit()

# 終了処理
cursor.close()
connection.close()

# レコードを挿入

cursor = connection.cursor()

sql = "INSERT INTO users (email, password) VALUES (%s, %s)"

cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

# コミットしてトランザクション実行

connection.commit()

# 終了処理

cursor.close()

connection.close()

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

postgres=# TRUNCATE users;
TRUNCATE TABLE

1 2	postgres=# TRUNCATE users; TRUNCATE TABLE

複数レコードを一括で挿入する（INSERT）

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

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

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

import psycopg2

# データベースに接続
connection = psycopg2.connect(host='localhost',
                             user='postgres',
                             password='PostgreSQLのパスワード',
                             database='mydb')

values = [['webmaster1@python.org', 'very-secret1'],
          ['webmaster2@python.org', 'very-secret2'],
          ['webmaster3@python.org', 'very-secret3'],
          ['webmaster4@python.org', 'very-secret4'],
          ['webmaster5@python.org', 'very-secret5']]

with connection:
    with connection.cursor() as cursor:
        # レコードを挿入
        sql = "INSERT INTO users (email, password) VALUES (%s, %s)"
        cursor.executemany(sql, values)

    # コミットしてトランザクション実行
    connection.commit()

# 終了処理
cursor.close()

import psycopg2

# データベースに接続

connection = psycopg2.connect(host='localhost',

user='postgres',

password='PostgreSQLのパスワード',

database='mydb')

values = [['webmaster1@python.org', 'very-secret1'],

['webmaster2@python.org', 'very-secret2'],

['webmaster3@python.org', 'very-secret3'],

['webmaster4@python.org', 'very-secret4'],

['webmaster5@python.org', 'very-secret5']]

with connection:

with connection.cursor() as cursor:

# レコードを挿入

sql = "INSERT INTO users (email, password) VALUES (%s, %s)"

cursor.executemany(sql, values)

# コミットしてトランザクション実行

connection.commit()

# 終了処理

cursor.close()

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

mydb=# select * from users;
 id |         email         |   password
----+-----------------------+--------------
  3 | webmaster1@python.org | very-secret1
  4 | webmaster2@python.org | very-secret2
  5 | webmaster3@python.org | very-secret3
  6 | webmaster4@python.org | very-secret4
  7 | webmaster5@python.org | very-secret5
(5 行)

mydb=# select * from users;

id | email | password

----+-----------------------+--------------

3 | webmaster1@python.org | very-secret1

4 | webmaster2@python.org | very-secret2

5 | webmaster3@python.org | very-secret3

6 | webmaster4@python.org | very-secret4

7 | webmaster5@python.org | very-secret5

(5 行)

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

データを取得する（SELECT）

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

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

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

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

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

import psycopg2

# データベースに接続
connection = psycopg2.connect(host='localhost',
                             user='postgres',
                             password='PostgreSQLのパスワード',
                             database='mydb')
with connection:
    with connection.cursor() as cursor:
        # データ読み込み
        sql = "SELECT id, password FROM users WHERE email = %s"
        cursor.execute(sql, ['webmaster1@python.org'])
        result = cursor.fetchone()
        print(result)

import psycopg2

# データベースに接続

connection = psycopg2.connect(host='localhost',

user='postgres',

password='PostgreSQLのパスワード',

database='mydb')

with connection:

with connection.cursor() as cursor:

# データ読み込み

sql = "SELECT id, password FROM users WHERE email = %s"

cursor.execute(sql, ['webmaster1@python.org'])

result = cursor.fetchone()

print(result)

取得結果はタプルになります。

(3, ‘very-secret1’)

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

# データ読み込み
cursor = connection.cursor()
sql = "SELECT id, password FROM users WHERE email=%s"
cursor.execute(sql, ['webmaster1@python.org'])
result = cursor.fetchone()
print(result)

# 終了処理
cursor.close()
connection.close()

# データ読み込み

cursor = connection.cursor()

sql = "SELECT id, password FROM users WHERE email=%s"

cursor.execute(sql, ['webmaster1@python.org'])

result = cursor.fetchone()

print(result)

# 終了処理

cursor.close()

connection.close()

全データを取得する

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

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

import psycopg2

# データベースに接続
connection = psycopg2.connect(host='localhost',
                             user='postgres',
                             password='PostgreSQLのパスワード',
                             database='mydb')
with connection:
    with connection.cursor() as cursor:
        # データ読み込み
        sql = "SELECT id, password FROM users"
        cursor.execute(sql)
        result = cursor.fetchall()
        print(result)

import psycopg2

# データベースに接続

connection = psycopg2.connect(host='localhost',

user='postgres',

password='PostgreSQLのパスワード',

database='mydb')

with connection:

with connection.cursor() as cursor:

# データ読み込み

sql = "SELECT id, password FROM users"

cursor.execute(sql)

result = cursor.fetchall()

print(result)

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

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

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

cursor = connection.cursor()
sql = "SELECT id, password FROM users"
cursor.execute(sql)
result = cursor.fetchall()
print(result)


# 接続を切断
cursor.close()
connection.close()

cursor = connection.cursor()

sql = "SELECT id, password FROM users"

cursor.execute(sql)

result = cursor.fetchall()

print(result)

# 接続を切断

cursor.close()

connection.close()

辞書型で取得する

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

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

import psycopg2
from psycopg2.extras import DictCursor

# データベースに接続
connection = psycopg2.connect(host='localhost',
                             user='postgres',
                             password='PostgreSQLのパスワード',
                             database='mydb')

dict_cur = connection.cursor(cursor_factory=DictCursor)

with connection:
    with dict_cur:
        # データ読み込み
        sql = "SELECT id, password FROM users"
        dict_cur.execute(sql)
        result = dict_cur.fetchall()
        print(result)

import psycopg2

from psycopg2.extras import DictCursor

# データベースに接続

connection = psycopg2.connect(host='localhost',

user='postgres',

password='PostgreSQLのパスワード',

database='mydb')

dict_cur = connection.cursor(cursor_factory=DictCursor)

with connection:

with dict_cur:

# データ読み込み

sql = "SELECT id, password FROM users"

dict_cur.execute(sql)

result = dict_cur.fetchall()

print(result)

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

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

データフレームに変換

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

import psycopg2
import pandas as pd

# データベースに接続
connection = psycopg2.connect(host='localhost',
                             user='postgres',
                             password='PostgreSQLのパスワード',
                             database='mydb')
with connection:
    with connection.cursor() as cursor:
        # データ読み込み
        sql = "SELECT * FROM users"
        cursor.execute(sql)
        df = pd.DataFrame(cursor.fetchall())

import psycopg2

import pandas as pd

# データベースに接続

connection = psycopg2.connect(host='localhost',

user='postgres',

password='PostgreSQLのパスワード',

database='mydb')

with connection:

with connection.cursor() as cursor:

# データ読み込み

sql = "SELECT * FROM users"

cursor.execute(sql)

df = pd.DataFrame(cursor.fetchall())

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

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

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

with connection:
    with connection.cursor() as cursor:
        cursor.execute('SELECT * FROM users')
        cols = [col.name for col in cursor.description]
        df = pd.DataFrame(cursor.fetchall(),columns = cols)

with connection:

with connection.cursor() as cursor:

cursor.execute('SELECT * FROM users')

cols = [col.name for col in cursor.description]

df = pd.DataFrame(cursor.fetchall(),columns = cols)

データの更新（UPDATE）

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

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

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

import psycopg2

# データベースに接続
connection = psycopg2.connect(host='localhost',
                             user='postgres',
                             password='PostgreSQLのパスワード',
                             database='mydb')
with connection:
    with connection.cursor() as cursor:
        # データ更新
        sql = "UPDATE users SET email = %s WHERE email = %s"
        cursor.execute(sql, ('master@python-work.com', 'webmaster1@python.org'))
        connection.commit()

import psycopg2

# データベースに接続

connection = psycopg2.connect(host='localhost',

user='postgres',

password='PostgreSQLのパスワード',

database='mydb')

with connection:

with connection.cursor() as cursor:

# データ更新

sql = "UPDATE users SET email = %s WHERE email = %s"

cursor.execute(sql, ('master@python-work.com', 'webmaster1@python.org'))

connection.commit()

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

mydb=# select * from users;
 id |         email          |   password
----+------------------------+--------------
  4 | webmaster2@python.org  | very-secret2
  5 | webmaster3@python.org  | very-secret3
  6 | webmaster4@python.org  | very-secret4
  7 | webmaster5@python.org  | very-secret5
  3 | master@python-work.com | very-secret1
(5 行)

mydb=# select * from users;

id | email | password

----+------------------------+--------------

4 | webmaster2@python.org | very-secret2

5 | webmaster3@python.org | very-secret3

6 | webmaster4@python.org | very-secret4

7 | webmaster5@python.org | very-secret5

3 | master@python-work.com | very-secret1

(5 行)

データの削除（DELETE）

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

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

import psycopg2

# データベースに接続
connection = psycopg2.connect(host='localhost',
                             user='postgres',
                             password='PostgreSQLのパスワード',
                             database='mydb')
with connection:
    with connection.cursor() as cursor:
        # データ削除
        sql = "DELETE FROM users WHERE email = %s"
        cursor.execute(sql, ('master@python-work.com'))
        connection.commit()
        print(cursor.rowcount, 'rows deleted')

import psycopg2

# データベースに接続

connection = psycopg2.connect(host='localhost',

user='postgres',

password='PostgreSQLのパスワード',

database='mydb')

with connection:

with connection.cursor() as cursor:

# データ削除

sql = "DELETE FROM users WHERE email = %s"

cursor.execute(sql, ('master@python-work.com'))

connection.commit()

print(cursor.rowcount, 'rows deleted')

1 rows deleted

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

mydb=# select * from users;
 id |         email         |   password
----+-----------------------+--------------
  4 | webmaster2@python.org | very-secret2
  5 | webmaster3@python.org | very-secret3
  6 | webmaster4@python.org | very-secret4
  7 | webmaster5@python.org | very-secret5
(4 行)

mydb=# select * from users;

id | email | password

----+-----------------------+--------------

4 | webmaster2@python.org | very-secret2

5 | webmaster3@python.org | very-secret3

6 | webmaster4@python.org | very-secret4

7 | webmaster5@python.org | very-secret5

(4 行)

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