csv.py

import psycopg2
import psycopg2.extras
from flask_csv import send_csv

@app.route("/fake", methods=["GET"])
def fake():
    desde = "01/01/2020"
    hasta = "25/05/2020"
    tipo = "whatsapp"
    SLACK_WORKSPACE_URL = app.config.get("SLACK_WORKSPACE_URL")
    query = 'SELECT to_char("Response".responsetimestamp, \'dd/mm/yyyy\') AS conversation_date, "FBSlackDB".fbuserid, CASE WHEN "Response".username = \'U0148BCP7AQ\' THEN \'Operador: Oscar Machado\' WHEN "Response".username = \'U010XLUANKX\' THEN \'Operador: Mercy Orellana\' WHEN "Response".username = \'U0148BCM0NQ\' THEN \'Operador: Yancy Melgar\' WHEN "Response".username = \'UMYPH50UU\' THEN \'Operador: Melvin Ernesto Hernández\' WHEN "Response".username != \'VirtualAgent\' AND "Response".username != \'Virtual Agent\' THEN concat(\'Operador: \', "Response".username) WHEN("Response".username = \'VirtualAgent\' OR "Response".username = \'Virtual Agent\') AND "Response".message LIKE \'%%nuestros agentes retomará%%\' THEN \'Notificada y pendiente\' ELSE \'Ageñte Virtúal\' END AS conversation_status, concat(%(SLACK_MESSAGES_URL)s, "FBSlackDB".channelid) AS channel_link FROM "FBSlackDB" JOIN "Response" ON ("Response".channelid = "FBSlackDB".channelid) JOIN (SELECT max("Response".responsetimestamp) as date, max("Response".responseid) maxid, "FBSlackDB".fbuserid fuid, to_char("Response".responsetimestamp, \'dd/mm/yyyy\') fecha FROM "Response", "FBSlackDB" WHERE "Response".channelid = "FBSlackDB".channelid AND "FBSlackDB".type = %(TIPO)s AND "Response".responsetimestamp between to_date(%(DESDE)s, \'dd/mm/yyyy\') and to_date(%(HASTA)s, \'dd/mm/yyyy\') + 1 GROUP BY "FBSlackDB".fbuserid, to_char("Response".responsetimestamp, \'dd/mm/yyyy\')) AS B ON (B.maxid = "Response".responseid) AND "Response".responsetimestamp between to_date(%(DESDE)s, \'dd/mm/yyyy\') AND to_date(%(HASTA)s, \'dd/mm/yyyy\') + 1 ORDER BY conversation_date DESC'

    with psycopg2.connect(app.config.get("CONNECTION_STRING")) as conn:
        with conn.cursor() as cur:
            cur = conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor)
            cur.execute(query, {"SLACK_MESSAGES_URL": SLACK_WORKSPACE_URL, "TIPO": tipo, "DESDE": desde, "HASTA": hasta})
            rows = cur.fetchall()
            colnames = [desc[0] for desc in cur.description]

            if rows:
                return send_csv(
                    rows,
                    "test.csv",
                    colnames
                )