Python - よく使うモジュール - psycopg2 PostgreSQL 接続

1.　概要

2.　インストール

3.　接続

4.　トランザクション

5.　SQL の実行

6.　変数展開

7.　バインド変数

8.　辞書型で取得

1.　概要

　「PostgreSQL」のデータベースアクセスに使用する、モジュールです。

　本項は、下記の記事を参考にさせていただきました。

「Python から PostgreSQL に接続する方法」
「psycopg2 でよくやる操作まとめ」


2.　インストール

　さすがにこれは、インストールが必要ですな。

　「FreeBSD」の場合、「ports」「pkg」でもインストールできそうです。ありがたい。


cd /usr/ports/databases/py-psycopg2
make
make install


　オプションは、ありません。

3.　接続

　データベースへの接続は、下記の方法で・・・。


import psycopg2

connection = psycopg2.connect("host=ホスト名 port=ポート番号 dbname=データベース名 user=ユーザ名 password=パスワード")


　とまぁ、接続文字列をべた書きのようです。

　接続文字列のパラメータには、以下のものを設定できるそうな。

4.　トランザクション

　参考サイトによれば「『psycopg2』ではデフォルトでトランザクションが有効になっているので『commit』を行わないと反映されない。」そうです。

5.　SQL の実行


CREATE TABLE PUBLIC.SAMPLE
(
    INDEX INTEGER NOT NULL,
    NAME  TEXT    NOT NULL,
    AGE   INTEGER NOT NULL
);

CREATE UNIQUE INDEX SAMPLE_KEY ON PUBLIC.SAMPLE USING BTREE (INDEX);


　てなテーブルを作成しておいて、「INSERT」してみます。


import psycopg2
import psycopg2.extras

connection = psycopg2.connect("host="+dbparam['host']+" port="+dbparam['port']+" dbname="+dbparam['dbname']+" user="+dbparam['user']+" password="+dbparam['password'])
connection.set_client_encoding('utf-8')

try:
  with connection.cursor() as cur:
    cur.execute("INSERT INTO SAMPLE (INDEX, NAME, AGE) VALUES (1, 'hogehoge', 19) ")

  connection.commit()

except Exception as ex:
  print(ex)
  connection.rollback()

connection.close()



　前の方で、接続時のパラメータを記述しておりますが、省略しています。悪しからず。
　このプログラム、予定通り動作します。

6.　変数展開

　前項では、「SQL」をべた書きしておりますが、こういうのは、不都合なことが多い・・・。
　変数使いたいですよね。
　下記の形式で書けるようです（テーブルの構成は、前項と同様）。


import psycopg2
import psycopg2.extras

connection = psycopg2.connect("host="+dbparam['host']+" port="+dbparam['port']+" dbname="+dbparam['dbname']+" user="+dbparam['user']+" password="+dbparam['password'])
connection.set_client_encoding('utf-8')

insert = {
          'index' : 2,
          'name'  : 'fugafuga',
          'age'   : 20,
          }

try:
  with connection.cursor() as cur:
    cur.execute("INSERT INTO SAMPLE (INDEX, NAME, AGE) VALUES (%s, %s, %s) ", (insert['index'], insert['name'], insert['age']))

  connection.commit()

except Exception as ex:
  print(ex)
  connection.rollback()

connection.close()



　これも、動作確認できました。

7.　バインド変数

　前項の状態でも、まだまだ、実行文が長くなっちゃいます。
　これを解消するために、バインド変数が使えるようです。
　さらにバインド変数を使えば、文字列が「str」型に収まっていれば、「'」のような特殊文字がはいっていても、エスケープすることなく、「SQL」が組めるはずです。

　下記の形式で書けるようです（テーブルの構成は、前項と同様）。


import psycopg2
import psycopg2.extras

connection = psycopg2.connect("host="+dbparam['host']+" port="+dbparam['port']+" dbname="+dbparam['dbname']+" user="+dbparam['user']+" password="+dbparam['password'])
connection.set_client_encoding('utf-8')

insert = {
          'index' : 3,
          'name'  : 'piyopiyo',
          'age'   : 21,
          }

try:

  sql = 'INSERT INTO SAMPLE (INDEX, NAME, AGE) VALUES (%(index)s, %(name)s, %(age)s) '

  with connection.cursor() as cur:
    cur.execute(sql, ({
                        'index' : (insert['index'], ),
                        'name'  : (insert['name'],),
                        'age'   : (insert['age'],),
                      } ))

  connection.commit()

except Exception as ex:
  print(ex)
  connection.rollback()

connection.close()



　これも、動作確認できました。

8.　辞書型で取得

　「psycopg2.connect」を接続後「cursor_factory」というプロパティを「psycopg2.extras.DictCursor」という値に設定することによって、「SELECT」した結果を、辞書柄で取得することができます。
　参考サイトでは、下記のような例が書かれていました。


from psycopg2.extras import DictCursor

with get_connection() as conn:
    with conn.cursor(cursor_factory=DictCursor) as cur:
        cur.execute('SELECT COUNT(1) AS count FROM users')
        row = cur.fetchone()
        print(row)  #=> { "count": 123 }


　だがしかし、大概のサイトで、「psycopg2.extras.DictCursor」で、辞書型にすることは、書いてくれているのですが、それ以外の値に関しては何も書かれていないのが大半です。
　辞書型で取得するのは、ありがたいのですが、そうでないときもあるのです。
　試してみて、わかったのが下記の状況です。