Интерфейс Python-PostgreSQL psycopg2 - ›executemany

просто скопируйте все данные в рабочую таблицу с помощью команды psql \ copy или используйте метод psycopg cursor.copy_in (). Потом:

insert into mytable
select * from (
    select distinct * 
    from scratch
) uniq
where not exists (
    select 1 
    from mytable 
    where mytable.mykey = uniq.mykey
);

Это будет дедупликация и выполняется намного быстрее, чем любая комбинация вставок.

-dg

У меня была та же проблема, и я искал здесь много дней, чтобы собрать много подсказок, чтобы сформировать полное решение. Даже если вопрос устарел, надеюсь, это будет полезно другим.

1) Забудьте об удалении индексов / ограничений и их повторном создании позже, преимущества незначительны или хуже.

2) Executemany лучше, чем execute, поскольку оно делает за вас инструкцию подготовки. Вы можете получить те же результаты самостоятельно, выполнив следующую команду, чтобы увеличить скорость на 300%:

# To run only once:
sqlCmd = """PREPARE myInsert (int, timestamp, real, text) AS
   INSERT INTO myBigTable (idNumber, date_obs, result, user)
     SELECT $1, $2, $3, $4 WHERE NOT EXISTS
     (SELECT 1 FROM myBigTable WHERE (idNumber, date_obs, user)=($1, $2, $4));"""
curPG.execute(sqlCmd)
cptInsert = 0   # To let you commit from time to time

#... inside the big loop:
curPG.execute("EXECUTE myInsert(%s,%s,%s,%s);", myNewRecord)
allreadyExists = (curPG.rowcount < 1)
if not allreadyExists:
   cptInsert += 1
   if cptInsert % 10000 == 0:
      conPG.commit()

Этот пример фиктивной таблицы имеет уникальное ограничение на (idNumber, date_obs, user).

3) Лучшее решение - использовать COPY_FROM и TRIGGER для управления уникальным ключом ПЕРЕД ВСТАВКОЙ. Это дало мне в 36 раз больше скорости. Я начал с обычных вставок со скоростью 500 записей / сек. а с «копией» я получил более 18 000 записей в секунду. Пример кода на Python с Psycopg2:

ioResult = StringIO.StringIO() #To use a virtual file as a buffer
cptInsert = 0 # To let you commit from time to time - Memory has limitations
#... inside the big loop:
   print >> ioResult, "\t".join(map(str, myNewRecord))
   cptInsert += 1
   if cptInsert % 10000 == 0:
      ioResult = flushCopyBuffer(ioResult, curPG)
#... after the loop:
ioResult = flushCopyBuffer(ioResult, curPG)

def flushCopyBuffer(bufferFile, cursorObj):
   bufferFile.seek(0)   # Little detail where lures the deamon...
   cursorObj.copy_from(bufferFile, 'myBigTable',
      columns=('idNumber', 'date_obs', 'value', 'user'))
   cursorObj.connection.commit()
   bufferFile.close()
   bufferFile = StringIO.StringIO()
   return bufferFile

Вот и все, что касается Python. Теперь триггер Postgresql не должен иметь исключения psycopg2.IntegrityError, а затем все записи команды COPY отклонены:

CREATE OR REPLACE FUNCTION chk_exists()
  RETURNS trigger AS $BODY$
DECLARE
    curRec RECORD;
BEGIN
   -- Check if record's key already exists or is empty (file's last line is)
   IF NEW.idNumber IS NULL THEN
      RETURN NULL;
   END IF;
   SELECT INTO curRec * FROM myBigTable
      WHERE (idNumber, date_obs, user) = (NEW.idNumber, NEW.date_obs, NEW.user);
   IF NOT FOUND THEN -- OK keep it
      RETURN NEW;
   ELSE    
      RETURN NULL; -- Oups throw it or update the current record
   END IF;
END;
$BODY$ LANGUAGE plpgsql;

Теперь свяжите эту функцию с триггером вашей таблицы:

CREATE TRIGGER chk_exists_before_insert
   BEFORE INSERT ON myBigTable FOR EACH ROW EXECUTE PROCEDURE chk_exists();

Это кажется большим трудом, но Postgresql - очень быстрый зверь, когда ему не нужно интерпретировать SQL снова и снова. Развлекайся.

«Когда мой скрипт обнаруживает такую ​​не УНИКАЛЬНУЮ ВСТАВКУ, он connection.rollback () (который составляет до 1000 строк каждый раз и как бы делает бессмысленным выполнение всех операций), а затем ВСТАВЛЯЕТ все значения одно за другим».

Вопрос действительно не имеет большого смысла.

КАЖДЫЙ блок из 1000 строк терпит неудачу из-за неуникальных строк?

Выходит ли из строя 1 блок из 1000 строк (из 5000 таких блоков)? Если это так, то выполнение многих помогает для 4 999 из 5 000 и далеко не «бесполезно».

Вас беспокоит эта неуникальная вставка? Или у вас есть актуальная статистика того, сколько раз это происходило?

Если вы переключились с 1000 блоков строк на 100 блоков строк, вы можете - очевидно - определить, есть ли преимущество в производительности для блоков 1000 строк, 100 блоков строк и 1 блока строк.

Пожалуйста, запустите фактическую программу с реальной базой данных и блоками разного размера и опубликуйте числа.

использование оператора MERGE вместо INSERT решит вашу проблему.