-
Notifications
You must be signed in to change notification settings - Fork 7
/
postgres.R
54 lines (38 loc) · 1.5 KB
/
postgres.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
require(RPostgreSQL) #https://cran.r-project.org/web/packages/RPostgreSQL/RPostgreSQL.pdf
require(ggplot2)
drv <- dbDriver("PostgreSQL")
# connect to the database
con <- dbConnect(drv, dbname = "musa620",
host = "localhost", port = 5432,
user = "postgres", password = '')
# SQL command to create a new table
sqlCommand <- paste0("CREATE TABLE cartable (carname character varying NOT NULL,",
"mpg numeric(3,1),",
"cyl numeric(1,0),",
"disp numeric(4,1),",
"hp numeric(3,0),",
"drat numeric(3,2),",
"wt numeric(4,3),",
"qsec numeric(4,2),",
"vs numeric(1,0),",
"am numeric(1,0),",
"gear numeric(1,0),",
"carb numeric(1,0),",
"CONSTRAINT cartable_pkey PRIMARY KEY (carname)) WITH (OIDS=FALSE);")
# run the SQL command to create the table
dbGetQuery(con, sqlCommand)
# create some test data to load into the table
data(mtcars)
carData <- data.frame(carname = rownames(mtcars),
mtcars,
row.names = NULL)
carData$carname <- as.character(carData$carname)
# import the data into the table "cartable" in the "musa620" database
dbWriteTable(con, "cartable", value = carData, append = TRUE, row.names = FALSE)
# run a SQL query for all of the data in cartable
queryResult <- dbGetQuery(con, "SELECT * from cartable")
head(queryResult)
# plot the results
ggplot(queryResult, aes(x = hp, y = mpg)) +
geom_point(aes(size = wt, colour = as.factor(cyl), alpha=0.5)) +
theme_bw()