https://pydocument.hatenablog.com/entry/2023/03/31/000945

PostgreSQLは、オープンソースのリレーショナルデータベース管理システムであり、多くの機能を備えています。Pythonは、簡単にPostgreSQLに接続して、データを操作できるようにするための多くのライブラリを提供しています。この記事では、PythonでPostgreSQLに接続し、テーブルを作成して、データを挿入、取得、更新する方法を説明します。

事前準備: psycopg2ライブラリのインストール

PythonでPostgreSQLに接続するには、まずpsycopg2ライブラリをインストールする必要があります。次のコマンドを使用して、psycopg2ライブラリをインストールします。

pip install psycopg2

PostgreSQLに接続する

PostgreSQLに接続するには、psycopg2を使用して接続を確立する必要があります。以下がコードの例です。

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    database="mydatabase",
    user="myusername",
    password="mypassword"
)

psycopg2.connect() 関数を使って、PostgreSQLデータベースに接続します。この関数は、接続に必要な情報を引数として受け取ります。

host: PostgreSQLサーバーのホスト名またはIPアドレスを指定します。この例では、localhostを指定していますが、実際の環境の情報に置き換えてください。
database: 接続するデータベースの名前を指定します。この例では、"mydatabase"という名前のデータベースに接続しようとしています。
user: データベースに接続するためのユーザー名を指定します。この例では、"myusername"というユーザー名を使用しています。
password: データベースに接続するためのパスワードを指定します。この例では、"mypassword"というパスワードを使用しています。

これにより、conn 変数にPostgreSQLデータベースへの接続が格納されます。以降のこの接続を使用して、データベースに対するクエリを実行したり、データを操作したりします。

テーブルを作成する

次に、PostgreSQLにテーブルを作成します。以下のコードは、新しいテーブルを作成するサンプルコードです。

cur = conn.cursor()

cur.execute("""
    CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255),
        email VARCHAR(255)
    )
""")

conn.commit()

このPythonコードは、先程のコードで作成したPostgreSQLデータベースに対して、新しいテーブルを作成する操作を行っています。ここではPostgreSQLデータベース内に新しい "users" という名前のテーブルを作成し、そのテーブルのスキーマを定義しています。

conn.cursor() メソッドを使用して、データベースとの通信に使用するカーソルオブジェクトを取得しています。このカーソルを使用して、データベース上でクエリを実行します。
cur.execute() メソッドを使用して、SQLクエリを実行します。新しいテーブルを作成する際はCREATE TABLE 文を使用します。この例では、下記の3つの列が定義した "users" という名前のテーブルを作成します。
- id: シリアル型の主キー列で、各行の一意の識別子として使用されます。
- name: VARCHAR型の列で、最大255文字の文字列を格納できます。
- email: VARCHAR型の列で、最大255文字の文字列を格納できます。
conn.commit() メソッドを呼び出して、変更をデータベースにコミットします。これにより、変更が永続化され、データベースに反映されます。

データを挿入する

新しく作成したテーブルに新しいデータを挿入する方法を説明します。ここでは、PostgreSQLデータベースの "users" テーブルに新しいデータを挿入していきます。

cur = conn.cursor()

cur.execute("""
    INSERT INTO users (name, email)
    VALUES (%s, %s)
""", ("John Doe", "johndoe@example.com"))

conn.commit()

このコードでは、先ほど作成したPostgreSQLデータベースの "users" テーブルに新しいデータを挿入する操作を行っています。

conn.cursor() メソッドを使用して、データベースとの通信に使用するカーソルオブジェクトを取得しています。
cur.execute() メソッドを使用して、SQLクエリを実行しています。ここでは、INSERT INTO 文を使用して新しいデータを挿入しています。このクエリでは、"users" テーブルに新しい行を挿入し、"name" と "email" 列に値を設定しています。
- %s はプレースホルダーで、後で渡される実際の値で置換されます。
- ("John Doe", "johndoe@example.com") は、VALUES 句に挿入される実際の値のタプルです。"John Doe" が "name" 列に、"johndoe@example.com " が "email" 列に挿入されます。
conn.commit() メソッドを呼び出して、変更をデータベースにコミットします。これにより、変更が永続化され、データベースに反映されます。

補足: プレースホルダーの利用について

上記のコードでプレースホルダーを使用する理由は、SQLインジェクション攻撃から保護するためです。SQLインジェクションは、不正なSQLコードを挿入することによって、データベースへのアクセスを悪用しようとする攻撃です。例えば、ユーザーが入力した情報をそのままSQLクエリに挿入する場合、入力された情報にSQLコマンドが含まれていると、意図しないクエリが実行される可能性があります。

