PythonでPostgreSQLを操作する

PostgreSQL は、最も先進的で広く使用されているリレーショナルデータベース管理システムの 1 つです。

オープンソースであること、拡張性があること、様々な種類のアプリケーションと様々な負荷に対応できることなど、多くの理由から非常に人気があります。

Pythonを使えば、PostgreSQLデータベースへの接続を簡単に確立することができます。

PostgreSQL用のPythonドライバはたくさんありますが、”psycopg “は最も人気のあるものです。

現在のバージョンはpsycopg2です。

今回は、psycopg2ドライバを使ってPythonでPostgreSQLのデータベースにアクセスする方法を説明します。

psycopg2 モジュール

psycopg2 モジュールを使って Postgres と Python を統合することができます。

psycopg2 は Python 用の Postgres データベースアダプタです。

このモジュールを使用するには、まずモジュールをインストールする必要があります。

これは、以下のように pip コマンドを使用して行うことができます。

$ pip3 install psycopg2

Python 3.5 を使用しているため、pip の代わりに pip3 を使用していることに注意してください。

モジュールがインストールされたら、アプリケーションでデータベースに接続するために使用することができます。

データベースへの接続

データベースに接続するには、まず、データベースを表す接続オブジェクトを作成する必要があります。

次に、SQL 文の実行を支援するカーソルオブジェクトを作成します。

次の例は、”postgres “という名前のデータベースへの接続を確立する方法を示しています。

import psycopg2


con = psycopg2.connect(database="postgres", user="postgres", password="", host="127.0.0.1", port="5432")


print("Database opened successfully")

出力

