Connect to postgreSQL from Python and use stored procedures in a loop.

What you want to achieve

• Create a postgreSQL stored procedure
• Connect to postgreSQL from python and try to loop the stored.

example

From the following product pair table (pair_table) and product-handling store table (tenpo_table), find the store list set that handles both of the two products for each row.

pair_table

tenpo_table

The table you want for output Common parts of stores associated with item_A and stores associated with item_B

Stored procedure

SQL to get the store name (shop_name) linked with tenpo_table to item_A (= apple) of row_id = 1 of pair_table

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;

Make the above a stored procedure. Take a row number (row_id) as an argument.

sample_2.sql

CREATE OR REPLACE FUNCTION get_shopname_x(x integer)
--With id as an argument, item_The name of the store that handles the products in column A is tenpo_Get from table
--When the return value is one or more lines
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';
--Stored execution
SELECT get_shopname_x(1);

Execution result List of stores handling apples

Similarly, for item_B of pair_table, create a stored procedure to acquire the handling store.

sample2.sql

CREATE OR REPLACE FUNCTION get_shopname_y(x integer)
--With id as an argument, item_The name of the store that handles the products in column A is tenpo_Get from table
--When the return value is one or more lines
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';
--Stored execution
SELECT get_shopname_y(1);

Execution result List of stores handling "Mikan"

A stored procedure that uses INTERSECT to get the intersection of the result of get_shopname_x () and the set of get_shopname_y ().

sample_3.sql

--Pass the serial number of the table row as an argument
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';

--Stored procedure interrupt_xy execution

SELECT intersect_xy(1);

Execution result Stores handling both of the two products (item_A and item_B, apples and oranges in the first line) were extracted.

A python program that calls a postgreSQL stored from a loop.

I installed the driver (psycopg2) that connects to postgreSQL from python with pip.

sample.py

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

#Import postgreSQL driver
import psycopg2

#Connect the connector
conn=psycopg2.connect(
     host="localhost",
     port=5432,
     database="testdatabase",
     user="fofofo",
     password="*****")

#Get the cursor to the connector
cur= conn.cursor()

#Pair to specify the number of loops_table Get the number of records in the table
cur.execute("SELECT count(*) FROM pair_table;")
#Commit explicitly.
conn.commit()
#fetchone()So you can get only one result
icn=cur.fetchone()
#The result goes into a one-dimensional array, so the first element[0]Explicitly specify
#icn[0]so,"SELECT COUNT(*) FROM pair_table"Can retrieve the number of rows resulting from

#icn[0]Turn the loop
for i in range(1,icn[0]):
    #Give the sequence number of the line, item_A and item_Repeat the stored procedure that returns the intersection (product set) of each dealer with B in a loop
    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()

Execute the above python program from terminal and get the result to a file.

test.sh

yuko$python sample.py > output.dat

Remarks

In this example, you can write to get the list of stores handling each paired product only with SQL without making it stored, but this time, I dare to use this method to "call the stored procedure of postgreSQL from python and loop". I tried.

sample_4.sql

--item_Pair the stores associated with a_Left join to 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_Pair the stores associated with b_Left join to 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 and shop_Extract rows with exactly the same two columns of 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;

Referenced page

Connect to PostgreSQL using psycopg2 Chikuwa Programmer Nikki: Slowly Python

Execution environment

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