Как использовать SQLAlchemy для дампа файла SQL от выражений запроса для вставления объем в DBMS?
Терпите меня, поскольку я объясняю проблему, как я пытался решить ее, и мой вопрос о том, как улучшиться, это в конце.
У меня есть 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
Это было быстрее, чем нерезьбовое читало/сохраняло, но это - путь медленнее, чем загрузка объема файл в базу данных. Задание закончило сохраняться приблизительно после 45 минут. Для забавы я решил записать это в SQL-операторах, потребовалось 5 минут.
Запись SQL-операторов взяла меня несколько часов, все же. Таким образом, мой вопрос, я, возможно, использовал более быстрый метод для вставки строк с помощью SQLAlchemy? Насколько я понимаю SQLAlchemy не разработан для объемных операций вставки, таким образом, это - меньше, чем идеал.
Это следует к моему вопросу, есть ли способ генерировать SQL-операторы с помощью SQLAlchemy, бросить их в файл и затем просто использовать объемную загрузку в базу данных? Я знаю об ул. (model_object), но она не показывает интерполированные значения.
Я ценил бы любое руководство тем, как сделать это быстрее.
Спасибо!
3 ответа
Во-первых, если у вас действительно нет машины с 50 ядрами ЦП, использование 50 потоков / процессов выиграет » t помочь производительности - это действительно замедлит работу.
Во-вторых, мне кажется, что если бы вы использовали способ SQLAlchemy для одновременной вставки нескольких значений , это было бы намного быстрее, чем создание объектов ORM и их сохранение по одному.
Обычно нет, нет способа получить запрос с включенными значениями.
Какую базу данных вы используете? Потому что во многих базах данных есть функция массовой загрузки 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)
Я бы рискнул сказать, что время, затраченное в сценарии python, относится к части загрузки каждой записи. Чтобы определить это, можно записать в CSV или отбросить результаты вместо загрузки новых записей. Это позволит определить, где находится узкое место; по крайней мере, с точки зрения поиска и вставки. Если, как я подозреваю, дело действительно в этом, вы можете воспользоваться функцией массового импорта, которая есть в большинстве DBS. Нет никаких причин, и даже есть некоторые аргументы против вставки записи за записью в подобных обстоятельствах.
Массовый импорт имеет тенденцию делать некоторые интересные оптимизации, такие как выполнение его как одной транзакции без фиксаций для каждой записи (даже просто делая это, можно увидеть заметное снижение времени выполнения); когда это возможно, я рекомендую массовую вставку для большого количества записей. Вы все еще можете использовать подход производитель/потребитель, но пусть потребитель хранит значения в памяти или в файле, а затем вызывает оператор массового импорта, специфичный для используемой вами БД. Такой путь может подойти, если вам нужно выполнить обработку каждой записи в CSV-файле. В этом случае я бы также рассмотрел вопрос о том, какую часть этой обработки можно кэшировать и распределить между записями.
также возможно, что узким местом является использование SQLAlchemy. Не то чтобы я знал о каких-либо присущих ему проблемах, но, учитывая то, что вы делаете, он может требовать гораздо больше обработки, чем необходимо - об этом свидетельствует 8-кратная разница во времени выполнения.
Для развлечения, поскольку вы уже знаете SQL, попробуйте использовать для этого прямой модуль DBAPI в Python и сравните время выполнения.