Connectez-vous à postgreSQL depuis Python et utilisez des procédures stockées dans une boucle.

Ce que vous voulez réaliser

• Créer une procédure stockée pour postgreSQL
• Connectez-vous à postgreSQL à partir de python et essayez de boucler le fichier stocké.

exemple

Dans la table des paires de produits (pair_table) et la table des magasins de gestion des produits (tenpo_table) suivantes, recherchez l'ensemble de listes de magasins qui gère les deux produits pour chaque ligne.

pair_table

tenpo_table

Table que vous voulez pour la sortie La partie commune de la boutique liée à item_A et la boutique liée à item_B

Procédure stockée

SQL pour obtenir le nom du magasin (shop_name) associé à item_A (= apple) de row_id = 1 de pair_table avec tenpo_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;

Faites de ce qui précède une procédure stockée. Prenez un numéro de ligne (row_id) comme argument.

sample_2.sql

CREATE OR REPLACE FUNCTION get_shopname_x(x integer)
--Élément avec identifiant comme argument_Le nom du magasin qui gère les produits de la rangée A est tenpo_Obtenir de la table
--Lorsque la valeur de retour est une ou plusieurs lignes
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';
--Exécution stockée
SELECT get_shopname_x(1);

Résultat d'exécution Liste des magasins traitant des pommes

De la même manière, pour item_B de pair_table, créez une procédure stockée pour acquérir le magasin de manutention

sample2.sql

CREATE OR REPLACE FUNCTION get_shopname_y(x integer)
--Élément avec identifiant comme argument_Le nom du magasin qui gère les produits de la rangée A est tenpo_Obtenir de la table
--Lorsque la valeur de retour est une ou plusieurs lignes
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';
--Exécution stockée
SELECT get_shopname_y(1);

Résultat d'exécution Liste des magasins traitant "Mikan"

Une procédure stockée qui utilise INTERSECT pour obtenir la partie commune de l'ensemble de get_shopname_x () et get_shopname_y ().

sample_3.sql

--Passez le numéro de série de la ligne du tableau comme 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';

--Interruption de procédure stockée_Exécuter xy

SELECT intersect_xy(1);

Résultat d'exécution Les magasins traitant les deux produits (item_A et item_B, pommes et oranges dans la première ligne) ont été extraits.

Un programme python qui appelle postgreSQL stocké à partir d'une boucle.

J'ai installé un pilote (psycopg2) qui se connecte à postgreSQL depuis python avec pip.

sample.py

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

#Importer le pilote postgreSQL
import psycopg2

#Branchez le connecteur
conn=psycopg2.connect(
     host="localhost",
     port=5432,
     database="testdatabase",
     user="fofofo",
     password="*****")

#Amenez le curseur sur le connecteur
cur= conn.cursor()

#Pair pour spécifier le nombre de boucles_table Récupère le nombre d'enregistrements dans la table
cur.execute("SELECT count(*) FROM pair_table;")
#Engagez-vous explicitement.
conn.commit()
#fetchone()Ainsi, vous ne pouvez obtenir qu'un seul résultat
icn=cur.fetchone()
#Le résultat va dans un tableau unidimensionnel, donc le premier élément[0]Précisez explicitement
#icn[0]alors,"SELECT COUNT(*) FROM pair_table"Peut récupérer le nombre de lignes résultant de

#icn[0]Tournez la boucle
for i in range(1,icn[0]):
    #Donnez le numéro de séquence de la ligne, élément_A et article_Répétez la procédure stockée qui renvoie la partie commune (ensemble de produits) de chaque revendeur avec B en boucle
    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()

Exécutez le programme python ci-dessus à partir du terminal et obtenez le résultat dans un fichier.

test.sh

yuko$python sample.py > output.dat

Remarques

Dans cet exemple, vous pouvez écrire pour obtenir la liste des magasins gérant chaque produit couplé uniquement avec SQL sans le stocker, mais cette fois, j'ose faire de cette façon pour "appeler la procédure stockée de postgreSQL à partir de python et de la boucle" J'ai essayé.

sample_4.sql

--item_Associez les magasins associés à un_Jointure gauche à la 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_Jumeler les magasins associés à b_Jointure gauche à la 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_identifiant et boutique_Extraire des lignes avec exactement les deux mêmes colonnes de nom
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;

Page référencée

Connectez-vous à PostgreSQL à l'aide de psycopg2 Chikuwa Programmer Nikki: Slowly Python

Environnement d'exécution

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