python fastapi: I want to get a csv file in return

Question

I have create a fastapi end point. I pass some params and i want to get a csv file. How can i do that. The following is the code.

@app.get("/")
async def root(token: str, dbhost: str, port: int, dbname: str ,username: str, passwd: str,table: str):
    con = psycopg2.connect(dbname=dbname, user=username, password=passwd, host=dbhost, port=port)
    cur = con.cursor()
    save = "{}.csv".format(table)
    store = sql.SQL("""COPY {table} TO STDOUT WITH CSV HEADER""").format(table=sql.Identifier(table),)
    ### --> HOW TO write the store into the save <-------
    con.commit()
    cur.close()
    con.close()
    return csv file ### <- how to return csv file

Maybe this helps: stackoverflow.com/questions/22776849/… ? — Luuk
– Luuk, Commented Aug 21, 2021 at 11:19
You'll have to use a FileResponse. See: fastapi.tiangolo.com/advanced/custom-response If you are asking how to retrieve data from a database and return a .csv file, then you'll have to store it temporarily on the server, return it and then delete it — lsabi
– lsabi, Commented Aug 21, 2021 at 12:46
Why to temporarily store it. Cant i send the csv format data retrieved as a csv file — Santhosh
– Santhosh, Commented Aug 21, 2021 at 17:42

Amin Taghikhani · Accepted Answer · 2021-08-21 17:48:19Z

3

if you have your csv as a binary you can use StreamingResponse like this:

from fastapi.responses import StreamingResponse

...

    export_media_type = 'text/csv'
    export_headers = {
          "Content-Disposition": "attachment; filename={file_name}.csv".format(file_name=file_name)
    }
    return StreamingResponse(csv_file_binary, headers=export_headers, media_type=export_media_type)

if you have your csv as a path you can use FileResponse like this:

from fastapi.responses import FileResponse

...

    return FileResponse(csv_file_path)

edited Aug 21, 2021 at 17:48

answered Aug 21, 2021 at 17:38

Amin Taghikhani

7622 gold badges9 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Santhosh Over a year ago

My goal is to send the csv format text which comes from sql query as a file to the user.

Amin Taghikhani Over a year ago

you want send as a text not a file?

Santhosh Over a year ago

As a file. Not as text. I was thinking text can be sent as a file. Do we have to first store as file.

Santhosh Over a year ago

csv_file . Is this the output from the sql.SQL("""COPY {table} TO STDOUT WITH CSV HEADER""").format(table=sql.Identifier(table),)

Amin Taghikhani Over a year ago

you can use first option without saving a file, StreamingResponse work with binary, FileResponse work with files and file must be saved first, you can use io.BytesIO for store file in memory not in disk

Tomasz Zielański · Accepted Answer · 2022-07-22 10:29:31Z

0

To return text as a file (e.g. csv):

...
text: str = ...
return StreamingResponse(
    iter([text]),
    media_type='text/csv',
    headers={"Content-Disposition":
             "attachment;filename=<file_name>.csv"})

Bonus for pandas DataFrame:

import pandas as pd

...
df = pd.DataFrame(...)
    
output = df.to_csv(index=False)
return StreamingResponse(
    iter([output]),
    media_type='text/csv',
    headers={"Content-Disposition":
             "attachment;filename=<file_name>.csv"})

answered Jul 22, 2022 at 10:29

Tomasz Zielański

4733 silver badges13 bronze badges

Collectives™ on Stack Overflow

python fastapi: I want to get a csv file in return

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related