Stellen Sie von Python aus eine Verbindung zu postgreSQL her und verwenden Sie gespeicherte Prozeduren in einer Schleife.

Was Sie erreichen wollen

• Erstellen Sie eine gespeicherte Prozedur für postgreSQL
• Stellen Sie von Python aus eine Verbindung zu postgreSQL her und versuchen Sie, das gespeicherte zu schleifen.

Beispiel

Suchen Sie in der folgenden Produktpaartabelle (pair_table) und der Produkthandhabungsspeichertabelle (tenpo_table) den Speicherlistensatz, der beide Produkte für jede Zeile behandelt.

pair_table

tenpo_table

Tabelle, die Sie für die Ausgabe wünschen Gemeinsame Teile von Geschäften, die mit item_A verknüpft sind, und Geschäfte, die mit item_B verknüpft sind

Gespeicherte Prozedur

SQL, um den mit tenpo_table verknüpften Geschäftsnamen (shop_name) mit item_A (= apple) von row_id = 1 von pair_table abzurufen

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;

Machen Sie das oben Gesagte zu einer gespeicherten Prozedur. Nehmen Sie eine Zeilennummer (row_id) als Argument.

sample_2.sql

CREATE OR REPLACE FUNCTION get_shopname_x(x integer)
--Artikel mit ID als Argument_Der Name des Geschäfts, das die Produkte in Zeile A verwaltet, lautet tenpo_Holen Sie sich vom Tisch
--Wenn der Rückgabewert eine oder mehrere Zeilen beträgt
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';
--Gespeicherte Ausführung
SELECT get_shopname_x(1);

Ausführungsergebnis Liste der Geschäfte, in denen Äpfel verarbeitet werden

Erstellen Sie auf die gleiche Weise für item_B von pair_table eine gespeicherte Prozedur, um den Bearbeitungsspeicher abzurufen

sample2.sql

CREATE OR REPLACE FUNCTION get_shopname_y(x integer)
--Artikel mit ID als Argument_Der Name des Geschäfts, das die Produkte in Zeile A verwaltet, lautet tenpo_Holen Sie sich vom Tisch
--Wenn der Rückgabewert eine oder mehrere Zeilen beträgt
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';
--Gespeicherte Ausführung
SELECT get_shopname_y(1);

Ausführungsergebnis Liste der Geschäfte mit "Mikan"

Eine gespeicherte Prozedur, die INTERSECT verwendet, um den gemeinsamen Teil der Menge von get_shopname_x () und get_shopname_y () abzurufen.

sample_3.sql

--Übergeben Sie die Seriennummer der Tabellenzeile als 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';

--Interrupt der gespeicherten Prozedur_Führen Sie xy aus

SELECT intersect_xy(1);

Ausführungsergebnis Die Geschäfte, die beide Produkte (item_A und item_B, Äpfel und Orangen in der ersten Zeile) handhabten, wurden extrahiert.

Ein Python-Programm, das postgreSQL aufruft, das in einer Schleife gespeichert ist.

Ich habe einen Treiber (psycopg2) installiert, der mit pip eine Verbindung zu postgreSQL von Python herstellt.

sample.py

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

#PostgreSQL-Treiber importieren
import psycopg2

#Schließen Sie den Stecker an
conn=psycopg2.connect(
     host="localhost",
     port=5432,
     database="testdatabase",
     user="fofofo",
     password="*****")

#Bewegen Sie den Cursor auf den Connector
cur= conn.cursor()

#Pair, um die Anzahl der Schleifen anzugeben_Tabelle Ruft die Anzahl der Datensätze in der Tabelle ab
cur.execute("SELECT count(*) FROM pair_table;")
#Explizit festlegen.
conn.commit()
#fetchone()Sie können also nur ein Ergebnis erhalten
icn=cur.fetchone()
#Das Ergebnis geht in ein eindimensionales Array, also das erste Element[0]Explizit angeben
#icn[0]damit,"SELECT COUNT(*) FROM pair_table"Kann die Anzahl der Zeilen abrufen, die sich aus ergeben

#icn[0]Drehen Sie die Schleife
for i in range(1,icn[0]):
    #Geben Sie die Sequenznummer der Zeile und des Elements an_A und Artikel_Wiederholen Sie die gespeicherte Prozedur, die den gemeinsamen Teil (Produktsatz) jedes Händlers mit B in einer Schleife zurückgibt
    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()

Führen Sie das obige Python-Programm vom Terminal aus und erhalten Sie das Ergebnis in einer Datei.

test.sh

yuko$python sample.py > output.dat

Bemerkungen

In diesem Beispiel können Sie schreiben, um die Liste der Geschäfte abzurufen, die jedes gepaarte Produkt nur mit SQL verarbeiten, ohne es zu speichern. Dieses Mal wage ich es jedoch, auf diese Weise "die gespeicherte Prozedur von postgreSQL von Python und Loop aufzurufen". Ich habe es versucht.

sample_4.sql

--item_Koppeln Sie die mit a verknüpften Geschäfte_Linke Verbindung zum Tisch
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_Koppeln Sie die mit b verbundenen Geschäfte_Linke Verbindung zum Tisch
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 und Shop_Extrahieren Sie Zeilen mit genau denselben zwei Namensspalten
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;

Referenzierte Seite

Mit psycopg2 eine Verbindung zu PostgreSQL herstellen Chikuwa-Programmierer Nikki: Langsam Python

Ausführungsumgebung

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