python - statement - psycopg2: Fügt mehrere Zeilen mit einer Abfrage ein




python3 postgresql (9)

Ich muss mehrere Zeilen mit einer Abfrage einfügen (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.


Alle diese Techniken werden in der Postgres-Terminologie als "Extended Inserts" bezeichnet und sind seit dem 24. November 2016 immer noch eine Tonne schneller als die von psychopg2 executemany () und alle anderen in diesem Thread aufgelisteten Methoden (die ich vorher versucht habe) Antworten).

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

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 wenn du copy_from () verwenden kannst, solltest du copy_from;) verwenden.


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

Ein letzter Punkt, den ich Ihnen zeigen möchte, ist, wie Sie mehrere Zeilen mit einem Wörterbuch einfügen. Wenn du folgendes hättest:

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

Sie können einfach alle drei Zeilen im 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.


Führen Sie die Stapelverarbeitung mit Hilfe von Datensatzvorlagen mit psycopg2 aus!

def get_batch(iterable, size=1):
    for i in range(0, len(iterable), size):
        yield iterable[i: i + size]


def insert_rows_batch(table, rows, batch_size=500, target_fields=None):
    """
    A utility method to insert batch of tuples(rows) into a table
    NOTE: Handle data type for fields in rows yourself as per your table 
    columns' type.
    :param table: Name of the target table
    :type table: str
    :param rows: The rows to insert into the table
    :type rows: iterable of tuples
    :param batch_size: The size of batch of rows to insert at a time
    :type batch_size: int
    :param target_fields: The names of the columns to fill in the table
    :type target_fields: iterable of strings
    """
    if target_fields:
        target_fields = ", ".join(target_fields)
        target_fields = "({})".format(target_fields)
    else:
        target_fields = ''

    conn = get_conn() # get connection using psycopg2
    cur = conn.cursor()
    count = 0

    for mini_batch in get_batch(rows, batch_size):
        mini_batch_size = len(mini_batch)
        count += mini_batch_size
        record_template = ','.join(["%s"] * mini_batch_size)
        sql = "INSERT INTO {0} {1} VALUES {2};".format(
            table,
            target_fields,
            record_template)
        cur.execute(sql, mini_batch)
        conn.commit()
        print("Loaded {} rows into {} so far".format(count, table))
    print("Done loading. Loaded a total of {} rows".format(count))
    cur.close()
    conn.close()

Wenn Sie UPSERT (Insert + Update) auch in Postgres mit Batches postgres_utilities : postgres_utilities


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

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

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 Verwendung dieser Methode:

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

In der SQLalchemy1.2-Version wird diese neue Implementierung hinzugefügt, um psycopg2.extras.execute_batch () anstelle von executemany zu verwenden, wenn Sie Ihre Engine mit use_batch_mode = True initialisieren:

engine = create_engine(
    "postgresql+psycopg2://scott:[email protected]/dbname",
    use_batch_mode=True)

http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#change-4109

Dann würde jemand SQLalchmey verwenden müssen, wird nicht versuchen, verschiedene Kombinationen von sqla und psycopg2 und direkte SQL zusammen zu versuchen ..


Neue Methode execute_values ​​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 Art, es in Psycopg 2.6 zu tun:

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 angegeben werden

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

dann ist es schon im genau benötigten Format als

  1. Die Wertesyntax der insert Klausel erwartet eine Liste von Datensätzen wie in

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

  2. Psycopg passt ein Python- tuple an einen Postgresql- record .

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

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

und platziere es in der Einfügeabfrage

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

Drucken der insert_query Ausgaben

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

Jetzt zu den üblichen Psycopg Argumenten Substitution

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')

Wenn Sie mehrere Zeilen innerhalb eines Einfüge-Statements einfügen möchten (vorausgesetzt, Sie verwenden kein ORM), wäre der einfachste Weg für mich bisher, eine Liste von Wörterbüchern zu verwenden. Hier ist ein Beispiel:

 t = [{'id':1, 'start_date': '2015-07-19 00:00:00', 'end_date': '2015-07-20 00:00:00', 'campaignid': 6},
      {'id':2, 'start_date': '2015-07-19 00:00:00', 'end_date': '2015-07-20 00:00:00', 'campaignid': 7},
      {'id':3, 'start_date': '2015-07-19 00:00:00', 'end_date': '2015-07-20 00:00:00', 'campaignid': 8}]

conn.execute("insert into campaign_dates
             (id, start_date, end_date, campaignid) 
              values (%(id)s, %(start_date)s, %(end_date)s, %(campaignid)s);",
             t)

Wie Sie sehen, wird nur eine Abfrage ausgeführt:

INFO sqlalchemy.engine.base.Engine insert into campaign_dates (id, start_date, end_date, campaignid) values (%(id)s, %(start_date)s, %(end_date)s, %(campaignid)s);
INFO sqlalchemy.engine.base.Engine [{'campaignid': 6, 'id': 1, 'end_date': '2015-07-20 00:00:00', 'start_date': '2015-07-19 00:00:00'}, {'campaignid': 7, 'id': 2, 'end_date': '2015-07-20 00:00:00', 'start_date': '2015-07-19 00:00:00'}, {'campaignid': 8, 'id': 3, 'end_date': '2015-07-20 00:00:00', 'start_date': '2015-07-19 00:00:00'}]
INFO sqlalchemy.engine.base.Engine COMMIT

[Update mit psycopg2 2.7]

Das klassische executemany() ist etwa 60 mal langsamer als die Implementierung von @ ant32 ("gefaltet"), 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, ist die Verwendung der multirow- VALUES Syntax mit VALUES execute() etwa 10x schneller als die Verwendung von psycopg2 executemany() . In der Tat führt executemany() nur viele einzelne INSERT Anweisungen aus.

@ ant32 's Code funktioniert perfekt in Python 2. Aber in Python 3 gibt cursor.mogrify() Bytes zurück, cursor.execute() nimmt entweder Bytes oder Zeichenketten und ','.join() erwartet eine str Instanz.

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

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 indem Sie nur Bytes (mit b'' oder b"" ) verwenden:

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) 

AiOpg verwenden - Das folgende Snippet funktioniert einwandfrei

    # items = [10, 11, 12, 13]
    # group = 1
    tup = [(gid, pid) for pid in items]
    args_str = ",".join([str(s) for s in tup])
    # insert into group values (1, 10), (1, 11), (1, 12), (1, 13)
    yield from cur.execute("INSERT INTO group VALUES " + args_str)




psycopg2