Project3 - Storing web scrapped data in Database

Load Library

#library(RODBC)
library(RMySQL)

## Warning: package 'RMySQL' was built under R version 3.4.4

## Loading required package: DBI

## Warning: package 'DBI' was built under R version 3.4.4

Required SQL scripts

#use following script to create table
# CREATE TABLE `skill_data` (
#   `source` varchar(25) DEFAULT NULL,
#   `base_skill` varchar(45) DEFAULT NULL,
#   `skill_term` varchar(45) DEFAULT NULL,
#   `city` varchar(45) DEFAULT NULL,
#   `radius` int(11) DEFAULT NULL,
#   `indeed_url` varchar(100) DEFAULT NULL,
#   `jobs_count` int(11) DEFAULT NULL
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create Database Connection

#cnString <- "SERVER=db4free.net;DATABASE=data607_skill;UID=cuny_user_607;PASSWORD=cuny*2018;OPTION=3;" # live
#db <- odbcConnect(cnString, case="nochange")

db <- dbConnect(MySQL(), user='root', password='root', dbname='skilldb', host='localhost')

Get Database information

dbListTables(db)

## [1] "skill_data"

dbListFields(db,"skill_data")

## [1] "source"     "base_skill" "skill_term" "city"       "radius"    
## [6] "indeed_url" "jobs_count"

Load data from CVS file to Database

df_skilldata = read.csv("Indeed_Job_Search_Results.csv", header = TRUE)
dbWriteTable(db, "skill_data", df_skilldata, append=TRUE, row.names=FALSE)

## [1] TRUE

Check no of rows loaded into table

rs<-dbSendQuery(db,"select * from skill_data")
df_msql_data = fetch(rs, n=-1)
dbDisconnect(db)

## Warning: Closing open result sets

## [1] TRUE

nrow(df_msql_data)

## [1] 300