PythonからPostgreSQLを操作する方法を解説します。
今回利用した環境は以下のようになります。
- OS : windows10
- Python : 3.10.4
- psycopg2 : 2.9.3
- PostgreSQL : 14.4
とりあえず接続を試すだけなので、同じPC上にインストールしたPostgresSQLに接続します。
目次 非表示
SQLに接続
PostgreSQLに接続するためにまずpsycopg2をpipからインストールします。
pip install psycopg2PostgresSQLへの接続の開始は以下のように書きます。
# pythonイテレータ
>>> import psycopg2 as db
>>> conn = db.connect("host=ホストアドレス port=ポート番号 dbname=データベース名 user=ユーザーネーム password=パスワード")
>>> conn.get_backend_pid()
20352これで新しいデータベースセッションを作成しconnectionインスタンスが生成されました。
実際にPosgresSQLサーバー側のプロセスID:20352で接続しています。
> tasklist /FI "PID eq 6664"
イメージ名 PID セッション名 セッション# メモリ使用量
========================= ======== ================ =========== ============
postgres.exe 6664 Services 0 10,320 Kconnect()の引数は、cmdでPostgresSQLに接続するときのパラメータと同じになります。
生成されたconnectionインスタンスはデータベースセッションをカプセル化し、次のことが可能となります。
- cursor()メソッドを利用して、データベースコマンドやクエリを実行するためのcursorインスタンスを生成
- commit()メソッドまたはrollback()メソッドを利用してトランザクションを終了させる
SQLクエリへのパラメータの受け渡し
sqlクエリはcusorインスタンスを利用して発行します。
まず以下の文でconnectionインスタンスからcursor()メソッドを利用して、cursorインスタンを生成します。
pythonイテレータの続き
>>> cur = conn.cursor()クエリの発行
先程は生成したcursorインスタンスのexecute()メソッドを利用してクエリを発行します。
基本的に引用符の中にpostgreのSQL文を入れます。
# テーブルの作成
>>> cur.excute("CREATE TABLE test(id integer, name varchr(10))")
# データの挿入
>>> cur.excute("INSERT INTO test(id,name) VALUES(1,'user')")
# データの取得(データはPythonオブジェクトとして渡されます)
>>> cur.excute("SELECT * FROM test;")
>>> for row in cur:
print(row)
....
(1, 'user')
#コミット
>>> conn.commit()
#データベースを閉じる
>>> cur.close()
>>> conn.close()いろいろなInsert文
プレースホルダーの使用
データの挿入は%sプレースホルダーを使用し、関数の２番目の引数として値のシーケンスを渡すことによって行える。
>>> cur.execute("""
insert into test(id,name)
values(%s,%s)
""",(4,'test4'))この関数においてもpythonのタプルは要素が一つの場合(“data”,)のようにコンマが必要である。
名前付き引数の利用
クエリでプレースホルダーを使用し、マッピングに値を指定することができる。
>>> cur.execute("""
... INSERT INTO some_table (an_int, a_date, another_date, a_string)
... VALUES (%(int)s, %(date)s, %(date)s, %(str)s);
... """,
... {'int': 10, 'str': "O'Reilly", 'date': datetime.date(2005, 11, 18)})名前付き引数を利用することで値を任意の順序で指定し、クエリの複数の場所で同じ値を繰り返すことができる。
pythonの構文とは違い、プレスホルダーは常に%sを利用します。
変数の利用
変数にタプルのデータを入れ、引数として渡すことができる。
data = (5,"data5",)
cur.execute("""
insert into test(id,name)
values(%s,%s)
""",data)
トランザクションの制御
sycopgでは、トランザクションはconnectionクラスによって処理されます。デフォルトでは、コマンドがデータベースに初めて送信されると（ cursor接続によって作成されたのいずれかを使用して）、新しいトランザクションが作成されます。データベースコマンドは、同じトランザクションのコンテキストで実行されます。データベースコマンドは、最初のカーソルによって発行されたコマンドだけでなく、同じ接続によって作成されたすべてのカーソルによって発行されたコマンドです。
