🔒 hi need help fastapi download excel file | Python | Page 1

astral echo Sep 10, 2024, 7:21 AM

#

hi

round carbonBOT Sep 10, 2024, 7:21 AM

#

astral echo hi

#

@astral echo

Python help channel opened

Remember to:

Ask your Python question, not if you can ask or if there's an expert who can help.
Show a code sample as text (rather than a screenshot) and the error message, if you've got one.
Explain what you expect to happen and what actually happens.

:warning: Do not pip install anything that isn't related to your question, especially if asked to over DMs.

astral echo Sep 10, 2024, 7:22 AM

#

hi i want to dowload the response in for query in excel file

proud quest Sep 10, 2024, 7:22 AM

#

You mean download the File Response like this? https://fastapi.tiangolo.com/advanced/custom-response/#fileresponse

Custom Response - HTML, Stream, File, others - FastAPI

FastAPI framework, high performance, easy to learn, fast to code, ready for production

astral echo Sep 10, 2024, 7:23 AM

#

https://paste.pythondiscord.com/G6YA

#

yes

#

yes i am using stream response i am worried does my code handle large amount of file and data efficently

proud quest Sep 10, 2024, 7:24 AM

#

does it need to be?

astral echo Sep 10, 2024, 7:25 AM

#

i think so like i am curious about the file writing part

proud quest Sep 10, 2024, 7:25 AM

#

how often does the excel file change?

astral echo Sep 10, 2024, 7:26 AM

#

basically it wont changed like i input start time and end time to my api and based on this time frame the respose come from database

now it need to be written in excel

proud quest Sep 10, 2024, 7:26 AM

#

Have you tried File Response? With File Response, you don't need to specify start time and end time.

#

Save you the hassle

astral echo Sep 10, 2024, 7:27 AM

#

       async def excel_generator():
.................................
.................................
            for col_num, header in enumerate(headers, 1):
                column_letter = get_column_letter(col_num)
                max_length = len(header)
                for row in ws.iter_rows(min_row=4, max_row=ws.max_row, min_col=col_num, max_col=col_num):
                    for cell in row:
                        if cell.value:
                            max_length = max(max_length, len(str(cell.value)))
                adjusted_width = max_length + 2  # Add padding for better display
                ws.column_dimensions[column_letter].width = adjusted_width

            buffer = io.BytesIO()
            wb.save(buffer)
            buffer.seek(0)
            yield buffer.read()

        buffer = io.BytesIO()
        async for chunk in excel_generator():
            buffer.write(chunk)
        buffer.seek(0)

        encoded_bytes = base64.b64encode(buffer.getvalue()).decode('utf-8')

        await cache_data(request, cache_key, encoded_bytes)

        return StreamingResponse(
            buffer,
            media_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            headers={
                "Content-Disposition": f"attachment; filename=alerts_report_{start_time_str}_to_{end_time_str}.xlsx",
                "Access-Control-Allow-Origin": "*",
                "Access-Control-Allow-Headers": "*",
                "Access-Control-Allow-Methods": "POST, GET, OPTIONS",
            }
        )```

#

basically i need excel file of specific time range

proud quest Sep 10, 2024, 7:27 AM

#

Streaming Response is useful for videos or some other stream of data that continuously flows

astral echo Sep 10, 2024, 7:28 AM

#

what is good for pdf excel and csv files

proud quest Sep 10, 2024, 7:28 AM

#

astral echo basically i need excel file of specific time range

Then that's different. Nothing to do with streaming

proud quest Sep 10, 2024, 7:28 AM

#

astral echo what is good for pdf excel and csv files

File Response

astral echo Sep 10, 2024, 7:28 AM

#

only need to change StreamingResponse to File Response

proud quest Sep 10, 2024, 7:28 AM

#

hopefully

astral echo Sep 10, 2024, 7:29 AM

#

can you check the rest piece of code does it write to file efficently

#

as this api call very frequently i am worry about that

proud quest Sep 10, 2024, 7:30 AM

#

Does it need to be in excel?

#

Can't it be in text file?

astral echo Sep 10, 2024, 7:31 AM

#

no only pdf or excel

#

@proud quest

#

getting this

#

 File "/usr/local/lib/python3.8/dist-packages/starlette/routing.py", line 762, in __call__
    await self.middleware_stack(scope, receive, send)
  File "/usr/local/lib/python3.8/dist-packages/starlette/routing.py", line 782, in app
    await route.handle(scope, receive, send)
  File "/usr/local/lib/python3.8/dist-packages/starlette/routing.py", line 297, in handle
    await self.app(scope, receive, send)
  File "/usr/local/lib/python3.8/dist-packages/starlette/routing.py", line 77, in app
    await wrap_app_handling_exceptions(app, request)(scope, receive, send)
  File "/usr/local/lib/python3.8/dist-packages/starlette/_exception_handler.py", line 64, in wrapped_app
    raise exc
  File "/usr/local/lib/python3.8/dist-packages/starlette/_exception_handler.py", line 53, in wrapped_app
    await app(scope, receive, sender)
  File "/usr/local/lib/python3.8/dist-packages/starlette/routing.py", line 75, in app
    await response(scope, receive, send)
  File "/usr/local/lib/python3.8/dist-packages/starlette/responses.py", line 326, in __call__
    raise RuntimeError(f"File at path {self.path} does not exist.")
RuntimeError: File at path /tmp/tmp4_vp49vg.xlsx does not exist.

#

https://paste.pythondiscord.com/2APA

red rain Sep 10, 2024, 7:48 AM

#

Just use a regular response

#

Don't write to disk

astral echo Sep 10, 2024, 7:49 AM

#

hi bro

#

where to change that

astral echo Sep 10, 2024, 7:50 AM

#

astral echo https://paste.pythondiscord.com/2APA

in this

red rain Sep 10, 2024, 7:50 AM

#

Also you probably want a sync def for your generate_excel and to use @asyncer.asyncify on it

#

(and replace the yield for a return)

astral echo Sep 10, 2024, 7:51 AM

#

@red rain this is old code we are working as request for clickhouse and redis doesnt the asyncify block code

red rain Sep 10, 2024, 7:51 AM

#

?

#

But generate_excel doesn't contain any async code. It's all blocking cpmpute

astral echo Sep 10, 2024, 7:52 AM

#

ok pleasee guide step by step

#

i'll chnge

red rain Sep 10, 2024, 7:52 AM

#

Well first fix the response. Send the bytes using a regular Response

astral echo Sep 10, 2024, 7:52 AM

#

https://paste.pythondiscord.com/IYSQ check this

#

oh ok

#

        return Response(
            content=file_content,
            media_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            headers={
                "Content-Disposition": f"attachment; filename=alerts_report_{start_time_str}_to_{end_time_str}.xlsx",
            }
        )``` like this

