PythonでPostgreSQLを操作する方法！psycopg2の基本と実践

先生と生徒の会話形式で理解しよう

生徒

「先生、PythonからPostgreSQLというデータベースを使うことはできますか？」

先生

「もちろんです！Pythonにはpsycopg2という強力なライブラリがあって、PostgreSQLデータベースを簡単に操作できますよ。」

生徒

「PostgreSQLって、MySQLとは何が違うんですか？」

先生

「PostgreSQLは、より高度な機能を持つオープンソースのデータベースです。複雑なクエリや大規模なデータ処理に強く、企業のシステムでも広く使われています。それでは、psycopg2の使い方を詳しく学んでいきましょう！」

1. PostgreSQLとpsycopg2の基礎知識

PostgreSQL（ポストグレスキューエル）は、世界中で使われている高性能なオープンソースのリレーショナルデータベース管理システムです。ACID特性（原子性、一貫性、独立性、永続性）を完全にサポートし、データの信頼性が非常に高いことが特徴です。

psycopg2（サイコピージーツー）は、PythonからPostgreSQLデータベースに接続するための最も人気のあるライブラリです。C言語で実装されているため、高速で効率的な動作が可能です。

PostgreSQLの主な特徴

高度なSQL機能：複雑な結合やサブクエリに対応
拡張性：カスタムデータ型や関数を作成可能
JSON対応：NoSQLのようなドキュメント型データも扱える
並行処理：MVCC（多版型同時実行制御）により高速な同時アクセスを実現
豊富なデータ型：配列、範囲型、幾何学型など多彩なデータ型をサポート

補足：ACID特性とは、データベースのトランザクションが持つべき4つの性質のことです。これにより、銀行システムのような厳密なデータ管理が必要な場面でも安心して使えます。

2. psycopg2のインストール方法

psycopg2を使うには、まずライブラリをインストールする必要があります。ターミナルやコマンドプロンプトを開いて、以下のコマンドを実行してください。

pip install psycopg2-binary

psycopg2-binaryは、コンパイル済みのバイナリパッケージです。通常のpsycopg2よりも簡単にインストールできるため、初心者におすすめです。開発環境ではpsycopg2-binaryを、本番環境ではpsycopg2を使うのが一般的です。

インストールの確認

正しくインストールされたか確認するには、Pythonで以下のコードを実行してみましょう：


import psycopg2

print("psycopg2のバージョン:", psycopg2.__version__)
print("インストール成功！")

エラーが表示されなければ、psycopg2が正しくインストールされています。

注意：psycopg2を使う前に、PostgreSQLサーバーがインストールされている必要があります。PostgreSQLの公式サイトからダウンロードしてインストールしてください。

3. PostgreSQLデータベースへの接続

psycopg2を使ってPostgreSQLデータベースに接続する方法を見ていきましょう。接続には、ホスト名、データベース名、ユーザー名、パスワードなどの情報が必要です。


import psycopg2

# PostgreSQLデータベースに接続
connection = psycopg2.connect(
    host='localhost',          # データベースサーバーのホスト
    database='sample_db',      # 接続するデータベース名
    user='postgres',           # ユーザー名
    password='mypassword',     # パスワード
    port='5432'                # ポート番号（デフォルトは5432）
)

print("PostgreSQLデータベースに接続しました")
print(f"接続情報: {connection.get_dsn_parameters()}")

# 接続を閉じる
connection.close()
print("接続を閉じました")

psycopg2.connect()関数を使って、PostgreSQLサーバーに接続します。各パラメータの意味は以下の通りです：

host：PostgreSQLサーバーが動作しているホスト名（localhostは自分のコンピュータ）
database：接続先のデータベース名
user：データベースにログインするためのユーザー名
password：認証用のパスワード
port：接続ポート番号（PostgreSQLのデフォルトは5432）

get_dsn_parameters()メソッドを使うと、現在の接続パラメータを確認できます。接続が完了したら、必ずclose()メソッドで接続を閉じることが重要です。

4. テーブルの作成とデータの挿入

データベースに接続したら、テーブルを作成してデータを保存してみましょう。テーブルは、スプレッドシートのように行と列でデータを整理して保存する構造です。


import psycopg2

connection = psycopg2.connect(
    host='localhost',
    database='sample_db',
    user='postgres',
    password='mypassword'
)

# カーソルオブジェクトを作成
cursor = connection.cursor()

# テーブルを作成するSQL文
create_table_sql = """
CREATE TABLE IF NOT EXISTS employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary INTEGER,
    hire_date DATE
)
"""

# テーブルを作成
cursor.execute(create_table_sql)
print("テーブルを作成しました")

# データを挿入
insert_sql = "INSERT INTO employees (name, department, salary, hire_date) VALUES (%s, %s, %s, %s)"
employees_data = [
    ('田中太郎', '営業部', 350000, '2023-04-01'),
    ('佐藤花子', '開発部', 420000, '2022-10-15'),
    ('鈴木一郎', '総務部', 380000, '2023-01-20')
]

