初心者向けPython入門:psycopg2で始めるPostgreSQL操作

PythonでPostgreSQLを操作する際、最もポピュラーなライブラリがpsycopg2です。

本記事では、初心者の方でもつまずかないよう、インストールから基本的な接続・CRUD操作、エラーハンドリングまで解説します。

最後に演習問題とその解答例を掲載していますので、実際に手を動かしながら理解を深めてみましょう。


psycopg2とは?

psycopg2はPythonからPostgreSQLデータベースに接続し、SQLを実行するためのドライバライブラリです。

純粋なPython実装ではなく、C言語で書かれているため高速かつ安定性があります。

また、トランザクション管理やプリペアドステートメントなど、PostgreSQLの機能をフルに使えます。


開発環境の準備とインストール

PostgreSQLのインストール

OSに合わせて公式サイトからインストーラを入手し、データベースサーバーを起動します。

Pythonと仮想環境の作成
python3 -m venv venv
source venv/bin/activate  # Windowsでは venv\Scripts\activate
psycopg2のインストール
pip install psycopg2-binary

公式版はpsycopg2ですが、ビルド済みバイナリのpsycopg2-binaryが簡便です。


データベースへの接続と切断

import psycopg2

# 接続情報
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    dbname="testdb",
    user="your_user",
    password="your_password"
)
# カーソル生成
cur = conn.cursor()

# --- ここでSQL操作 ---

# リソース解放
cur.close()
conn.close()

connect()でコネクションを生成し、cursor()でSQLを実行するためのカーソルを取得します。

最後にclose()で確実に切断しましょう。


テーブル作成(CREATE)

create_table_sql = """
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);
"""
cur.execute(create_table_sql)
conn.commit()

IF NOT EXISTSを付けることで既存テーブルがあってもエラーになりません。

DDL実行後は必ずcommit()を呼び出します。


データ挿入(INSERT)

insert_sql = "INSERT INTO users (name, email) VALUES (%s, %s) RETURNING id;"
cur.execute(insert_sql, ("Alice", "alice@example.com"))
new_id = cur.fetchone()[0]
conn.commit()
print(f"新規ユーザーID: {new_id}")

パラメータは%sとタプルで渡し、SQLインジェクションを防ぎます。

returning句で挿入後のIDを取得できます。


データ取得(SELECT)

cur.execute("SELECT id, name, email FROM users WHERE name LIKE %s;", ("A%",))
rows = cur.fetchall()
for row in rows:
    print(row)

fetchall()は全件、fetchone()は1件、fetchmany(size)は指定件数を取得します。

条件付き検索や並び替え、集計もSQLで自由に記述できます。


データ更新(UPDATE)

update_sql = "UPDATE users SET email = %s WHERE id = %s;"
cur.execute(update_sql, ("alice@newdomain.com", new_id))
conn.commit()
print(f"ユーザーID {new_id} のメールアドレスを更新しました。")

データ削除(DELETE)

delete_sql = "DELETE FROM users WHERE id = %s;"
cur.execute(delete_sql, (new_id,))
conn.commit()
print(f"ユーザーID {new_id} を削除しました。")

トランザクションと例外処理

try:
    cur.execute("BEGIN;")
    # 複数の操作
    cur.execute("INSERT ...")
    cur.execute("UPDATE ...")
    conn.commit()
except psycopg2.DatabaseError as e:
    conn.rollback()
    print(f"エラー発生、ロールバックしました: {e}")
finally:
    cur.close()
    conn.close()

トランザクション管理はBEGIN/COMMIT/ROLLBACKで行います。

例外時にはrollback()を忘れずに。


応用:プリペアドステートメントとパフォーマンス

  • 大量データを扱う際は、一度に多くのINSERTをバルク実行すると高速です。
  • cur.execute_batch()(psycopg2.extras)を活用するのも手です。
  • 適切なインデックス設計やSQLでの結合(JOIN)にも注意しましょう。

演習問題

  1. テーブル作成
    • テーブル名:products
    • カラム:id(SERIAL, PK), name(TEXT), price(NUMERIC)
  2. データ挿入
    • 3件の製品データを挿入してください。
  3. データ取得
    • 価格が500以上の製品を取得してください。
  4. データ更新
    • 特定の製品の価格を1.1倍に更新してください。
  5. データ削除
    • 価格が100以下の製品を削除してください。

解答例

import psycopg2

conn = psycopg2.connect(host="localhost", port=5432, dbname="testdb", user="user", password="pass")
cur = conn.cursor()

# 1. テーブル作成
cur.execute("""
CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC NOT NULL
);
""")
conn.commit()

# 2. データ挿入
products = [
    ("ノートPC", 120000),
    ("USBケーブル", 800),
    ("ゲーミングマウス", 4500)
]
for p in products:
    cur.execute("INSERT INTO products (name, price) VALUES (%s, %s);", p)
conn.commit()

# 3. データ取得
cur.execute("SELECT id, name, price FROM products WHERE price >= %s;", (500,))
print("価格500以上の製品:")
for row in cur.fetchall():
    print(row)

# 4. データ更新
cur.execute("UPDATE products SET price = price * 1.1 WHERE id = %s;", (1,))
conn.commit()
print("ID=1 の価格を1.1倍に更新しました。")

# 5. データ削除
cur.execute("DELETE FROM products WHERE price <= %s;", (100,))
conn.commit()
print("価格100以下の製品を削除しました。")

cur.close()
conn.close()

以上がpsycopg2の基本から演習までの流れです。

実際に手を動かしながら学習すると理解が深まります。ぜひローカル環境で試してみてください!