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)にも注意しましょう。
演習問題
- テーブル作成
- テーブル名:products
- カラム:id(SERIAL, PK), name(TEXT), price(NUMERIC)
- データ挿入
- 3件の製品データを挿入してください。
- データ取得
- 価格が500以上の製品を取得してください。
- データ更新
- 特定の製品の価格を1.1倍に更新してください。
- データ削除
- 価格が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の基本から演習までの流れです。
実際に手を動かしながら学習すると理解が深まります。ぜひローカル環境で試してみてください!