プレースホルダーを使用すると、SQLクエリの値を動的に挿入する際に、値をエスケープしてSQLコマンドとして解釈されないようにすることができます。これにより、SQLインジェクション攻撃から保護されます。具体的には、プレースホルダー %s を使用することで、実際の値がクエリに挿入される際に、その値がエスケープされます。したがって、cur.execute() メソッドに渡された実際の値が、SQLコマンドとして解釈されることはありません。

データを取得する

テーブルにデータを挿入したら、次にデータを取得します。以下のコードで、テーブルからすべてのユーザーを取得します。

cur = conn.cursor()

cur.execute("SELECT * FROM users")

rows = cur.fetchall()

for row in rows:
    print(row)

conn.cursor() メソッドを使用して、データベースとの通信に使用するカーソルオブジェクトを取得しています。
cur.execute() メソッドを使用して、SQLクエリを実行します。ここでは、SELECT * FROM users を実行し、"users" テーブルからすべての列を取得しています。
cur.fetchall() メソッドを使用して、クエリの結果を取得します。このメソッドは、クエリの実行結果からすべての行を取得し、それらをタプルのリストとして返します。
for row in rows: ループを使用して、取得した各行について処理しています。各行はタプルとして表されます。 print(row)で各行の内容を表示します。

データを更新する

最後に、テーブル内のデータを更新します。以下のコードで、テーブル内の特定のユーザーのデータを更新します。ここでは、PostgreSQLデータベース内の "users" テーブルの特定の行を更新する操作を行っています。

cur = conn.cursor()

cur.execute("""
    UPDATE users SET name = %s, email = %s WHERE id = %s
""", ("Jane Doe", "janedoe@example.com", 1))

conn.commit()

conn.cursor() メソッドを使用して、データベースとの通信に使用するカーソルオブジェクトを取得しています。
cur.execute() メソッドを使用して、SQLクエリを実行しています。ここでは、UPDATE 文を使用して "users" テーブルの行を更新しています。このクエリでは、指定された条件に一致する行の "name" と "email" 列の値を指定された新しい値に更新します。
- %s はプレースホルダーで、後で渡される実際の値で置換されます。
- ("Jane Doe", "janedoe@example.com", 1) は、SET 句で指定された新しい値のタプルです。"Jane Doe" が "name" 列に、"janedoe@example.com " が "email" 列に、1が "id" 列に対応する行を特定します。
conn.commit() メソッドを呼び出して、変更をデータベースにコミットします。これにより、変更が永続化され、データベースに反映されます。

よく発生するエラーとその対応方法

PythonでPostgreSQLを操作する際によく発生するエラーとその対応方法を以下に示します。

`psycopg2.OperationalError: FATAL: database "dbname" does not exist`

データベース名が正しいかどうか確認してください。存在しない場合は、データベースを作成する必要があります。

`psycopg2.ProgrammingError: relation "table_name" does not exist`

テーブル名が正しいかどうか確認してください。存在しない場合は、テーブルを作成する必要があります。

`psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint`

一意制約違反エラーが発生した場合、データベースに既に同じ値が存在していることが原因です。重複する値を更新するか、一意制約を変更する必要があります。

`psycopg2.errors.SyntaxError: syntax error at or near "column_name"`

クエリに構文エラーがある場合、SQL文を再度確認してください。問題が特定できない場合は、クエリを少しずつ実行していき、エラーが発生する箇所を特定してください。

`psycopg2.InterfaceError: cursor already closed`

カーソルがすでにクローズされているため、再度カーソルを取得してください。

`psycopg2.errors.UndefinedColumn: column "column_name" of relation "table_name" does not exist`

カラム名が正しいかどうか確認してください。存在しない場合は、テーブルにカラムを追加する必要があります。

`psycopg2.errors.InsufficientPrivilege: permission denied for table table_name`

アクセス権が不十分な場合、データベース管理者に権限を与えるように依頼してください。

まとめ

以上のように、Pythonを使用してPostgreSQLに接続し、テーブルを作成し、データを挿入、取得、更新することができます。psycopg2ライブラリは、PostgreSQLとPythonの間の接続を確立し、データベースに対するクエリを実行するための機能を提供します。これらのコードを使用して、PythonとPostgreSQLを連携させ、データを操作することができます。最後にPythonの学習に利用できるUdemy のサイトを紹介します。ぜひ活用ください。

[PR]

click.linksynergy.com