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
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.
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
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;
Connect to PostgreSQL using psycopg2 Chikuwa Programmer Nikki: Slowly Python
Recommended Posts