psycopg2: füge mehrere Zeilen mit einer Abfrage ein

Lesezeit: 1 Minute

Benutzer-Avatar
Sergej Fedosejew

Ich muss mehrere Zeilen mit einer Abfrage einfügen (die Anzahl der Zeilen ist nicht konstant), also muss ich eine Abfrage wie diese ausführen:

INSERT INTO t (a, b) VALUES (1, 2), (3, 4), (5, 6);

Der einzige Weg, den ich kenne, ist

args = [(1,2), (3,4), (5,6)]
args_str=",".join(cursor.mogrify("%s", (x, )) for x in args)
cursor.execute("INSERT INTO t (a, b) VALUES "+args_str)

aber ich möchte einen einfacheren Weg.

Benutzer-Avatar
Ameise32

Ich habe ein Programm erstellt, das mehrere Zeilen zu einem Server einfügt, der sich in einer anderen Stadt befindet.

Ich fand heraus, dass die Verwendung dieser Methode etwa 10-mal schneller war als executemany. In meinem Fall tup ist ein Tupel mit etwa 2000 Zeilen. Bei dieser Methode dauerte es ungefähr 10 Sekunden:

args_str=",".join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup)
cur.execute("INSERT INTO table VALUES " + args_str) 

und 2 Minuten bei dieser Methode:

cur.executemany("INSERT INTO table VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)", tup)

  • Fast zwei Jahre später immer noch sehr aktuell. Eine heutige Erfahrung legt nahe, dass es umso besser ist, die zu verwenden, je mehr Zeilen Sie pushen möchten execute Strategie. Ich habe dadurch eine Beschleunigung von etwa 100x gesehen!

    – Rob Watts

    22. Januar 2014 um 21:16 Uhr


  • Vielleicht executemany führt nach jedem Einfügen einen Commit aus. Wenn Sie stattdessen das Ganze in eine Transaktion einpacken, würde das vielleicht die Dinge beschleunigen?

    – Richard

    12. April 2015 um 17:06 Uhr

  • Ich habe diese Verbesserung gerade selbst bestätigt. Von dem, was ich psycopg2’s gelesen habe executemany macht nichts optimal, macht nur Schleifen und macht viele execute Aussagen. Mit dieser Methode ging eine Einfügung von 700 Zeilen auf einen Remote-Server von 60 Sekunden auf <2 Sekunden.

    – Nelson

    27. April 2015 um 23:22 Uhr

  • Vielleicht bin ich paranoid, aber verkette die Abfrage mit a + Es scheint, als könnte es sich für SQL-Injektionen öffnen, ich fühle mich wie @Clodoaldo Neto execute_values() Lösung ist sicherer.

    – Will Munn

    17. Januar 2018 um 11:55 Uhr


  • falls jemand auf den folgenden Fehler stößt: [TypeError: sequence item 0: expected str instance, bytes found] Führen Sie stattdessen diesen Befehl aus [args_str = ‘,’.join(cur.mogrify(“(%s,%s)”, x).decode(“utf-8”) for x in tup)]

    – Hr

    3. September 2018 um 23:08 Uhr


Benutzer-Avatar
Clodoaldo Neto

Neu execute_values Methode in Psycopg 2.7:

data = [(1,'x'), (2,'y')]
insert_query = 'insert into t (a, b) values %s'
psycopg2.extras.execute_values (
    cursor, insert_query, data, template=None, page_size=100
)

Die pythonische Vorgehensweise in Psycopg 2.6:

data = [(1,'x'), (2,'y')]
records_list_template=",".join(['%s'] * len(data))
insert_query = 'insert into t (a, b) values {}'.format(records_list_template)
cursor.execute(insert_query, data)

Erläuterung: Wenn die einzufügenden Daten als Liste von Tupeln wie in

data = [(1,'x'), (2,'y')]

dann liegt es schon im exakt benötigten Format vor

  1. das values Syntax der insert -Klausel erwartet eine Liste von Datensätzen wie in

    insert into t (a, b) values (1, 'x'),(2, 'y')

  2. Psycopg adaptiert einen Python tuple zu einem Postgresql record.

Die einzige notwendige Arbeit besteht darin, eine Datensatzlistenvorlage bereitzustellen, die von psycopg ausgefüllt werden muss

# We use the data list to be sure of the template length
records_list_template=",".join(['%s'] * len(data))

und legen Sie es in die insert Anfrage

insert_query = 'insert into t (a, b) values {}'.format(records_list_template)

Drucken der insert_query Ausgänge

