Feature: Switch from CSV to sqlite3

[astrochun]

We currently generate individual tables. This could in fact be improved with using a SQL library. We could perhaps use SQLModel to support pydantic and pandas.

• Add SQLModel models for clone, traffic and referring
• Develop pydantic models for each data view/stats
• Add db module
  • configure function for setting up SQLAlchemy engine
  • query function to be able to query SQLite by repository_name and date
  • migrate_csv that will work with merged and individual run CSV files (difference is with skip_rows settings).
• Data Migration:
  • Script that can be executed within the Docker container to pull in merged data and parse that over to sqlite3.db
• Will need to load the sqlite3 database at some point when the gts_run_all_repos script is executed
• Be able to transform SQL data to pandas DataFrame to construct figures/charts
• Update documentation
• Test migration with a separate repo: astrochun/sqltest-github-stats

[astrochun]

The referrer tables do not have a timestamp. As such, we need to merge them and include the date the code was run (via the filename). I'm looking for my case whether there are duplicates with a simple merge. In most cases, the records are same with duplicate entries except for the following:

2021-03-17,Evolution-of-Galaxies,github.com,6,1 (first entry gave 4,1)
2021-03-17,MZEvolve,github.com,2,1 (first entry gave 1,1)
2021-03-19,MMTtools,github.com,2,1 (first entry gave 1,1)
2021-03-19,Zcalbase_gal,github.com,9,2 (first entry gave 8,2)

[astrochun]

One limitation right now with this feature is that with each run, there are overlap with SQLite db. We need to address this by ensure that the new numbers are used.

There was an ask for this in pandas: pandas-dev/pandas#14553; however, this was never adopted/implemented. I think we would need to do SQLModel updates directly here.