SQL Reporting Tool
What is it?
This is a reporting tool for a news website's database. It takes in information from 3 tables -
articles - and returns the top 3 most viewed articles, authors, and any dates where the error rate was greater than 1%.
There is a utility
query() procedure to prevent unnecessary repetition of code in connecting to/querying the database.
There are three procedures that each use the
query procedure to return some data, and then I used Python3 to turn that data into something human-readable.
top_articles() returns the top 3 articles, along with views.
top_authors() returns the top 3 authors, along with views.
percent_errors() returns any dates for which the error rate exceeded 1%, along with the actual error rate as a percentage.
print_report() runs these three procedures, printing the full report.
How to run
Download this data set. Unzip, and move the
news.sql file into the
vagrant directory within the virtual machine setup you downloaded as part of step one.
3. Run the virtual machine
In your terminal application,
CD into the same
$ vagrant up to start the virtual machine, then
$ vagrant ssh to connect to it via SSH.
4. Load the data into the news database
Run the following to load the data:
$ psql -d news -f newsdata.sql
-d news connects to the news database that is already set up.
-f newsdata.sqlruns the
newsdata.sql file, setting up the tables and importing all of the data.
5. Run the report
Get out of the
psql prompt -
ctrl - z in my case.
$ python3 news-db-report.py on the virtual machine, and the report will be printed out.
To see what the output looks like in the console, check out the included