Flask Pandas Dataframe

Simple Flask project that loads pandas dataframe into the database and shows the information on a page. Flask Pandas Dataframe is a one-file project that might help beginners to understand some basic Flask concepts:

• Create a simple Flask app
• Download a public pandas DF
• Create an SQLite DB and a table to save the information
• Load pandas in DB using a new custom command
• Visualize the data in the browser

Links

• 👉 Support - Provided by AppSeed
• 👉 More Open-Source Starters - actively supported and versioned

Pandas Dataframe - Console View

Pandas Dataframe - Browser View

Set up

$ # Clone sources
$ git clone https://github.com/app-generator/flask-pandas-dataframe.git
$ cd flask-pandas-dataframe
$
$ # Virtualenv modules installation (Unix based systems)
$ virtualenv env
$ source env/bin/activate
$
$ # Virtualenv modules installation (Windows based systems)
$ # virtualenv env
$ # .\env\Scripts\activate
$
$ # Install dependencies
$ pip3 install -r requirements.txt
$
$ # Create database via Flask CLI
$ flask shell
>>> from app import db  # import SqlAlchemy interface 
>>> db.create_all()     # create SQLite database and Data table 
>>> quit()              # leave the Flask CLI  
$
$ # Load the data into the database
$ flask load-data titanic-min.csv
$
$ # Set the FLASK_APP environment variable
$ (Unix/Mac) export FLASK_APP=run.py
$ (Windows) set FLASK_APP=run.py
$ (Powershell) $env:FLASK_APP = ".\run.py"
$
$ # Set up the DEBUG environment
$ # (Unix/Mac) export FLASK_ENV=development
$ # (Windows) set FLASK_ENV=development
$ # (Powershell) $env:FLASK_ENV = "development"
$ 
$ flask run 
$ # access the app in the browser: http://localhost:5000

Dependencies

• Flask - the framework used
• Pandas - an amazing data analysis library
• SQLAlchemy - Python SQL Toolkit and ORM
• Flask-SqlAlchemy - extension for Flask that adds support for SQLAlchemy
• Requests - simple HTTP library.

$ # Virtualenv modules installation (Unix based systems)
$ virtualenv env
$ source env/bin/activate
$
$ # Virtualenv modules installation (Windows based systems)
$ # virtualenv env
$ # .\env\Scripts\activate
$
$ # Install modules - SQLite Database
$ pip3 install -r requirements.txt

Env

$ # Enable the DEBUG environment
$ # (Unix/Mac) export FLASK_ENV=development
$ # (Windows) set FLASK_ENV=development
$ # (Powershell) $env:FLASK_ENV = "development"
$
$ # Set the FLASK_APP environment variable
$ (Unix/Mac) export FLASK_APP=app.py
$ (Windows) set FLASK_APP=app.py
$ (Powershell) $env:FLASK_APP = ".\app.py"

Download the data

The dataset is downloaded from a remote location and saved locally.

>>> import requests
>>> import pandas as pd
>>> 
>>> # Define the remote CSV file
>>> csv_file = 'https://static.appseed.us/data/titanic.txt'
>>>
>>> # Download the file (via request library)
>>> r = requests.get( csv_file )
>>> 
>>> # Save the content to a new LOCAL file
>>> f = open('titanic.csv', 'w')  
>>> f.write( r.content.decode("utf-8") )
>>> f.close   

Process data

The RAW dataset has ~900 rows and we can inspect it with ease using pandas library

>>> import pandas as pd
>>> 
>>> df = pd.read_csv( 'titanic.csv' )
>>> df
     PassengerId  Survived  Pclass                                               Name     Sex   Age  SibSp  Parch            Ticket     Fare Cabin Embarked
0              1         0       3                            Braund, Mr. Owen Harris    male  22.0      1      0         A/5 21171   7.2500   NaN        S
1              2         1       1  Cumings, Mrs. John Bradley (Florence Briggs Th...  female  38.0      1      0          PC 17599  71.2833   C85        C
2              3         1       3                             Heikkinen, Miss. Laina  female  26.0      0      0  STON/O2. 3101282   7.9250   NaN        S
3              4         1       1       Futrelle, Mrs. Jacques Heath (Lily May Peel)  female  35.0      1      0            113803  53.1000  C123        S
4              5         0       3                           Allen, Mr. William Henry    male  35.0      0      0            373450   8.0500   NaN        S
..           ...       ...     ...                                                ...     ...   ...    ...    ...               ...      ...   ...      ...
886          887         0       2                              Montvila, Rev. Juozas    male  27.0      0      0            211536  13.0000   NaN        S
887          888         1       1                       Graham, Miss. Margaret Edith  female  19.0      0      0            112053  30.0000   B42        S
888          889         0       3           Johnston, Miss. Catherine Helen "Carrie"  female   NaN      1      2        W./C. 6607  23.4500   NaN        S
889          890         1       1                              Behr, Mr. Karl Howell    male  26.0      0      0            111369  30.0000  C148        C
890          891         0       3                                Dooley, Mr. Patrick    male  32.0      0      0            370376   7.7500   NaN        Q

Return columns data types in the DataFrame: df.dtypes. This informationis used to design a table where is information is loaded.

>>> df.dtypes 
PassengerId      int64
Survived         int64
Pclass           int64
Name            object
Sex             object
Age            float64
SibSp            int64
Parch            int64
Ticket          object
Fare           float64
Cabin           object
Embarked        object

Prepare the storage

Integrate SQLAlchemy and define a table to load the data.

# Store the Titanic sad stats
class Data(db.Model):

    passengerId  = db.Column(db.Integer,     primary_key=True )
    name         = db.Column(db.String(250), nullable=False   )
    survived     = db.Column(db.Integer,     nullable=False   )
    sex          = db.Column(db.String(10 ), default=None     ) # name, female
    age          = db.Column(db.Integer,     default=-1       ) 
    fare         = db.Column(db.Float,       default=-1       )

    # The string representation
    def __repr__(self):
        return str(self.passengerId) + ' - ' + str(self.name) 

Create the SQLite database and the new table via Flask CLI:

$ flask shell
App: app [development]
Instance: D:\work\repo-learn\python\how-to\instance
>>> from app import db
>>> db.create_all()

At this point, we can inspect the database using SQLiteBrowser, an open-source and free editor for SQLite (the table is empty).

Load Data

The information will be loaded into the database via a custom command = load-data. The command expects the input file as argument (CSV format).

# New import
import click

...

# Custom command
@app.cli.command("load-data")
@click.argument("fname")
def load_data(fname):
    ''' Load data from a CSV file '''
    print ('*** Load from file: ' + fname)

    # The functional part goes here
    ... 

To check the command is properly coded we can type flask --help in the terminal:

$ flask --help

Options:
  --version  Show the flask version
  --help     Show this message and exit.

Commands:
  load-data  Load data from a CSV file          <-- NEW Command
  routes     Show the routes for the app.
  run        Run a development server.
  shell      Run a shell in the app context.

Links & Resources

• Flask - the framework used
• Pandas - an amazing data analysis library
• AppSeed - for support annd more samples

Flask Pandas Dataframe - Open-source sample provided by AppSeed App Generator.