Как оптимизировать формирование на сервере большого xlsx файла при помощи python?

Question

[SkiBY]

Есть система Django+MySQL, из которой нужно регулярно выгружать список заказов.
Выгружаются они с зависимостями. select_related идет по 15 полям.
В выборке примерно 300 000 строк за год выходит.
Ситуация в следующем: база выгружает все достаточно быстро, одним запросом, места много не занимает, все ок.
А вот когда начинается формирование csv-файла (библиотека csv ) или xlsx ( xlsxwriter ), система конкретно вешается. На облаке уже от 15000, на локали 150000 - иначе система убивает процесс. Использование constant_memory=True помогает - система долго и нудно работает и все же отдает результат. Но это на локали. Сервер не терпит такой долгий запрос.
xlsx-файл для 150000 получается размером примерно в 32 Мб, но перебор "сжирает" всю память в системе начисто.
Все делается стандартным способом - for o in orders ну и тд, потому что собрать нужно данные, а затем уже отдается сформированный файл.
Дополнительных запросов к базе во время цикла нет - все из памяти.

Код цикла

for o in orders:

        sheet.write(i, 0, o.id)
        sheet.write(i, 1, u'%s' % o.get_o_type_display())
        sheet.write(i, 2, u'%s' % o.o_number)
        if o.o_type == '1':
            name = u'%s, Цвет: %s' % (o.canvas.f_name(), o.color.code)
            sheet.write(i, 3, name)
            name_2 = u'%s, Цвет: %s' % (o.r_canvas.f_name(), o.r_color.code)
            sheet.write(i, 4, name_2)
        else:
            sheet.write(i, 3, u'%s, %s' % (o.part.name, o.part.get_units_display()))
            sheet.write(i, 4, u'%s, %s' % (o.part.name, o.part.get_units_display()))
            

        sheet.write(i, 5, o.rest.id)
        sheet.write(i, 6, u'%s' % o.dealer)
        sheet.write(i, 7, u'%s' % o.dealer.city.name)
        sheet.write(i, 8, o.dealer.id)
        sheet.write(i, 9, u'%s' % o.warehouse)
        sheet.write(i, 10, o.amount, format1)
        sheet.write(i, 11, o.perimetr, format1) if o.perimetr else sheet.write(i, 11, u'-')
        sheet.write(i, 12, o.fix_price, format1)
        sheet.write(i, 13, o.full_price, format1)
        sheet.write(i, 14, o.cost, format1)
        sheet.write(i, 15, u'%s' % o.calc_increase)
        sheet.write(i, 16, o.mat_cost, format1)
        sheet.write(i, 17, u'%s' % o.owner)
        sheet.write(i, 18, o.entry_date.strftime("%Y-%m-%d"))
        sheet.write(i, 19, o.accept_date.strftime("%Y-%m-%d"))
        sheet.write(i, 20, o.in_prod_date.strftime("%Y-%m-%d")) if o.in_prod_date else sheet.write(i, 20, u'-')
        sheet.write(i, 21, o.shipping_date.strftime("%Y-%m-%d")) if o.shipping_date else sheet.write(i, 21, u'-')
        sheet.write(i, 22, o.defect)
        sheet.write(i, 23, o.defect_return)
        sheet.write(i, 24, u'%s' % o.manager_memo)
        sheet.write(i, 25, u'%s' % o.dealer_memo)
        sheet.write(i, 26, u'%s' % o.warehouse_memo)
        sheet.write(i, 27, u'%s' % o.final_memo)
        sheet.write(i, 28, u'%s' % o.final_d_memo)        

        if o.o_type == '1':
            sheet.write(i, 29, u'%s' % o.canvas.id)
            sheet.write(i, 30, u'%s' % o.canvas.c_width)
            sheet.write(i, 31, u'%s' % o.color.code)
        else:
            sheet.write(i, 29, u'%s' % o.part.id)
        sheet.write(i, 32, u'%s' % o.dealer.region.name)
        sheet.write(i, 33, u'%s' % o.get_o_status_display())
        
        
        i += 1

Каким образом оптимизировать этот процесс? Как-то это все же немного - 300000, а уже беда.