insert into t (a, b) values %s,%s

Nun zum Üblichen Psycopg Argumente ersetzen

cursor.execute(insert_query, data)

Oder einfach testen, was an den Server gesendet wird

print (cursor.mogrify(insert_query, data).decode('utf8'))

Ausgabe:

insert into t (a, b) values (1, 'x'),(2, 'y')

  • Wie verhält sich die Leistung dieser Methode im Vergleich zu cur.copy_from?

    – Michael Goldshteyn

    3. März 2016 um 22:05 Uhr

  • Hier ist eine Zusammenfassung mit einem Benchmark. copy_from skaliert auf meinem Computer mit 10 Millionen Datensätzen etwa 6,5-mal schneller.

    – Josef Sheedy

    13. Juli 2016 um 0:28 Uhr

  • verwenden execute_values Ich konnte mein System mit 1.000 Datensätzen pro Minute bis zu 128.000 Datensätzen pro Minute zum Laufen bringen

    – Conrad.Dean

    15. November 2018 um 15:17 Uhr

  • Meine Beilagen wurden nicht richtig registriert, bis ich anrief connection.commit() nach dem execute_values(...).

    – Philipp

    18. August 2020 um 15:24 Uhr

  • @Phillipp das ist bei jeder Ausführungsanweisung normal, es sei denn, Sie befinden sich im Auto-Commit-Modus.

    – Chris

    3. September 2020 um 10:17 Uhr

Benutzer-Avatar
Antoine Dusséaux

Update mit psycopg2 2.7:

Der Klassiker executemany() ist etwa 60-mal langsamer als die Implementierung von @ ant32 (genannt “folded”), wie in diesem Thread erklärt: https://www.postgresql.org/message-id/20170130215151.GA7081%40deb76.aryehleib.com

Diese Implementierung wurde in Version 2.7 zu psycopg2 hinzugefügt und heißt execute_values():

from psycopg2.extras import execute_values
execute_values(cur,
    "INSERT INTO test (id, v1, v2) VALUES %s",
    [(1, 2, 3), (4, 5, 6), (7, 8, 9)])

Vorherige Antwort:

Um mehrere Zeilen einzufügen, verwenden Sie multirow VALUES Syntax mit execute() ist etwa 10x schneller als die Verwendung von psycopg2 executemany(). In der Tat, executemany() läuft einfach viele einzelne INSERT Aussagen.

Der Code von @ ant32 funktioniert perfekt in Python 2. Aber in Python 3, cursor.mogrify() gibt Bytes zurück, cursor.execute() nimmt entweder Bytes oder Strings und ','.join() erwartet str Beispiel.

In Python 3 müssen Sie also möglicherweise den Code von @ ant32 ändern, indem Sie hinzufügen .decode('utf-8'):

args_str=",".join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x).decode('utf-8') for x in tup)
cur.execute("INSERT INTO table VALUES " + args_str)

Oder durch die Verwendung von Bytes (mit b'' oder b"") nur:

args_bytes = b','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup)
cur.execute(b"INSERT INTO table VALUES " + args_bytes) 

  • Danke, die aktualisierte Antwort funktioniert gut. Bitte vergessen Sie nicht, conn.commit() auszuführen, um Änderungen beizubehalten.

    – RicarHincapie

    17. Oktober 2021 um 21:52 Uhr

  • So ist execute_values() schneller als @ant32?

    – étale-Kohomologie

    26. November 2021 um 8:29 Uhr

Benutzer-Avatar
Josef Sheedy

cursor.copy_from ist mit Abstand die schnellste Lösung, die ich für Massenbeilagen gefunden habe. Hier ist eine Zusammenfassung Ich habe eine Klasse namens IteratorFile enthalten, die es einem Iterator ermöglicht, Strings wie eine Datei zu lesen. Wir können jeden Eingabedatensatz mithilfe eines Generatorausdrucks in eine Zeichenfolge konvertieren. Die Lösung wäre also

args = [(1,2), (3,4), (5,6)]
f = IteratorFile(("{}\t{}".format(x[0], x[1]) for x in args))
cursor.copy_from(f, 'table_name', columns=('a', 'b'))

Für diese triviale Größe von Argumenten macht es keinen großen Geschwindigkeitsunterschied, aber ich sehe große Beschleunigungen, wenn ich mit Tausenden von Zeilen umgehe. Es ist auch speichereffizienter als das Erstellen einer riesigen Abfragezeichenfolge. Ein Iterator würde immer nur einen Eingabedatensatz gleichzeitig im Speicher halten, wobei Ihnen irgendwann der Speicher in Ihrem Python-Prozess oder in Postgres ausgeht, wenn Sie die Abfragezeichenfolge erstellen.

