BlueDevilPride BlueDevilPride - 8 months ago 28
R Question

id = nil, when sending data from R to postgreSQL

Let's say I have a model users, with attributes: name:string, email:string. If I create a user online or in the console and save that user, the user is given an id. However, if I send (via R) user data into my posgresql database, the users will have attributes but no user id. What do I need to do to ensure users will have an id, when I'm importing data to the model?

Rails Console User Id Check

2.2.1 :002 >
User Load (12.6ms) SELECT "Users".* FROM "Users" LIMIT 1
=> nil

R import code


pw <- {

con <- dbConnect(drv, dbname = "my_database_production",
host = "localhost", port = 5432,
user = "user_name", password = pw)

dbExistsTable(con, "users")

dbWriteTable(con, "users",
value = new_data, overwrite=TRUE)



Making sure I've got this right: you want to add users, and have the function return the new user ids so you've got some way of referring to them, presumably later in the function.

I don't know whether there's a built-in function to do this in Rails already, but if the R app needs to know the user ids of new users, you can either do this as:

1) a two-step: insert, then select id from Users where [blah]. Using dbWriteTable() doesn't quite do this: the existing users table gets over-written when you write the new table in. I'm assumingid is a serial type or is from a server-side sequence, i.e. assigned by the database.

2) Use an insert query with the RETURNING clause. Caveat: I'm not a Rails expert, so there might be other table-magic we need to do.

users <- data.frame(stringsAsFactors=FALSE,
  name=c("Alice", "Bob", "Claire"),
  email=c("", "", "")

qry <- "INSERT INTO Users (name, email) VALUES "

for(ii in 1:nrow(users)){
  # use dbQuoteString in case of names like O'Toole, etc.
  # also protects against SQL injection - google 'Bobby Tables' 
  # if that term is mysterious.
  nm <- dbQuoteString(ANSI(), users[ii,"name"])
  em <- dbQuoteString(ANSI(), users[ii,"email"])

  qry <- paste(qry, "(", nm, ",", em, ")")

  if(ii != nrow(users)) {
    qry <- paste0(qry, ",")

# in this example, we return all the columns we've inserted,
# plus the user id.
qry <- paste(qry, "RETURNING name, email, id")

res <- dbGetQuery(con, qry)