DATA 607 Homework 2

Create an SQL db with R

connection = dbConnect(SQLite(), dbname="Movie_ratings.sqlite")

dbSendQuery(connection, 'CREATE TABLE IF NOT EXISTS Ratings
(
NAME TEXT,
ID INTEGER,
Matrix INTEGER,
Fargo INTEGER,
EternalSunshine INTEGER,
DrStrangeLove INTEGER,
LostInSpace INTEGER,
BackdoorClutsNine INTEGER)'
)

## <SQLiteResult>
##   SQL  CREATE TABLE IF NOT EXISTS Ratings
## (
## NAME TEXT,
## ID INTEGER,
## Matrix INTEGER,
## Fargo INTEGER,
## EternalSunshine INTEGER,
## DrStrangeLove INTEGER,
## LostInSpace INTEGER,
## BackdoorClutsNine INTEGER)
##   ROWS Fetched: 0 [complete]
##        Changed: 0

dbSendQuery(connection,'INSERT INTO Ratings
VALUES ("Michaelangelo",1,1,2,3,4,5,1)')

## Warning: Closing open result set, pending rows

## <SQLiteResult>
##   SQL  INSERT INTO Ratings
## VALUES ("Michaelangelo",1,1,2,3,4,5,1)
##   ROWS Fetched: 0 [complete]
##        Changed: 1

dbSendQuery(connection,'INSERT INTO Ratings
VALUES ("Leonardo",2,5,2,4,3,1,1)')

## Warning: Closing open result set, pending rows

## <SQLiteResult>
##   SQL  INSERT INTO Ratings
## VALUES ("Leonardo",2,5,2,4,3,1,1)
##   ROWS Fetched: 0 [complete]
##        Changed: 1

dbSendQuery(connection,'INSERT INTO Ratings
VALUES ("Donatello",3,2,1,3,5,4,1)')

## Warning: Closing open result set, pending rows

## <SQLiteResult>
##   SQL  INSERT INTO Ratings
## VALUES ("Donatello",3,2,1,3,5,4,1)
##   ROWS Fetched: 0 [complete]
##        Changed: 1

dbSendQuery(connection,'INSERT INTO Ratings
VALUES ("Rapheal",4,5,5,5,5,5,3)')

## Warning: Closing open result set, pending rows

## <SQLiteResult>
##   SQL  INSERT INTO Ratings
## VALUES ("Rapheal",4,5,5,5,5,5,3)
##   ROWS Fetched: 0 [complete]
##        Changed: 1

dbSendQuery(connection,'INSERT INTO Ratings
VALUES ("THE_SHREDDER",5,1,1,1,1,1,5)')

## Warning: Closing open result set, pending rows

## <SQLiteResult>
##   SQL  INSERT INTO Ratings
## VALUES ("THE_SHREDDER",5,1,1,1,1,1,5)
##   ROWS Fetched: 0 [complete]
##        Changed: 1

TheObjectAndyWants = dbGetQuery(connection,'SELECT * FROM Ratings')

## Warning: Closing open result set, pending rows

TheObjectAndyWants = data.frame(TheObjectAndyWants)
TheObjectAndyWants

##            NAME ID Matrix Fargo EternalSunshine DrStrangeLove LostInSpace
## 1 Michaelangelo  1      1     2               3             4           5
## 2      Leonardo  2      5     2               4             3           1
## 3     Donatello  3      2     1               3             5           4
## 4       Rapheal  4      5     5               5             5           5
## 5  THE_SHREDDER  5      1     1               1             1           1
##   BackdoorClutsNine
## 1                 1
## 2                 1
## 3                 1
## 4                 3
## 5                 5