# 複数のデータを一度に挿入
cursor.executemany(insert_sql, employees_data)
connection.commit()
print(f"{cursor.rowcount}件のデータを挿入しました")

# カーソルと接続を閉じる
cursor.close()
connection.close()

SERIALは、PostgreSQL独自のデータ型で、自動的に連番を生成します。これにより、各レコードに一意のIDが割り当てられます。

psycopg2では、プレースホルダーとして%sを使用します。これにより、SQLインジェクション攻撃を防ぎ、安全にデータを挿入できます。

commit()メソッドは、データベースへの変更を確定させるために必須です。これを実行しないと、変更内容が保存されません。

5. データの取得と表示

データベースに保存したデータを取得して表示する方法を学びましょう。SELECT文を使うことで、必要なデータを検索できます。


import psycopg2

connection = psycopg2.connect(
    host='localhost',
    database='sample_db',
    user='postgres',
    password='mypassword'
)

cursor = connection.cursor()

# すべてのデータを取得
cursor.execute("SELECT * FROM employees")
all_employees = cursor.fetchall()

print("=== 全従業員情報 ===")
for employee in all_employees:
    print(f"ID: {employee[0]}, 名前: {employee[1]}, 部署: {employee[2]}, 給与: {employee[3]}円, 入社日: {employee[4]}")

# 条件を指定してデータを取得
cursor.execute("SELECT name, salary FROM employees WHERE salary >= %s", (400000,))
high_salary_employees = cursor.fetchall()

print("\n=== 給与40万円以上の従業員 ===")
for employee in high_salary_employees:
    print(f"名前: {employee[0]}, 給与: {employee[1]}円")

# 1件だけ取得
cursor.execute("SELECT * FROM employees WHERE name = %s", ('田中太郎',))
employee = cursor.fetchone()
print(f"\n検索結果: {employee}")

cursor.close()
connection.close()

fetchall()メソッドは、実行結果のすべての行をタプルのリストとして取得します。fetchone()メソッドは、結果から1行だけを取得する場合に使います。

WHERE句を使うことで、特定の条件に合致するデータだけを取得できます。例えば、「給与が40万円以上の従業員」という条件を指定しています。

便利な機能：fetchmany(size)メソッドを使うと、指定した件数だけデータを取得できます。大量のデータを扱う場合に便利です。

6. データの更新と削除

既存のデータを変更したり、不要なデータを削除したりする方法を見ていきましょう。

データの更新（UPDATE）


import psycopg2

connection = psycopg2.connect(
    host='localhost',
    database='sample_db',
    user='postgres',
    password='mypassword'
)

cursor = connection.cursor()

# 従業員の給与を更新
update_sql = "UPDATE employees SET salary = %s WHERE name = %s"
cursor.execute(update_sql, (450000, '佐藤花子'))
connection.commit()

print(f"{cursor.rowcount}件のデータを更新しました")

# 部署を一括更新
cursor.execute("UPDATE employees SET department = %s WHERE department IS NULL", ('未配属',))
connection.commit()

print("未配属の従業員の部署を更新しました")

cursor.close()
connection.close()

UPDATE文では、SETで変更する列と新しい値を指定し、WHEREで更新対象を絞り込みます。WHERE句を指定しないと、すべてのレコードが更新されてしまうので注意が必要です。

データの削除（DELETE）


import psycopg2

connection = psycopg2.connect(
    host='localhost',
    database='sample_db',
    user='postgres',
    password='mypassword'
)

cursor = connection.cursor()

# 特定の従業員を削除
delete_sql = "DELETE FROM employees WHERE name = %s"
cursor.execute(delete_sql, ('鈴木一郎',))
connection.commit()

print(f"{cursor.rowcount}件のデータを削除しました")

# 削除後の件数を確認
cursor.execute("SELECT COUNT(*) FROM employees")
count = cursor.fetchone()[0]
print(f"現在の従業員数: {count}名")

cursor.close()
connection.close()

DELETE文を使う際は、必ずWHERE句を指定してください。WHERE句を忘れると、テーブル内のすべてのデータが削除されてしまいます。

7. トランザクション処理の実装

トランザクションは、複数のデータベース操作をひとまとまりとして扱う仕組みです。すべての操作が成功した場合のみ変更を確定し、途中でエラーが発生した場合はすべてを元に戻します。


import psycopg2

connection = psycopg2.connect(
    host='localhost',
    database='sample_db',
    user='postgres',
    password='mypassword'
)

cursor = connection.cursor()

