PythonからpostgreSQLに接続し、ストアドプロシジャーをループで使う。

実現したいこと

• postgreSQLのストアドプロシジャーを作成する
• pythonからpostgreSQLに接続し、ストアドをループで回してみる。

例題

　次のような商品のペアのテーブル（pair_table)と、商品ー取扱店舗テーブル(tenpo_table)から、行ごとに２つの商品のいずれも取扱っている店舗リスト集合を求めます。

pair_table

tenpo_table

アウトプットにほしいテーブル
item_Aに紐付く店舗とitem_Bに紐付く店舗の共通部分

ストアドプロシジャー

pair_tableのrow_id=1のitem_A(=りんご）にtenpo_tableで紐付く店舗名(shop_name）を取得するSQL

sample_1.sql

SELECT shop_name
FROM tenpo_table s
   WHERE EXISTS (SELECT *
     FROM  pair_table t
     WHERE t.item_a =s.item
     AND t.row_id = 1;

上記をストアドプロシジャーにする。引数には行番号(row_id)を取る。

sample_2.sql

CREATE OR REPLACE FUNCTION get_shopname_x(x integer)
--idを引数にして、item_A列の商品を取り扱う店舗名を tenpo_tableから取得する
--戻り値が1行以上になる場合
RETURNS SETOF VARCHAR AS
$$
    SELECT shop_name
    FROM tenpo_table s
    WHERE EXISTS(SELECT *
    FROM pair_table t
      WHERE row_id = x
      AND t.item_a = s.item);
$$LANGUAGE 'sql';
--ストアドの実行
SELECT get_shopname_x(1);

実行結果
りんごの取扱店舗リスト

同じようにpair_tableのitem_Bに対して、取扱店舗を取得するストアドプロシジャーを作る

sample2.sql

CREATE OR REPLACE FUNCTION get_shopname_y(x integer)
--idを引数にして、item_A列の商品を取り扱う店舗名を tenpo_tableから取得する
--戻り値が1行以上になる場合
RETURNS SETOF VARCHAR AS
$$
    SELECT shop_name
    FROM tenpo_table s
    WHERE EXISTS(SELECT *
    FROM pair_table t
      WHERE row_id = x
      AND t.item_b = s.item);
$$LANGUAGE 'sql';
--ストアドの実行
SELECT get_shopname_y(1);

実行結果
「みかん」の取扱店舗リスト

get_shopname_x()の結果とget_shopname_y()の集合の共通部分をINTERSECTで取得するストアドプロシジャー

sample_3.sql

--引数には、テーブルの行の連番を渡す
CREATE OR REPLACE FUNCTION intersect_xy(x integer)
RETURNS SETOF VARCHAR AS
$$
SELECT get_shopname_x(x)
INTERSECT
SELECT get_shopname_y(x)
$$LANGUAGE 'sql';

--ストアドプロシジャーintersect_xyの実行

SELECT intersect_xy(1);

実行結果
２つの商品(item_Aとitem_B、１行目ではりんごとみかん)のいずれも取り扱う店舗が抽出された。

postgreSQLのストアドをループから呼び出すpythonプログラム。

pythonからpostgreSQLに接続するドライバ(psycopg2)をpipでインストールしました。

sample.py

# -*-coding:utf-8 -*-

# postgreSQLのドライバをインポート
import psycopg2

# コネクタを接続する
conn=psycopg2.connect(
     host="localhost",
     port=5432,
     database="testdatabase",
     user="fofofo",
     password="*****")

# コネクタへのカーソルを取得
cur= conn.cursor()

# ループの回数を指定するため、pair_tableテーブルのレコード数を取得する
cur.execute("SELECT count(*) FROM pair_table;")
# 明示的にコミットする。
conn.commit()
# fetchone()で、１つだけの結果を取り出せる
icn=cur.fetchone()
# 結果は１次元配列に入るので、要素の１個目[0]を明示的に指定する
# icn[0]で、"SELECT COUNT(*) FROM pair_table"の結果の行数を取り出せる

# icn[0]回ループを回す
for i in range(1,icn[0]):
    # 行の連番を与えて、item_Aとitem_Bとのそれぞれの取扱店の共通部分（積集合）を返すストアドプロシージャをループで繰り返す
    sql="SELECT intersect_xy(%d)" % i
    cur.execute(sql)
    conn.commit()
    rows=cur.fetchall()
    for row in rows:
         print '%4d\t %s' % (i,row[0])
    
cur.close()
conn.close()

terminalから上記のpythonプログラムを実行し結果をファイルに取得する。

test.sh

yuko$python sample.py > output.dat

備考

この例題は、ストアドにしなくてもSQLだけでペア商品ごとの取扱店舗リストを取得するように記述できますが、今回は「postgreSQLのストアドプロシジャーをpythonから呼び出しループさせる」ためにあえてこのような方法を試行しました。

sample_4.sql

--item_aに紐付く店舗をpair_tableに左結合
CREATE TEMP TABLE item_a_shop AS
SELECT 
  t.row_id,
  t.item_a,
  s.shop_name
  FROM pair_table t
  LEFT JOIN tenpo_table s
   ON t.item_a=s.item;
  

CREATE TEMP TABLE item_b_shop AS
--item_bに紐付く店舗をpair_tableに左結合
SELECT 
  t.row_id,
  t.item_b,
  s.shop_name AS shop_b
  FROM pair_table t
  LEFT JOIN tenpo_table s
   ON t.item_b=s.name;
  
--row_idと、shop_nameの２列が全く共通の行を抽出
SELECT 
  t.row_id,
  t.shop_name
  FROM item_a_shop t
  INTERSECT
  SELECT
  s.row_id,
  s.shop_b
  FROM item_b_shop s;

参考にしたページ

psycopg2 を使って PostgreSQL に接続する
ちくわプログラマにっき:ゆっくりPython

実行環境

• postgreSQL9.5
• python 2.7.11
• psycopy2
• Eclipse Mars.2 Release (4.5.2)