Python - よく使うモジュール - データベース - psycopg PostgreSQL 接続

1.　概要

2.　インストール

3.　接続

4.　トランザクション

5.　SQL の実行

6.　変数展開

7.　バインド変数

8.　辞書型で取得

9.　参考サイト

1.　概要

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

2.　インストール

　さすがにこれは、インストールが必要ですな。
　「FreeBSD」の場合、「ports」「pkg」でもインストールできそうです。ありがたい。


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


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

3.　接続

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


import psycopg

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


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

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

4.　トランザクション

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

5.　SQL の実行


create table 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

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 }


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

9.　参考サイト

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

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