How to import SQL information to a data frame, from postgreSQL

PostgreSQL use in R RPostgrade package Load DBI package: It is requiered

library("DBI", lib.loc="~/R/win-library/3.4")
library("RPostgreSQL", lib.loc="~/R/win-library/3.4")
library(sqldf)
## Loading required package: gsubfn
## Loading required package: proto
## Loading required package: RSQLite
## sqldf will default to using PostgreSQL
con = dbConnect(PostgreSQL(), user="postgres", password="Doradu8030",
                 host="localhost", port=5432, dbname="mysql")

I used two different methods to populate my tables in SQL

First: I loaded a .csv file from postgreSQL using the instruction “COPY” script name : Assig2.sql

Second: I used the INSERT instruction script name :Assigwinsert.sql

Being more eficient the first method especially for large files already populated. However, I found useful and faster the second method if a conditional and repetitive routine like IF or FOR will be added to populate tables more efficiently (automate process).

For booth, the first and second method, I created two .sql scripts to show this.

Getting data from my first table

topmovies = dbGetQuery(con,"select * from movie_info ")
names(topmovies) = c("Movie_Title", "Date_Release"," Director")
topmovies
##            Movie_Title Date_Release             Director
## 1 Jumanji                1995-12-15 Joe Johnstone       
## 2 ThorRagnarok           2011-05-06 Kenneth Branagh     
## 3 SpiderMan              2002-05-03 Sam Raimi           
## 4 WonderWoman            2017-06-02 Patty Jenkins       
## 5 Fast & Furious8        2017-04-04 F Gary Gray         
## 6 Cinderella             2015-03-13 Kenneth Branagh     
## 7 KongSkullIsland        2017-03-10 Jordan Vogt-Roberts 
## 8 TheLegoBatman          2017-02-10 Chris Mckay

Getting data from my second table

movierate = dbGetQuery(con,"select * from movie_rate ")
names(movierate) = c("Movie_Title", "Scale 1-5")
movierate
##            Movie_Title Scale 1-5
## 1 Jumanji                      5
## 2 ThorRagnarok                 3
## 3 SpiderMan                    4
## 4 WonderWoman                  5
## 5 Fast & Furious8              5
## 6 Cinderella                   3
## 7 KongSkullIsland              2
## 8 TheLegoBatman                4

I created a third table using INSERT INTO SELECT, it requires that data types in source and target tables match to show aditional information.

result = dbGetQuery(con,"select * from movie_res ")
names(result) = c("Movie_Title", "Scale 1-5","Date_Release", "Director")
result
##                      Movie_Title Scale 1-5 Date_Release
## 1 Jumanji                                5   1995-12-15
## 2 ThorRagnarok                           3   2011-05-06
## 3 SpiderMan                              4   2002-05-03
## 4 WonderWoman                            5   2017-06-02
## 5 Fast & Furious8                        5   2017-04-04
## 6 Cinderella                             3   2015-03-13
## 7 KongSkullIsland                        2   2017-03-10
## 8 TheLegoBatman                          4   2017-02-10
##                    Director
## 1 Joe Johnstone            
## 2 Kenneth Branagh          
## 3 Sam Raimi                
## 4 Patty Jenkins            
## 5 F Gary Gray              
## 6 Kenneth Branagh          
## 7 Jordan Vogt-Roberts      
## 8 Chris Mckay
X<-dbDisconnect(con)
X
## [1] TRUE