UPD
Проблему памяти оказалось решить проще всего. Помог chunked_queryset с djangosnippets - там смысл в разбитии qs и использовании yield. Но скорость обработки, к сожалению, осталась той же - слишком долго (

Comments

[sim3x]

Готовить файл по крону, а не во время хттп запроса?

[SkiBY]

sim3x, рассматривал такой вариант. И все же нет ли способа оптимизации "на лету"? Просто миллионы записей - это одно. А тут как-то же вообще немного. SQL отдает всю пачку очень быстро и компактно, а вот уже python начинает перебирать и умирает

[javedimka]

Я почти уверен, что для Джанго есть модуль который может qs вернуть как csv, я бы сначала его поискал, прежде чем писать такие простыни в коде.

[SkiBY]

javedimka, конечно, есть. Только мне не нужно qs возвращать. Мне нужно файл с конкретными данными и форматами этих данных. Если бы можно было бы брать одним запросом, я бы экспортировал напрямую из базы средствами sql.

[sim3x]

SkiBY,
Как вариант, вместо записи поштучно - сделайте лист и потом одномоментно его пишите
Если не поможет, то нучжно смотреть профайлером, что больше всего времени занимает

[Anton Kuzmichev]

Не знаю, что за chunked_queryset вы имеете ввиду, но в Django у кверисета есть метод iterator(), который работает как генератор и не требует загрузки всех данных из СУБД в скрипт, чтобы начать их обрабатывать.

[SkiBY]

Anton Kuzmichev, там просто yield подсмотрел. Спасибо за метод, прошел мимо меня.
Но формирование списка все же долгое (

[Anton Kuzmichev]

SkiBY, а вы профайлили? Какие операции запнимают больше всего времени в процессе формирования файла?

[SkiBY]

Anton Kuzmichev, пробовал. Просто долго работает. Нет там какой-то отдельной функции, которая все делает хуже. конкретно составление списка. Ребята, делавшие xlsxwriter вон даже у себя дают статистику неутешительную 12800 строк за 26 секунд на "среднем" компе(xlsxwriter.readthedocs.io/working_with_memory.html)

[Anton Kuzmichev]

SkiBY, попробуйте писать csv сырыми строками, а не либой.

Answer 1

[Tash1moto]

Я когда то подобную вещь решил через потоки.
разбиваете список orders примерно на 20 частей,
на каждую часть делаете отдельный поток который пишет в свой файл.
по завершению всех потоков сливаем файлы в один финальный

Answer 2

[almirus]

csv же обычный текстовый файл, пробуйте писать напрямую. как вариант разбить на несколько CVS а потом файлово объединить в один

Comments

[SkiBY]

Когда доходит до записи - все пишется. Вопрос в том, что данные готовятся очень долго и какого-то мусора в памяти остается немеряно.
Под катом код цикла, что я делал для подготовки csv-файла:

код

for o in orders:

            rows.append([o.id,
                u'%s' % o.get_o_type_display(),
                u'%s' % o.o_number,
                u'%s, Цвет: %s' % (o.canvas.f_name(), o.color.code) if o.o_type=='1' else u'%s, %s' % (o.part.name, o.part.get_units_display()),
                u'%s, Цвет: %s' % (o.r_canvas.f_name(), o.r_color.code) if o.o_type=='1' else u'%s, %s' % (o.part.name, o.part.get_units_display()),
                o.rest.id,
                u'%s' % o.dealer,
                u'%s' % o.dealer.city.name,
                o.dealer.id,
                u'%s' % o.warehouse,
                o.amount,
                o.perimetr if o.perimetr else u'-',
                o.fix_price,
                o.full_price,
                o.cost,
                u'%s' % o.calc_increase,
                o.mat_cost, 
                u'%s' % o.owner,
                o.entry_date.strftime("%Y-%m-%d"),
                o.accept_date.strftime("%Y-%m-%d"),
                o.in_prod_date.strftime("%Y-%m-%d"),
                o.shipping_date.strftime("%Y-%m-%d"),
                o.defect,
                o.defect_return,
                u'%s' % o.manager_memo,
                u'%s' % o.dealer_memo,
                u'%s' % o.warehouse_memo,
                u'%s' % o.final_memo,
                u'%s' % o.final_d_memo,
                u'%s' % (o.canvas.c_width) if o.o_type=='1' else u'-',
                u'%s' % (o.color.code) if o.o_type=='1' else u'-',
                ])

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