Flask: Simple site to export tables on DB to CSV

user_13361360 0
Viewed 764

I have a couple of tables in a .db file. I’d like to create a simple site with Flask that allows users to select a table out of the database and then export it as a .csv file. All the example I find online are too complex, or are focused on creating User/Login schemas.

I've added this to my routes.py file.

import sqlite3 as sql
import os
import csv
from sqlite3 import Error

conn = sql.connect("LocalEconData.db")

tables = []


def sql_fetch(con):
    cursorObj = con.cursor()
    cursorObj.execute('SELECT name from sqlite_master where type= "table"')
    rows = cursorObj.fetchall()
    tables.append(rows)
    return tables


sql_fetch(conn)


@app.route("/tables", methods=["GET"])
def dropdown():
    return render_template("tables.html", tables=tables)

On my tables.html file I have written

{% extends "layout.html" %} {% block content %}
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Dropdown</title>
  </head>
  <body>
    <select name="table" method="GET" action="/">
      <option value="{{tables[0]}}" selected>{{tables[0]}}</option>
      {% for table in tables[1:] %}
      <option value="{{table}}">{{table}}</option>
      {% endfor %}
    </select>
  </body>
</html>
{% endblock content %}

When I run my app, all I get is this so far.

Tables.html

I would need a Dropdown with TIME_SERIES_CODES and UNEMPLOYMENT_RATE as options, then create a button to save/export as CSV.

Any suggestions?

flask python
1 Answers

I ended up using Dash instead to solve this problem. It was much easier like this


import dash
from dash.dependencies import Input, Output
import dash_core_components as dcc, dash_html_components as html, pandas as pd, urllib, io, flask, pandas as pd, sqlite3
from glob import glob
from os.path import expanduser

conn = sqlite3.connect("LocalEconData.db")
df = pd.read_sql_query('SELECT name from sqlite_master where type= "table"', conn)


def sql_fetch(con):
    cursorObj = con.cursor()
    cursorObj.execute('SELECT name from sqlite_master where type= "table"')
    rows = cursorObj.fetchall()
    tables = [row[0] for row in rows]
    return tables


table = sql_fetch(conn)


def generate_table(dataframe, max_rows=10):
    return html.Table(
        [html.Tr([html.Th(col) for col in dataframe.columns])]
        + [
            html.Tr([html.Td(dataframe.iloc[i][col]) for col in dataframe.columns])
            for i in range(min(len(dataframe), max_rows))
        ]
    )


external_stylesheets = [
    "https://codepen.io/chriddyp/pen/bWLwgP.css",
    {
        "href": "https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css",
        "rel": "stylesheet",
        "integrity": "sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO",
        "crossorigin": "anonymous",
    },
]
app = dash.Dash(__name__, external_stylesheets=external_stylesheets)
app.layout = html.Div(
    [
        html.Label("Filter"),
        dcc.Dropdown(
            id="field-dropdown",
            options=[{"label": i, "value": i} for i in df["name"]],
            value="UNEMPLOYMENT_RATE",
        ),
        html.Div(id="table"),
        html.A(
            html.Button("Export to CSV"),
            id="download-link",
            download="rawdata.csv",
            href="",
            target="_blank",
        ),
    ]
)
value = "UNEMPLOYMENT_RATE"


def filter_data(value):
    conn = sqlite3.connect(glob(expanduser("LocalEconData.db"))[0])
    cursor = conn.cursor()
    df = pd.read_sql(f"SELECT * FROM {value}", conn)
    return df


@app.callback(Output("table", "children"), [Input("field-dropdown", "value")])
def update_table(filter_value):
    dff = filter_data(filter_value)
    return generate_table(dff)


@app.callback(Output("download-link", "href"), [Input("field-dropdown", "value")])
def update_download_link(filter_value):
    dff = filter_data(filter_value)
    csv_string = dff.to_csv(index=False, encoding="utf-8")
    csv_string = "data:text/csv;charset=utf-8," + urllib.parse.quote(csv_string)
    return csv_string


PORT = 9000
ADDRESS = "127.0.0.1"
if __name__ == "__main__":
    app.run_server(
        debug=True,
        port=PORT,
        host=ADDRESS
        )
user_133613600
Related