Как использовать SQLAlchemy для выгрузки файла SQL из выражений запроса в массивную вставку в СУБД?

Пожалуйста, несите меня, когда я объясняю проблему, как я пытался ее решить, и мой вопрос о том, как его улучшить, в конце.

У меня есть 100 000 строк csv файла из автономного пакетного задания, и мне нужно было вставьте его в базу данных в качестве своих подходящих моделей. Обычно, если это довольно простая загрузка, это может быть тривиально загружено путем простого перебора CSV файла в соответствии с схемой; но мне пришлось сделать некоторую внешнюю обработку, требующую запросов, и гораздо удобнее использовать SQLAlchemy для генерации требуемых данных.

Данные, которые я хочу здесь, представляют собой 3 модели, которые представляют 3 таблицы, предшествующие началу в базе данных, и каждая последующая модель зависит от предыдущей модели. Например:

Model C --> Foreign Key --> Model B --> Foreign Key --> Model A

Итак, модели должны быть вставлены в порядке A, B и C. Я подошел с подходом производителя/потребителя:

 - instantiate a multiprocessing.Process which contains a
 threadpool of 50 persister threads that have a threadlocal 
 connection to a database

 - read a line from the file using the csv DictReader

 - enqueue the dictionary to the process, where each thread creates
 the appropriate models by querying the right values and each
 thread persists the models in the appropriate order

Это было быстрее, чем не-threaded read/persist, но это медленнее, чем массовая загрузка файла в базу данных. Завершение работы закончилось после примерно 45 минут. Для удовольствия я решил написать его в SQL операторам потребовалось 5 минут.

Написание инструкций SQL заняло у меня пару часов. Так что мой вопрос в том, мог ли я использовать более быстрый метод для вставки строк, используя SQLAlchemy? Насколько я понимаю, SQLAlchemy не предназначен для массовых вставки, поэтому это меньше, чем идеально.

Это следует моему вопросу, есть ли способ генерировать SQL-выражения, используя SQLAlchemy, throw их в файл, а затем просто использовать массовую загрузку в базу данных? я знаете о str (model_object), но он не показывает интерполированный значения.

Буду признателен за то, как это сделать быстрее.

Спасибо!

Ответ 1

Во-первых, если у вас на самом деле нет машины с 50 ядрами процессора, использование 50 потоков/процессов не поможет производительности - это фактически замедлит работу.

Во-вторых, я чувствую, что если вы использовали метод SQLAlchemy вставлять сразу несколько значений, это было бы намного быстрее, чем создание ORM и сохраняя их один за другим.

Ответ 2

Обычно нет, нет способа получить запрос с включенными значениями.

Какая база данных вы используете? Потому что у многих баз данных есть функция массовой загрузки для CSV.

Postgres: http://www.postgresql.org/docs/8.4/static/sql-copy.html
MySQL: http://dev.mysql.com/doc/refman/5.1/en/load-data.html
Oracle: http://www.orafaq.com/wiki/SQL * Loader_FAQ

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

'''Replace the parameter placeholders with values'''
params = compiler.params.items()
params.sort(key=lambda (k, v): len(str(k)), reverse=True)
for k, v in params:
    '''Some types don't need escaping'''
    if isinstance(v, (int, long, float, bool)):
        v = unicode(v)
    else:
        v = "'%s'" % v

    '''Replace the placeholders with values
    Works both with :1 and %(foo)s type placeholders'''
    query = query.replace(':%s' % k, v)
    query = query.replace('%%(%s)s' % k, v)

Ответ 3

Я бы рискнул сказать, что время, потраченное на python script, находится в части загрузки каждой записи. Чтобы определить это, вы можете написать в CSV или отменить результаты, а не загружать новые записи. Это определит, где узкое место; по крайней мере, с точки зрения lookup-vs-insert. Если, как я подозреваю, это действительно так, вы можете воспользоваться функцией массового импорта, которой обладает большинство DBS. Нет никаких оснований и даже некоторых аргументов против вставки записей в запись в таких обстоятельствах.

Массовый импорт имеет тенденцию делать некоторую оптимизацию интереса, например, делать это, поскольку одна транзакция без коммитов для каждой записи (даже если это делает заметное падение времени выполнения); когда это возможно, я рекомендую объемную вставку для больших учетных записей. Вы все равно можете использовать подход производителя/потребителя, но вместо этого потребитель должен хранить значения в памяти или в файле, а затем вызывать оператор массового импорта, специфичный для используемого вами БД. Это может быть маршрут, если вам нужно выполнить обработку для каждой записи в файле CSV. Если это так, я бы также рассмотрел, сколько из них можно кэшировать и делиться между записями.

также возможно, что узкое место использует SQLAlchemy. Не то, чтобы я знал о каких-либо неотъемлемых проблемах, но, учитывая то, что вы делаете, может потребоваться намного больше обработки, чем это необходимо - о чем свидетельствует разница в 8 раз в времени выполнения.

Для удовольствия, поскольку вы уже знаете SQL, попробуйте использовать прямой модуль DBAPI в Python для этого и сравните время выполнения.