Data Science Thought Leadership

# Update read linkedIn ID data, 18 March
DataLink <- read_csv("link182.csv")
location <- NA
linkSkill <- NA
linkInterests <- NA
for ( i in  1: length(DataLink$fullData)){
  dataLink <- htmlParse(DataLink$fullData[i] )
  dataLink <- read_html(DataLink$fullData[i])
  location[i] <- html_nodes(dataLink, "h3.pv-top-card-section__location") %>% 
    html_text() %>% str_trim() %>% unlist() 
  linkSkill[i] <- html_nodes(dataLink,"span.pv-skill-category-entity__name-text")  %>%  
    html_text()  %>% str_trim() %>% unlist() %>% paste(collapse = " | ")
  linkInterests[i] <- html_nodes(dataLink,"span.pv-entity__summary-title-text")  %>%  
    html_text() %>% str_trim() %>% unlist() %>% paste(collapse = " | ")
}
linkedinData <- tibble(DataLink$Name,DataLink$`web-scraper-start-url`,DataLink$Location,DataLink$Intro,linkSkill,linkInterests)

CheckDB <- function() {
  
# Open Connection 
  con2 <- dbConnect(RMySQL::MySQL(), dbname = "", host="localhost", user="root",password= "")
  
# Drop Table if Exists 
if(dbExistsTable(con2, "dataurla")){
    dbRemoveTable(con2,"dataurla")
  }

# Create the first table for dataurla

query <- "CREATE TABLE `dataurla` (  
`profile_id`  INT AUTO_INCREMENT,
`Rank` INT NOT NULL AUTO_INCREMENT,
`Twitter Handle` VARCHAR(45) NULL,
`Name` VARCHAR(45) NULL, 
`TL Score` VARCHAR(45) NULL, 
`profileURL` VARCHAR(45) NULL,
`profileIMGURL` VARCHAR(45) NULL,
`areofExpert` VARCHAR(45) NULL,
`AreasofExpertise` VARCHAR(45) NULL,
`company` VARCHAR(45) NULL,
`intro` VARCHAR(45) NULL,
`location` VARCHAR(45) NULL,
`tFollower` VARCHAR(45) NULL,
`IndustryExperience` VARCHAR(45) NULL,
`Publications` VARCHAR(45) NULL,
`Opportunities` VARCHAR(45) NULL,
`socialURL` VARCHAR(45) NULL,
`tURL` VARCHAR(45) NULL,
`linkURL` VARCHAR(45) NULL,
`thandle` VARCHAR(45) NULL,
 PRIMARY KEY (`Rank`))"
tab1 <- dbSendQuery(con2,query)
tab1data <- dbFetch(tab1)

# Close Connection
dbDisconnect(con2)
}

# Update dataurla table 

query <- sprintf("insert into dataurla (Twitter Handle, Name, TL Score, profileURL, profileIMGURL, areofExpert, AreasofExpertise, company, intro, location, tFollower, IndustryExperience, Publications, Opportunities, socialURL, tURL, linkURL, thandle) values('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",twitterhandle, name, tlscore, profileurl, profileimgurl, areofexpert, areasofexpertise, company, intro, location, tFollower, industryexperience, publications, opportunities, socialurl, turl, linkurl, thandle)
  print (query)
tab1 <- dbSendQuery(con2,query)
tab1data <- dbFetch(tab1)

# Get 'Rank' to update dataurla table
query <- sprintf("select  Rank from dataurla  where Twitter Handle='%s', Name='%s', TL Score='%s', profileURL='%s', profileIMGURL='%s', areofExpert='%s', AreasofExpertise='%s', company='%s', intro='%s', location='%s', tFollower='%s', IndustryExperience='%s', Publications='%s', Opportunities='%s', socialURL='%s', tURL='%s', linkURL='%s', thandle='%s' limit 1",twitterhandle, name, tlscore, profileurl, profileimgurl, areofexpert, areasofexpertise, company, intro, location, tFollower, industryexperience, publications, opportunities, socialurl, turl, linkurl, thandle)
  
tab1 <- dbSendQuery(con2,query)
tab1data <- dbFetch(tab1)
data_Rank <- tab1data$Rank

# Close Connection
dbDisconnect(con2)
}

# Saving data to local directory and upload onto MONGO
write.csv(linkedinData,"linkedin.csv")
mongoFile$upload("linkedin.csv")

# Connect to MongoDB Atlas
mongoFile <- gridfs(db = "PROJECT TITLE", url = "mongodb+srv://USER URL", prefix = "fs",options = ssl_options())

mongoFile <- gridfs(db = "MSProject3", url = "mongodb+srv://msds_user:msds@cluster0-bqyhe.gcp.mongodb.net/", prefix = "fs",options = ssl_options())



# List All File from MongoDB Atlas
allfiles <- mongoFile$find()
allfiles

# Download a file by ID or Name
mongoFile$download("tweet.csv")
mongoFile$download("dataurlA.csv")
mongoFile$download("linkedin.csv")