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

What you want to achieve

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 image

tenpo_table image

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

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 image

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" image

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. image

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

Recommended Posts

Connect to postgreSQL from Python and use stored procedures in a loop.
How to use is and == in Python
Use Tor to connect from urllib2 [Python] [Mac]
Use libsixel to output Sixel in Python and output a Matplotlib graph to the terminal.
Connect to sqlite from python
Use slackbot as a relay and return from bottle to slack in json format.
I want to write a triple loop and conditional branch in one line in python
How to slice a block multiple array from a multiple array in Python
How to use the __call__ method in a Python class
How to connect to various DBs from Python (PEP 249) and SQLAlchemy
[Introduction to Udemy Python 3 + Application] 36. How to use In and Not
A standard way to develop and distribute packages in Python
Comparison of how to use higher-order functions in Python 2 and 3
Just try to receive a webhook in ngrok and python
[C / C ++] Pass the value calculated in C / C ++ to a python function to execute the process, and use that value in C / C ++.
Read and use Python files from Python
Connect to utf8mb4 database from python
How to use Mysql in python
How to use ChemSpider in Python
How to use PubChem in Python
processing to use notMNIST data in Python (and tried to classify it)
Post a message from IBM Cloud Functions to Slack in Python
How to use functions in separate files Perl and Python versions
How to get a string from a command line argument in python
From installing Ansible to building a Python environment in Vagrant's virtual environment
Check if you can connect to a TCP port in Python
A story about everything from data collection to AI development and Web application release in Python (3. AI development)
Started Python: Swap an array of values obtained from SQL results to a list type and use it in IN of another query
[Introduction to Python] How to use class in Python?
Use print in a Python2 lambda expression
From Python to using MeCab (and CaboCha)
Use PostgreSQL data type (jsonb) from Python
How to install and use pandas_datareader [Python]
Send a message from Python to Slack
I want to use jar from python
Connect a lot of Python or and and
How to get a stacktrace in python
Easy way to use Wikipedia in Python
Porting and modifying doublet-solver from python2 to python3.
python: How to use locals () and globals ()
How to use __slots__ in Python class
Generate a class from a string in Python
How to use Python zip and enumerate
Use Django from a local Python script
How to use regular expressions in Python
Convert from Markdown to HTML in Python
Connect to coincheck's Websocket API from Python
A story I was addicted to when inserting from Python to a PostgreSQL table
Migration from Python2 to Python3 (Python2 is rebuilt as a virtual environment and coexists)
How to get a value from a parameter store in lambda (using python)
How to put a half-width space before letters and numbers in Python.
A memorandum because I stumbled on trying to use MeCab in Python
How to stop a program in python until a specific date and time
Use the LibreOffice app in Python (2) Manipulate calc (from macros and externals)
Send a message from Slack to a Python server
How to use the C library in Python
I want to connect to PostgreSQL from various languages
Organize python modules and packages in a mess
Try to calculate a statistical problem in Python
How to open a web browser from python
How to clear tuples in a list (Python)