如何将.sql文件的内容读取到R脚本中以运行查询？ [英] How to read the contents of an .sql file into an R script to run a query?

问题描述

我已经尝试过 readLines 和 read.csv 函数，但是没有用。

I have tried the readLines and the read.csv functions but then don't work.

这是 my_script.sql 文件的内容：

SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE HireDate >= '1-july-1993'

，并将其保存在我的桌面上。

and it is saved on my Desktop.

现在我想要从我的R脚本运行此查询。这是我的东西：

Now I want to run this query from my R script. Here is what I have:

conn = connectDb()

fileName <- "C:\\Users\\me\\Desktop\\my_script.sql"
query <- readChar(fileName, file.info(fileName)$size)

query <- gsub("\r", " ", query)
query <- gsub("\n", " ", query)
query <- gsub("", " ", query)

recordSet <- dbSendQuery(conn, query)
rate <- fetch(recordSet, n = -1)

print(rate)
disconnectDb(conn)

在这种情况下，我什么也收不到。我该怎么办？

And I am not getting anything back in this case. What can I try?

推荐答案

我自己在读取sql文件时遇到了麻烦，并且经常发现语法如果sql中有任何单行注释，则将损坏。由于在R中，您将sql语句存储为单行字符串，因此，如果sql中有任何双破折号，它将基本上注释掉双破折号之后的所有代码。

I've had trouble with reading sql files myself, and have found that often times the syntax gets broken if there are any single line comments in the sql. Since in R you store the sql statement as a single line string, if there are any double dashes in the sql it will essentially comment out any code after the double dash.

getSQL <- function(filepath){
  con = file(filepath, "r")
  sql.string <- ""

  while (TRUE){
    line <- readLines(con, n = 1)

    if ( length(line) == 0 ){
      break
    }

    line <- gsub("\\t", " ", line)

    if(grepl("--",line) == TRUE){
      line <- paste(sub("--","/*",line),"*/")
    }

    sql.string <- paste(sql.string, line)
  }

  close(con)
  return(sql.string)
}

这篇关于如何将.sql文件的内容读取到R脚本中以运行查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！