avisionh

# set global .gitconfig file details

# to edit global .gitconfig
#git config --edit --global

# to see where global .gitconfig file is enter following commands on BASH
#cd~
#pwd

[user]

avisionh

USE [<database_name>]

/* Deleting Tables by Schema */
-- Usage:		This script generates dynamic SQL that writes a set of SQL queries that delete tables in a specified database
--				  by the schema those tables belong to.


DECLARE @query NVARCHAR(MAX)
SELECT @query = 
    COALESCE(@query, N'') + N'DROP TABLE [<schema_name>].' + QUOTENAME(TABLE_NAME) + N';' + CHAR(13)

avisionh

USE [<schema_name>]
GO

/* Joining via intermediary join */
-- DESC:	Purpose of this script is to find a way to join [REGBODY1] and [REGBODY2]
--			 columns from [usr].[Y17_18_Student_Course] onto [usr].[Y17_18_Student_Core].

-- Q.	IS THERE A COMMON UNIQUE IDENTIFIER COLUMN BETWEEN THE TWO TABLES?
-- A.	Yes, [UKPRN], [COURSEID], [COURSEAIM]
SELECT 

avisionh

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);

SET @cols = 
(
	SELECT STRING_AGG([TABLE_NAME],',') 
	FROM (
		SELECT DISTINCT [TABLE_NAME] 
		FROM [INFORMATION_SCHEMA].[COLUMNS]
		WHERE [TABLE_NAME] LIKE '%Y08_09_Student%'
	) AS t

avisionh

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);

SET @cols = 
(
	SELECT STRING_AGG([TABLE_NAME],',') 
	FROM (
		SELECT DISTINCT [TABLE_NAME] 
		FROM [INFORMATION_SCHEMA].[COLUMNS]
		WHERE [TABLE_NAME] LIKE '%Y08_09_Student%'
	) AS t

avisionh

# ----------- #
# SQL Connect #
# ----------- #
# DESCRIPTION: This script is a short tutorial in connecting R to a SQL server.
#              It uses the 'DBI' package as opposed to the 'RODBC' packages due to the
#              security that the DBI package can bring in preventing SQL injection attacks.

# NOTE:        SQL injection attacks are destructive actions that can be taken to your database
#              that are brought from providing users the ability to query from SQL via a different
#              software/programme/platform. Typically, it can involve things like DROP TABLE.

avisionh

# ----------- #
# SQL Connect #
# ----------- #
# DESCRIPTION: This script is a short tutorial in connecting R to the EDAP SQL server.
#              It uses the 'DBI' package as opposed to the 'RODBC' packages due to the
#              security that the DBI package can bring in preventing SQL injection attacks.

# NOTE:        SQL injection attacks are destructive actions that can be taken to your database
#              that are brought from providing users the ability to query from SQL via a different
#              software/programme/platform. Typically, it can involve things like DROP TABLE.

avisionh

# ------------------ #
# dataimport_sqldata #
# ------------------ #

# DESCRIPTION:          imports and wrangles the geography lookups in SQL.
# AUTHOR:               Avision Ho
# ASSUMPTIONS:          none
# SCRIPT DEPENDENCIES:  
#                       1. 'functions.R'
# PACKAGE DEPENDENCIES:

avisionh

import pandas as pd
import requests

# get country lookups
url = "https://unstats.un.org/unsd/methodology/m49/"
html = requests.get(url).content
data_countries = pd.read_html(html)
 # take first list element which is in English
data_countries = data_countries[0]

avisionh

# doesn't work because have 2FA
# https://pygithub.readthedocs.io/en/latest/introduction.html
# https://pygithub.readthedocs.io/en/latest/examples/Repository.html#get-a-specific-content-file
from github import Github

# create Github instance
password = os.getenv("GITHUB_PASSWORD")
g = Github("avisionh", password)

repo = g.get_repo("lukes/ISO-3166-Countries-with-Regional-Codes")