How do I correctly export my Postgres table in Flask?

user_16531348 0
Viewed 68

I've created a Heroku web app as a personal project, but keep running into errors, as I'm new to Flask and SQL databases.

I've created a page that displays the database that users input on the website (operates like an admin view). My plan was to have a button/link that exports whatever information is currently in the database's table as CSV.

Here is the HTML which holds the "download CSV" link - the page is 'download_csv.html':

<body>
    <h3>Download Power Rankings CSV Report</h3>
    <p>
        <a href={{ url_for('download_report') }}>Generate CSV Report</a>
    </p>
</body>

The route I have created within app.py in regards to both the page with the link, and the link itself (in that order):

@app.route('/download_csv')
def download():
    return render_template('download_csv.html')

The above route works and I am able to navigate to the page with the link.

@app.route('/download_csv/')
def download_report():
    conn = None
    cursor = None
    try:
        curr = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        curr.execute("SELECT id, username, league, team1, team2, team3, team4, team5, team6, team7, team8, team9, team10 FROM voting")
        result = cursor.fetchall()
        output = io.StringIO()
        writer = csv.writer(output)
        line = ['ID, League, Username, Team1, Team2, Team3, Team4, Team5, Team6, Team7, Team8, Team9, Team10']
        writer.writerow(line)

        for row in result:
            line = [str(row['id']) + ',' + row['username'] + ',' + row['league'] + ',' + row['team1'] + ',' + row['team2'] + ',' + row['team3'] + ',' + row['team4'] + ',' + row['team5'] + ',' + row['team6'] + ',' + row['team7'] + ',' + row['team8'] + ',' + row['team9'] + ',' + row['team10']]
            writer.writerow(line)

        output.seek(0)

        return Response(output, mimetype="text/csv", headers=("Content-Disposition:attachment;filename=power_ranking"))

    except Exception as e:
        print(e)
    finally:
        cursor.close()
        conn.close()

When I click the link, I am receiving a Server 500 error, but am not sure why. I'm sure it has something to do with the first line of the last route; but not being familiar enough with Flask, I can't be certain. I do not know where to point that route; which is why it is 'download_csv/'

Thank you in advance.

flask postgresql python
1 Answers

I changed the code to this, and it worked for what I was looking to achieve:

@app.route('/download_csv/report/excel')
def download_report():
    curr = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    curr.execute("SELECT * FROM voting")
    result = curr.fetchall()
    #for row in result:
        #print(row)

        #output in bytes
    output = io.BytesIO()
        #create WorkBook object
    workbook = xlwt.Workbook()
        #add sheet
    sh = workbook.add_sheet('Power Rankings Report')

        #add headers
    sh.write(0, 0, 'ID')
    sh.write(0, 1, 'Username')
    sh.write(0, 2, 'League')
    sh.write(0, 3, 'Team 1')
    sh.write(0, 4, 'Team 2')
    sh.write(0, 5, 'Team 3')
    sh.write(0, 6, 'Team 4')
    sh.write(0, 7, 'Team 5')
    sh.write(0, 8, 'Team 6')
    sh.write(0, 9, 'Team 7')
    sh.write(0, 10, 'Team 8')
    sh.write(0, 11, 'Team 9')
    sh.write(0, 12, 'Team 10')

    id = 0
    for row in result:
        sh.write(id+1,0, str(row['id']))
        sh.write(id+1,1, row['username'])
        sh.write(id+1,2, row['league'])
        sh.write(id+1,3, row['team1'])
        sh.write(id+1,4, row['team2'])
        sh.write(id+1,5, row['team3'])
        sh.write(id+1,6, row['team4'])
        sh.write(id+1,7, row['team5'])
        sh.write(id+1,8, row['team6'])
        sh.write(id+1,9, row['team7'])
        sh.write(id+1,10, row['team8'])
        sh.write(id+1,11, row['team9'])
        sh.write(id+1,12, row['team10'])
        id += 1

    workbook.save(output)
    output.seek(0)

    return Response(output, mimetype="application/ms-excel", headers={"Content-Disposition":"attachment;filename=power_rankings.xls"})
user_165313480
Related