PythonからPostgresSQL利用する方法

スポンサーリンク
プログラミング

PythonからPostgreSQLを操作する方法を解説します。

今回利用した環境は以下のようになります。

  • OS : windows10
  • Python : 3.10.4
  • psycopg2 : 2.9.3
  • PostgreSQL : 14.4

とりあえず接続を試すだけなので、同じPC上にインストールしたPostgresSQLに接続します。

スポンサーリンク

SQLに接続

PostgreSQLに接続するためにまずpsycopg2をpipからインストールします。

pip install psycopg2

PostgresSQLへの接続の開始は以下のように書きます。

# 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 K

connect()の引数は、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プレースホルダーを使用し、関数の2番目の引数として値のシーケンスを渡すことによって行える。

>>> 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接続によって作成されたのいずれかを使用して)、新しいトランザクションが作成されます。データベースコマンドは、同じトランザクションのコンテキストで実行されます。データベースコマンドは、最初のカーソルによって発行されたコマンドだけでなく、同じ接続によって作成されたすべてのカーソルによって発行されたコマンドです。

コメント

タイトルとURLをコピーしました