Postgresql: вставьте из огромного CSV-файла, соберите идентификаторы и соблюдайте уникальные ограничения.

В базе данных postgresql:

class Persons(models.Model):
    person_name = models.CharField(max_length=10, unique=True)

Файл persons.csv содержит 1 миллион имен.

$cat persons.csv
Name-1
Name-2
...
Name-1000000

Я хочу:

  1. Создайте имена, которых еще нет
  2. Запросите базу данных и получите идентификатор для каждого имени, содержащегося в файле csv.

Мой подход:

  1. Используйте команду COPY или django-postgres-copy, которое его реализует.

    Также воспользуйтесь преимуществами нового Postgresql-9.5+ функция upsert.

  2. Теперь все имена в CSV-файле также находятся в базе данных.

    Мне нужно получить их идентификаторы из базы данных либо в памяти, либо в другом CSV-файле эффективным способом:

    • Используйте объекты Q

      list_of_million_q = <iterate csv and append Qs>
million_names = Names.objects.filter(list_of_million_q)

      или

    • Используйте __in для фильтрации на основе списка имен:

      list_of_million_names = <iterate csv and append strings>
million_names = Names.objects.filter(
    person_name__in=[list_of_million_names]
)

      или

      ?

Я не считаю, что какой-либо из вышеперечисленных подходов для получения идентификаторов эффективен.

Обновить

Существует третий вариант, похожий на этот пост, который должен быть отличным решением, сочетающим в себе все вышеперечисленное.


python django postgresql django-queryset django-postgresql
person raratiru    schedule 15.04.2018    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Что-то типа:

SELECT * FROM persons;

составить словарь name:id из имен, полученных из базы данных:

db_dict = {'Harry': 1, 'Bob': 2, ...}

Запрос словаря:

ids = []
for name in list_of_million_names:
    if name in db_dict:
        ids.append(db_dict[name])

Таким образом, вы используете быструю индексацию по словарю, а не более медленный подход if x in list.

Но единственный способ узнать наверняка — сравнить эти 3 подхода.

person Joost    schedule 15.04.2018
comment
Если таблица Persons содержит миллионы записей, SELECT * FROM persons; раздует память, не так ли? - person raratiru; 15.04.2018
comment
Даже 10 миллионов записей с именами длиной в 10 символов займут всего около половины гигабайта в памяти, так что это не является узким местом. - person Joost; 15.04.2018
comment
Я понимаю вашу точку зрения, но я работаю на бюджетном сервере с 1 ГБ памяти для всех операций! Вероятно, что-то в строках этого ответа может помочь. - person raratiru; 15.04.2018
comment
Это то, что вам нужно делать часто? Потому что если это так, вам, вероятно, следует где-то сохранить вывод. - person Joost; 15.04.2018
comment
Да, это будет делаться часто. Было бы идеально insert или do nothing и сохранить возвращаемые идентификаторы в файл csv. - person raratiru; 15.04.2018

arrow_upward
0
arrow_downward

В этом сообщении описывается, как использовать RETURNING с ON CONFLICT, поэтому при вставке в базу данных содержимого CSV-файла идентификаторы будут сохранены в другой таблице либо в случае успешной вставки, либо в случае, когда из-за уникальных ограничений вставка была пропущена.

Я протестировал его в sqlfiddle, где я использовал настройка, похожая на ту, которая использовалась для COPY, которая вставляет в базу данных прямо из CSV-файла с учетом уникальных ограничений. .

Схема:

CREATE TABLE IF NOT EXISTS label (
  id serial PRIMARY KEY,
  label_name varchar(200) NOT NULL UNIQUE
  );
INSERT INTO label (label_name) VALUES
  ('Name-1'),
  ('Name-2');

CREATE TABLE IF NOT EXISTS ids (
  id serial PRIMARY KEY,
  label_ids varchar(12) NOT NULL
  );

Сценарий:

CREATE TEMP TABLE tmp_table
(LIKE label INCLUDING DEFAULTS)
ON COMMIT DROP;

INSERT INTO tmp_table (label_name) VALUES
  ('Name-2'),
  ('Name-3');

WITH ins AS(
  INSERT INTO label 
  SELECT *
  FROM tmp_table
  ON CONFLICT (label_name) DO NOTHING
  RETURNING id
)
INSERT INTO ids (label_ids)
SELECT
  id FROM ins
UNION ALL
SELECT
  l.id FROM tmp_table
JOIN label l USING(label_name);

Выход:

SELECT * FROM ids;
SELECT * FROM label;
person raratiru    schedule 16.04.2018