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: https://stackoverflow.com/questions/22776849/how-to-save-results-of-postgresql-to-csv-excel-file-using-psycopg2 ? — Luuk, Aug 21 '21 at 11:19
or this: https://stackoverflow.com/questions/41884254/how-to-save-csv-file-from-query-in-psycopg2 — Luuk, Aug 21 '21 at 11:21
You'll have to use a `FileResponse`. See: https://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, Aug 21 '21 at 12:46
Why to temporarily store it. Cant i send the csv format data retrieved as a csv file — Santhosh, Aug 21 '21 at 17:42

Amin Taghikhani · Answer 1 · 2021-08-21T17:48:19.030

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 '21 at 17:48

answered Aug 21 '21 at 17:38

Amin Taghikhani

684
1
8
22

My goal is to send the csv format text which comes from sql query as a file to the user. – Santhosh Aug 21 '21 at 17:40
you want send as a `text` not a `file`? – Amin Taghikhani Aug 21 '21 at 17:41
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 Aug 21 '21 at 17:43
`csv_file` . Is this the output from the `sql.SQL("""COPY {table} TO STDOUT WITH CSV HEADER""").format(table=sql.Identifier(table),)` – Santhosh Aug 21 '21 at 17:45
1

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` – Amin Taghikhani Aug 21 '21 at 17:46

score 0 · Answer 2 · answered Jul 22 '22 at 10:29

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"})

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

2 Answers2