Python で psycopg2 を使って PostgreSQL にアクセスする

[Tobotobo]

psycopg2 でよくやる操作まとめ

import psycopg2
db_host = "postgres"
db_port = "5432"
db_name = "postgres"
db_username = "postgres"
db_password = "postgres"
dsn = f"postgresql://{db_username}:{db_password}@{db_host}:{db_port}/{db_name}"

テーブル作成

with psycopg2.connect(dsn) as conn:
    with conn.cursor() as cur:
        cur.execute("""
                CREATE TABLE users (
                    id SERIAL PRIMARY KEY,
                    name VARCHAR(255) NOT NULL
                );
            """)

インサート

with psycopg2.connect(dsn) as conn:
    with conn.cursor() as cur:
        cur.execute("""
                INSERT INTO users (
                    name
                ) VALUES (
                    '東京 太郎'
                );
            """)

クエリパラメータ

with psycopg2.connect(dsn) as conn:
    with conn.cursor() as cur:
        name = "東京 花子"
        cur.execute("""
                INSERT INTO users (
                    name
                ) VALUES (
                    %s
                );
            """, [name])

取得した結果を表示

with psycopg2.connect(dsn) as conn:
    with conn.cursor() as cur:
        cur.execute('SELECT * FROM users')
        rows = cur.fetchall()
print(rows)