Benutzer-Avatar
ptrn

Ein Ausschnitt aus der Tutorial-Seite von Psycopg2 unter Postgresql.org (siehe unten):

Ein letztes Element, das ich Ihnen zeigen möchte, ist das Einfügen mehrerer Zeilen mithilfe eines Wörterbuchs. Wenn Sie Folgendes hätten:

namedict = ({"first_name":"Joshua", "last_name":"Drake"},
            {"first_name":"Steven", "last_name":"Foo"},
            {"first_name":"David", "last_name":"Bar"})

Sie können alle drei Zeilen einfach in das Wörterbuch einfügen, indem Sie Folgendes verwenden:

cur = conn.cursor()
cur.executemany("""INSERT INTO bar(first_name,last_name) VALUES (%(first_name)s, %(last_name)s)""", namedict)

Es spart nicht viel Code, aber es sieht definitiv besser aus.

  • Dies wird viele einzelne ausführen INSERT Aussagen. Nützlich, aber nicht dasselbe wie ein einzelnes Multi-VALUEd einfügen.

    – Craig Ringer

    9. April 2013 um 2:26 Uhr

  • Und im selben Dokument steht die Anweisung cur.executemany, die automatisch das Wörterbuch durchläuft und die INSERT-Abfrage für jede Zeile ausführt.

    – sp1rs

    21. Januar 2021 um 5:09 Uhr


Alle diese Techniken werden in der Postgres-Terminologie „Extended Inserts“ genannt, und am 24. November 2016 ist es immer noch eine Tonne schneller als die executemany() von psychopg2 und alle anderen Methoden, die in diesem Thread aufgeführt sind (die ich ausprobiert habe, bevor ich dazu kam Antworten).

Hier ist ein Code, der cur.mogrify nicht verwendet und nett und einfach ist, um sich zurechtzufinden:

valueSQL = [ '%s', '%s', '%s', ... ] # as many as you have columns.
sqlrows = []
rowsPerInsert = 3 # more means faster, but with diminishing returns..
for row in getSomeData:
        # row == [1, 'a', 'yolo', ... ]
        sqlrows += row
        if ( len(sqlrows)/len(valueSQL) ) % rowsPerInsert == 0:
                # sqlrows == [ 1, 'a', 'yolo', 2, 'b', 'swag', 3, 'c', 'selfie' ]
                insertSQL = 'INSERT INTO "twitter" VALUES ' + ','.join(['(' + ','.join(valueSQL) + ')']*rowsPerInsert)
                cur.execute(insertSQL, sqlrows)
                con.commit()
                sqlrows = []
insertSQL = 'INSERT INTO "twitter" VALUES ' + ','.join(['(' + ','.join(valueSQL) + ')']*len(sqlrows))
cur.execute(insertSQL, sqlrows)
con.commit()

Aber es sollte beachtet werden, dass, wenn Sie copy_from() verwenden können, Sie copy_from verwenden sollten;)

  • Dies wird viele einzelne ausführen INSERT Aussagen. Nützlich, aber nicht dasselbe wie ein einzelnes Multi-VALUEd einfügen.

    – Craig Ringer

    9. April 2013 um 2:26 Uhr

  • Und im selben Dokument steht die Anweisung cur.executemany, die automatisch das Wörterbuch durchläuft und die INSERT-Abfrage für jede Zeile ausführt.

    – sp1rs

    21. Januar 2021 um 5:09 Uhr


Benutzer-Avatar
jprockbauch

Ich verwende die obige Antwort von ant32 seit mehreren Jahren. Ich habe jedoch festgestellt, dass dies ein Fehler in Python 3 ist, weil mogrify gibt einen Byte-String zurück.

Das explizite Konvertieren in Bytse-Strings ist eine einfache Lösung, um Code mit Python 3 kompatibel zu machen.

args_str = b','.join(cur.mogrify("(%s,%s,%s,%s,%s,%s,%s,%s,%s)", x) for x in tup) 
cur.execute(b"INSERT INTO table VALUES " + args_str)

  • Eine einfachere Option wäre die Dekodierung cur.mogrify()

    – Hemil

    14. August 2020 um 12:36 Uhr

1038550cookie-checkpsycopg2: füge mehrere Zeilen mit einer Abfrage ein

This website is using cookies to improve the user-friendliness. You agree by using the website further.

Privacy policy