R and PostgreSQL: Inserting Data
8 November 2018
Today, I needed to generate a fake data set, and I wanted to give R a shot for generating the set. The good news is that everything is pretty simple. You just need to know how to do everything in the correct order. The following code will do exactly this task.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Import libraries. You don't really need tidyverse, I just like working with tibbles. | |
library(RPostgreSQL) | |
library(tidyverse) | |
record_count = 1000 | |
min_date = "2015-01-01" | |
max_date = "2018-12-31" | |
actions = c("Button click", "Fetch data", "Page load", "Refresh") | |
users = c("Allen", "Brian", "Charlie", "Dave", "Evan") | |
# Create the SQL statement. | |
sql_statement = paste("INSERT INTO \"actions\"", | |
"(\"action\", \"user\", \"timestamp\")", | |
"VALUES", | |
"($1, $2, $3)") | |
print(paste("SQL Statement:", sql_statement)) | |
# Connect to the database. | |
driver = dbDriver("PostgreSQL") | |
connection = dbConnect(driver, | |
host = "XXX.XXX.XXX.XXX", | |
port = 5432, | |
user = "XXXXXXXX", | |
password = "XXXXXXXXXXXXXXXXXXXXXX" | |
dbname = "XXXXXXXXXXXXX") | |
dates = seq(from = as.POSIXct(as.Date(min_date)), | |
to = as.POSIXct(as.Date(max_date)), | |
by = "min") | |
data = tibble(action = sample(actions, record_count, repalce = TRUE), | |
user = sample(users, record_count, replace = TRUE), | |
timestamp = sample(dates, record_count, replace = TRUE)) | |
for (index in seq_len(record_count)) { | |
row = data[index,] | |
dbExecute(connection, sql_statement, row) | |
} | |
print(paste("Inserted", record_count, "rows.")) | |
# Clean up after yourself and disconnect from the database. | |
dbDisconnect(connection) |
I hope this helps. Happy coding!