WebAppChallenge

pre-requisites:

• Git: https://git-scm.com/downloads
• Anaconda: https://www.anaconda.com/products/individual
• DB Browser for SQLite does NOT need to be installed in the machine.

Setting up the repository

In order to set up the repository please first clone the repo into a directory of choice. In order to do so you first need to have Git installed into your machine. Once you have Git installed then follow these steps using the Anaconda Prompt.

• Clone the repository into a directory z: by navigating to that directory using the Anaconda Prompt and running the following command:

git clone <location_of_bare_repository/WebAppChallenge>

• Once the repo is cloned its time to create a conda environment with the appropriate libraries installed as specified in environment.yml. Navigate to z/WebAppChallenge and create an empty directory called envs by running:

mkdir envs

Then make this your conda environment by running on your Anaconda Prompt shell:

conda env create -p envs -f environment.yml

This might take a couple of minutes as it will install the packages necessary, specified in the environment.yml file, for this repository to run. Once these

conda activate z/WebAppChallenge/envs

Once the conda environment is activated, you should be able to see the path of the environment in front of the current directory path in the conda prompt as shown below.

(z/WebAppChallenge/envs) Idlirs-Laptop:WebAppChallenge idlirshkurti$ 

In order to run the application you must run flask_app.py by running:

python flask_app.py 

App details

Once the app is run, a new tab on your browser should open at port localhost:5000. The front-end consists of the full table given as input in the bottom, a search bar on the left where you can search for a specific ID and the corresponding SQL query on the right hand side. Note: This app was heavily influenced by this blogpost

The data is stored in a database in the root directory as example.db. This database consists of 2 tables: - logs - task_data

The task_data table contains the data from the task_data.csv file in the input folder and the logs table contains the logs of the queries that have been conducted in this app. The queries are stored as strings in a SQL query type format with the corresponding datetime of the query.

This app could just as easily have a search bar where the user could input exactly the SQL query they need, or maybe instead of one search bar for ID it could have 4 search bars for each column of the data. This could be easily implemented with the current code structure but for simplicity sake I went with one search bar for ID only just to indicate how this is possible.

Repository structure

The repository structure should look like the following diagram.

The flask_app.py is the main python script which runs the entire app. This calls the sqlquery.py script in the functions directory which contains the necessary functions needed to create, read and render the example.db database. flask_app.py then after doing so renders the table and results in the front end which is designed using the sqldatabase.html script in the templates directory.

The environment.yml file contains the libraries necessary for this project to run. These libraries, as explained previously are installed in the conda environment.

WebAppChallenge
│   README.md 
│	flask_app.py
│	environment.yml
│
└───functions
│   │   __init__.py
│   │   sqlquery.py
│   
└───templates
│   │   sqldatabase.html
│   
└───input
    │   task_data.csv