出力：“`
Database opened successfully

以下は `connect()` メソッドに渡されたパラメーターのリストです。

* `database`: 接続するデータベースの名前です。
* `user`: 接続するデータベースの名前。認証に使用するユーザー名
* `password`: このユーザーのデータベースパスワード。
* `host`: データベースサーバーのアドレス。データベースサーバーのアドレス。例えば、ドメイン名、"localhost"、または IP アドレスです。
* `port`: ポート番号。もし指定しなかった場合は、デフォルトの5432が使用されます。

接続に成功するためには、上記のパラメータの値が正しい必要があることに注意してください。そうでない場合は、例外が生成されます。上記のコードの出力は、データベースへの接続が正常に確立されたことを示しています。



### テーブルの作成

Python で Postgres のテーブルを作成するには、`CREATE TABLE` という SQL 文を使用します。このクエリは、データベースへの接続を確立した後に実行する必要があります。また、 `connection` オブジェクトに属する `cursor()` メソッドを呼び出して、カーソルオブジェクトを作成します。この `cursor` オブジェクトは、実際にコマンドを実行するために使用されます。

次に、`cursor` オブジェクトの `execute()` メソッドを呼び出して、テーブルの作成を支援します。最後に、接続をコミットして閉じる必要があります。接続を "コミット" することで、ドライバがデータベースにコマンドを送信するようになります。

以下の例では、これを実演しています。

import psycopg2

con = psycopg2.connect(database=”postgres”, user=”postgres”, password=”Kaliakakya”, host=”127.0.0.1″, port=”5432″)
print(“Database opened successfully”)

cur = con.cursor()
cur.execute(”’CREATE TABLE STUDENT
(ADMISSION INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
COURSE CHAR(50),
DEPARTMENT CHAR(50));”’)
print(“Table created successfully”)

con.commit()
con.close()

出力

Database opened successfully
Table created successfully

commit()` メソッドは、データベースに行った変更を適用するのに役立ちます。`commit()` が正常に実行されると、これらの変更は元に戻せません。close()` メソッドは、データベースへの接続を閉じます。

この時点で、4 つのカラムを持つテーブルを作成しましたが、これらのカラムはすべてさまざまなデータ型を持っています。上の出力は、テーブルが正常に作成されたことを示しています。



### データの挿入

Postgresデータベースのテーブルには、単一のレコードまたは複数のレコードを挿入することができます。ここでも、まず `connect()` 関数を呼び出してデータベースサーバへの接続を確立しなければなりません。次に、`cursor()` メソッドを呼び出して、カーソルオブジェクトを作成しなければなりません。最後に、`execute()` メソッドで `INSERT` 文を実行し、テーブルにデータを追加します。

以下は、その例です。

import psycopg2

con = psycopg2.connect(database=”postgres”, user=”postgres”, password=”Kaliakakya”, host=”127.0.0.1″, port=”5432″)
print(“Database opened successfully”)

cur = con.cursor()

cur.execute(“INSERT INTO STUDENT (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3420, ‘John’, 18, ‘Computer Science’, ‘ICT’)”);

con.commit()
print(“Record inserted successfully”)
con.close()

出力

出力

このコードを実行すると、データベースのテーブルに1つのレコードが挿入されます。これは、テーブルの名前と、データを挿入する必要のある列を指定することによって行われました。また、1つのコマンドで複数のレコードを挿入することもできます。例えば

Database opened successfully
Record inserted successfully

出力

import psycopg2

con = psycopg2.connect(database=”postgres”, user=”postgres”, password=”Kaliakakya”, host=”127.0.0.1″, port=”5432″)
print(“Database opened successfully”)

cur = con.cursor()

cur.execute(“INSERT INTO STUDENT (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3419, ‘Abel’, 17, ‘Computer Science’, ‘ICT’)”);
cur.execute(“INSERT INTO STUDENT (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3421, ‘Joel’, 17, ‘Computer Science’, ‘ICT’)”);
cur.execute(“INSERT INTO STUDENT (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3422, ‘Antony’, 19, ‘Electrical Engineering’, ‘Engineering’)”);
cur.execute(“INSERT INTO STUDENT (ADMISSION,NAME,AGE,COURSE,DEPARTMENT) VALUES (3423, ‘Alice’, 18, ‘Information Technology’, ‘ICT’)”);

con.commit()
print(“Records inserted successfully”)
con.close()

すべての `INSERT` 文を「実行」するまで `commit()` メソッドは呼び出されないので、一度の `commit()` メソッド呼び出しで複数のレコードを挿入することができます。



### データの取得

Postgresデータベースからデータを選択して、テーブルのレコードを表示することができる。まず、`connect()`関数を用いてデータベースへの接続を確立する必要があります。次に、`cursor()` メソッドを呼び出して、新しいカーソルを作成しなければなりません。作成したカーソルオブジェクトを使って、`SELECT` 文を実行し、データベースからデータを取得することができます。

例えば

Database opened successfully
Records inserted successfully

出力

import psycopg2

con = psycopg2.connect(database=”postgres”, user=”postgres”, password=”Kaliakakya”, host=”127.0.0.1″, port=”5432″)
print(“Database opened successfully”)

cur = con.cursor()
cur.execute(“SELECT admission, name, age, course, department from STUDENT”)
rows = cur.fetchall()

for row in rows:
print(“ADMISSION =”, row[0])
print(“NAME =”, row[1])
print(“AGE =”, row[2])
print(“COURSE =”, row[3])
print(“DEPARTMENT =”, row[4], ”
“)

print(“Operation done successfully”)
con.close()

ここでは、データベースからデータを取得するために、テーブルとカラムの名前を指定しています。このデータはタプルのリストとして返され、「トップレベル」のリストがデータの行となる。そして、各行がカラムデータのタプルになる。もしクエリに対して行が返されなかった場合は、`fetchall()` によって空のリストが返されます。



### テーブルの更新

データベースのテーブルに既に挿入されているレコードの詳細を更新したり変更したりすることができます。まず、`connect()` メソッドを用いてデータベースへの接続を確立する必要があります。次に、`cursor()`関数を呼び出してカーソルオブジェクトを生成します。最後に、`execute()` メソッドを実行して、入力値を指定して `UPDATE` 文を実行します。

例えば

Database opened successfully
ADMISSION = 3420
NAME = John
AGE = 18
COURSE = Computer Science
DEPARTMENT = ICT

ADMISSION = 3419
NAME = Abel
AGE = 17
COURSE = Computer Science
DEPARTMENT = ICT

ADMISSION = 3421
NAME = Joel
AGE = 17
COURSE = Computer Science
DEPARTMENT = ICT

ADMISSION = 3422
NAME = Antony
AGE = 19
COURSE = Electrical Engineering
DEPARTMENT = Engineering

ADMISSION = 3423
NAME = Alice
AGE = 18
COURSE = Information Technology
DEPARTMENT = ICT

Operation done successfully

出力

import psycopg2

con = psycopg2.connect(database=”postgres”, user=”postgres”, password=”Kaliakakya”, host=”127.0.0.1″, port=”5432″)
print(“Database opened successfully”)

cur = con.cursor()

cur.execute(“UPDATE STUDENT set AGE = 20 where ADMISSION = 3420”)
con.commit()
print(“Total updated rows:”, cur.rowcount)

cur.execute(“SELECT admission, age, name, course, department from STUDENT”)
rows = cur.fetchall()
for row in rows:
print(“ADMISSION =”, row[0])
print(“NAME =”, row[1])
print(“AGE =”, row[2])
print(“COURSE =”, row[2])
print(“DEPARTMENT =”, row[3], ”
“)

print(“Operation done successfully”)
con.close()

上記の例では、`ADMISSION` が 3420 であるすべての行の `AGE` カラムの値を更新しています。更新を実行した後、このデータを取得し、適切な行や列が更新されたことを確認します。



### 行の削除

Postgresデータベースのテーブルからレコードを削除するには、まずデータベースサーバとの接続を確立する必要があります。次に、`cursor()`関数を呼び出してカーソルオブジェクトを作成しなければなりません。それから、`DELETE` 文を実行して削除を実行します。

例えば

Database opened successfully
Total updated rows: 1
ADMISSION = 3419
NAME = 17
AGE = Abel
COURSE = Abel
DEPARTMENT = Computer Science

ADMISSION = 3421
NAME = 17
AGE = Joel
COURSE = Joel
DEPARTMENT = Computer Science

ADMISSION = 3422
NAME = 19
AGE = Antony
COURSE = Antony
DEPARTMENT = Electrical Engineering

ADMISSION = 3423
NAME = 18
AGE = Alice
COURSE = Alice
DEPARTMENT = Information Technology

ADMISSION = 3420
NAME = 20
AGE = John
COURSE = John
DEPARTMENT = Computer Science

Operation done successfully

出力

import psycopg2

con = psycopg2.connect(database=”postgres”, user=”postgres”, password=”Kaliakakya”, host=”127.0.0.1″, port=”5432″)
print(“Database opened successfully”)

cur = con.cursor()

cur.execute(“DELETE from STUDENT where ADMISSION=3420;”)
con.commit()
print(“Total deleted rows:”, cur.rowcount)

cur.execute(“SELECT admission, name, age, course, department from STUDENT”)
rows = cur.fetchall()
for row in rows:
print(“ADMISSION =”, row[0])
print(“NAME =”, row[1])
print(“AGE =”, row[2])
print(“COURSE =”, row[3])
print(“DEPARTMENT =”, row[4], ”
“)

print(“Deletion successful”)
con.close()

“`

この例では、生徒の入学番号が3420であるレコードをすべて削除しています。

SELECT`を使用してデータを取得した後、このレコードが上記の出力に含まれないことがわかり、データベースから削除されたことが確認できます。

結論

PythonでPostgreSQLのデータベースにアクセスする方法はいろいろあります。

この目的のために使えるPython用のデータベースドライバはたくさんありますが、psycopgは最も人気のあるものです。

この記事では、モジュールをインストールし、PostgreSQLデータベースへの接続を確立し、Pythonコードを使用して一般的なSQLクエリを実行する方法を示しました。