try:
    # トランザクション開始（自動的に開始される）
    
    # 従業員Aの給与を減らす
    cursor.execute("UPDATE employees SET salary = salary - %s WHERE id = %s", (50000, 1))
    
    # 従業員Bの給与を増やす
    cursor.execute("UPDATE employees SET salary = salary + %s WHERE id = %s", (50000, 2))
    
    # すべて成功したらコミット
    connection.commit()
    print("トランザクションが正常に完了しました")
    
except psycopg2.Error as e:
    # エラーが発生したらロールバック
    connection.rollback()
    print(f"エラーが発生しました: {e}")
    print("すべての変更を取り消しました")
    
finally:
    cursor.close()
    connection.close()

psycopg2では、commit()を呼び出すまでの操作が自動的にトランザクションとして扱われます。rollback()メソッドを使うと、トランザクション内のすべての変更を取り消せます。

例えば、給与の調整処理で片方だけ成功してもう片方が失敗した場合、ロールバックによって両方の変更が取り消されるため、データの不整合を防げます。

8. コンテキストマネージャーの活用

Pythonのwith文を使うことで、データベース接続をより安全に管理できます。自動的にリソースを解放してくれるため、メモリリークを防げます。


import psycopg2

try:
    # with文を使った安全な接続管理
    with psycopg2.connect(
        host='localhost',
        database='sample_db',
        user='postgres',
        password='mypassword'
    ) as connection:
        with connection.cursor() as cursor:
            # データを取得
            cursor.execute("SELECT COUNT(*) FROM employees")
            count = cursor.fetchone()[0]
            print(f"従業員数: {count}名")
            
            # 新しい従業員を追加
            insert_sql = "INSERT INTO employees (name, department, salary, hire_date) VALUES (%s, %s, %s, %s)"
            cursor.execute(insert_sql, ('山田次郎', 'マーケティング部', 390000, '2024-02-01'))
            
            # commitは自動的に実行される
            print("従業員を追加しました")
            
except psycopg2.DatabaseError as e:
    print(f"データベースエラー: {e}")
    
except Exception as e:
    print(f"予期しないエラー: {e}")

with文を使うと、ブロックを抜けるときに自動的にカーソルが閉じられ、接続も適切に処理されます。エラーが発生した場合は自動的にロールバックされ、正常終了時はコミットされます。

9. PostgreSQL特有の機能の活用

PostgreSQLには、他のデータベースにはない独自の強力な機能があります。これらを活用することで、より効率的なデータ処理が可能になります。

配列型の使用

PostgreSQLでは、列に配列を保存できます。複数の値をひとつの列に格納できるため、データ構造をシンプルにできます。


import psycopg2

connection = psycopg2.connect(
    host='localhost',
    database='sample_db',
    user='postgres',
    password='mypassword'
)

cursor = connection.cursor()

# 配列を含むテーブルを作成
cursor.execute("""
    CREATE TABLE IF NOT EXISTS projects (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100),
        tags TEXT[]
    )
""")

# 配列データを挿入
cursor.execute(
    "INSERT INTO projects (name, tags) VALUES (%s, %s)",
    ('ウェブサイト開発', ['Python', 'Django', 'PostgreSQL'])
)

connection.commit()
print("配列データを挿入しました")

cursor.close()
connection.close()

JSON型のサポート

PostgreSQLは、JSONデータをネイティブにサポートしています。NoSQLのような柔軟なデータ構造を、リレーショナルデータベース内で扱えます。

PostgreSQLの利点：JSONデータに対してインデックスを作成できるため、大量のJSONデータも高速に検索できます。

10. エラー処理とセキュリティのベストプラクティス

psycopg2を安全に使うための重要なポイントをまとめました。

主なエラーの種類

psycopg2.OperationalError：接続エラーやサーバーエラー
psycopg2.ProgrammingError：SQL文の構文エラー
psycopg2.IntegrityError：主キー制約違反などの整合性エラー
psycopg2.DataError：データ型の不一致エラー
psycopg2.DatabaseError：データベース関連のすべてのエラーの基底クラス

セキュリティ対策

データベース操作では、セキュリティに十分注意する必要があります。以下のポイントを守りましょう：

必ずプレースホルダー（%s）を使用し、文字列の連結でSQL文を作らない
データベース接続情報は環境変数や設定ファイルで管理する
最小権限の原則に従い、必要最小限の権限だけを付与する
本番環境では、rootユーザーではなく専用のユーザーを使用する

パフォーマンス最適化

インデックスを適切に設定して検索速度を向上させる
大量のデータを挿入する場合はexecutemany()を使う
不要な列は取得せず、必要な列だけをSELECTする
接続プールを使って接続のオーバーヘッドを削減する

開発のヒント

psycopg2は、準備された文（Prepared Statements）を自動的に使用するため、同じSQL文を繰り返し実行する場合のパフォーマンスが向上します。また、PostgreSQLのEXPLAINコマンドを使ってクエリの実行計画を確認することで、パフォーマンスのボトルネックを特定できます。

公的機関での指導実績【Python実務設計実践セミナー】