Python script into a command-line tool

Overview

Using the sqlite-utils command-line tool we can directly run the sql commands from cmd. It can be used to manipulate SQLite databases in a number of different ways. I will again be doing CRUD operations but directly from terminal.

User Guide

This is the default subcommand, so the following two examples work the same way:

sqlite-utils query dogs.db "select * from dogs"

sqlite-utils dogs.db "select * from dogs"

UPDATE, INSERT and DELETE

If you execute an UPDATE, INSERT or DELETE query the command will return the number of affected rows:

sqlite-utils dogs.db "update dogs set age = 5 where name = 'Cleo'"

[{"rows_affected": 1}]

If your data is in CSV format, you can insert it using the --csv option:

sqlite-utils insert dogs.db dogs dogs.csv --csv

Here is the documentation for more command line tools for reference sqlite-utils

Code Description

This repo has been created by forked from (https://github.com/nogibjj/sqlite-lab). I have used world university ranking csv file and loaded it into 'ranking.db' database under the table name 'universities'.

1. create.py This script is used for load and transform. A databased called 'ranking.db' with a table named 'universities' is created and a csv file is loaded into that table.

2. read.py This script is used to interact with the SQL database. The queries used are :
   • sqlite-utils query ranking.db 'SELECT "Name of University" FROM universities WHERE "Location" == "United States"'
   • sqlite-utils query ranking.db 'SELECT "Name of University", "No of student per staff" FROM universities WHERE "No of student per staff" > 40.0'
   • sqlite-utils query ranking.db 'SELECT "Name of University", "No of student per staff" FROM universities WHERE ("No of student per staff" < 40.0) AND ("Location" == "Canada")'

3. update.py Updating of tuple values already present in the table.

   • sqlite-utils query ranking.db "update universities set Location = 'australia' where Location = 'Australia'"

4. delete.py Deletion of data present in the table. The query used is : sqlite-utils query ranking.db 'DELETE FROM universities WHERE "Industry Income Score" < 90.0'

6. test_graph.py ** pd.read_sql_query ** is used for creating visualisation. It is a function used to read SQL query or database table into DataFrame.

7. Makefile with the following:

   • install: using requirements.txt file to install required packages

   • test:

   python -m pytest -vv --cov=main *.py

- format: using black formatter

  - lint: using ruff 

7.Created GitHub Actions that performs all four Makefile commands with badges for each one in the README.md

Action include the general CI/CD process in test.yml file, which automatically generate the graph and markdown

Visualization

Visualization Created using sql database using pandas.read_sql_query (https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html)

Count of top universities vs mean industry income score