red rain Sep 10, 2024, 7:54 AM

#

Yeah I think so

astral echo Sep 10, 2024, 7:54 AM

#

ok next

red rain Sep 10, 2024, 7:55 AM

#

Get rid of yield from generate_excel and don't call it with a for loop

astral echo Sep 10, 2024, 7:57 AM

#

ok wait

red rain Sep 10, 2024, 8:07 AM

#

Waiting

astral echo Sep 10, 2024, 8:40 AM

#

@red rain done

#

https://paste.pythondiscord.com/IFAA

#

@red rain

#

check pls

#

@red rain it worked as expected download file but can you please verify

red rain Sep 10, 2024, 8:50 AM

#

Oh cool yeah looks good

#

I thought Redis supported binary values

astral echo Sep 10, 2024, 8:50 AM

#

        @asyncer.asyncify
        def generate_excel():
            wb = Workbook()
            ws = wb.active
            ws.title = "Alerts Report"
            ws.insert_rows(1)
            ws.merge_cells('A2:K2')
            title_cell = ws['A2']
            title_cell.value = "Centralized Network Alerts"
            title_cell.font = Font(size=25, bold=True)
            title_cell.alignment = Alignment(horizontal='center', vertical='center')
            headers = [
                "Timestamp",
                "Organization",
                "Department",
                "Source IP",
                "Destination IP",
                "Source Port",
                "Destination Port",
                "Protocol",
                "Category",
                "Signature",
                "Sensor"
            ]```

#

please check this function

#

is there not any code blocking or cpu operation that block compute

#

then i call this file_content = await generate_excel()

red rain Sep 10, 2024, 8:54 AM

#

astral echo is there not any code blocking or cpu operation that block compute

You run it in a thread using asyncify so you don't need to worry

astral echo Sep 10, 2024, 8:55 AM

#

we need to use await right ?

#

        @asyncer.asyncify
        def generate_excel():
            wb = Workbook()
            ws = wb.active
            ws.title = "Alerts Report"
            ws.insert_rows(1)
            ws.merge_cells('A2:K2')
            title_cell = ws['A2']
            title_cell.value = "Centralized Network Alerts"
            title_cell.font = Font(size=25, bold=True)
            title_cell.alignment = Alignment(horizontal='center', vertical='center')
            headers = [
                "Timestamp",
                "Organization"
            ]
            for col_num, header in enumerate(headers, 1):
                cell = ws.cell(row=4, column=col_num)
                cell.value = header
                cell.font = Font(bold=True)
                cell.alignment = Alignment(horizontal='center')
            for i, row in enumerate(rows, start=5):
                for col_num, value in enumerate(row, 1):
                    cell = ws.cell(row=i, column=col_num)
                    cell.value = value
                    if col_num in [1, 9, 10, 11]:
                        cell.font = Font(bold=True)
            for col_num, header in enumerate(headers, 1):
                column_letter = get_column_letter(col_num)
                max_length = len(header)
                for row in ws.iter_rows(min_row=4, max_row=ws.max_row, min_col=col_num, max_col=col_num):
                    for cell in row:
                        if cell.value:
                            max_length = max(max_length, len(str(cell.value)))
                adjusted_width = max_length + 2
                ws.column_dimensions[column_letter].width = adjusted_width
            buffer = io.BytesIO()
            wb.save(buffer)
            buffer.seek(0)
            return buffer.getvalue()

# is it correct way to call this function
        file_content = await generate_excel()```

#

please check comment

#

because of @asyncer.asyncify right.

red rain Sep 10, 2024, 9:02 AM

#

Yeah

astral echo Sep 10, 2024, 9:03 AM

#

rest the api look ok ?

round carbonBOT Sep 10, 2024, 10:03 AM

#

Python help channel closed

This help channel has been closed and it's no longer possible to send messages here. If your question wasn't answered, feel free to create a new post in #1035199133436354600. To maximize your chances of getting a response, check out this guide on asking good questions.

#🔒 hi need help fastapi download excel file