如何将 .sql 文件的内容读入 R 脚本以运行查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44853322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read the contents of an .sql file into an R script to run a query?
提问by user1367204
I have tried the readLinesand the read.csvfunctions but then don't work.
我已经尝试了readLines和read.csv功能，但后来不起作用。
Here is the contents of the my_script.sqlfile:
这是my_script.sql文件的内容：
SELECT EmployeeID, FirstName, LastName, HireDate, City FROM Employees
WHERE HireDate >= '1-july-1993'
and it is saved on my Desktop.
它保存在我的桌面上。
Now I want to run this query from my R script. Here is what I have:
现在我想从我的 R 脚本运行这个查询。这是我所拥有的：
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?
在这种情况下，我没有得到任何回报。我可以尝试什么？
回答by Matt Jewett
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.
我自己阅读 sql 文件时遇到了麻烦，并且发现如果 sql 中有任何单行注释，语法经常会被破坏。由于在 R 中您将 sql 语句存储为单行字符串，如果 sql 中有任何双破折号，它基本上会注释掉双破折号之后的任何代码。
This is a function that I typically use whenever I am reading in a .sql file to be used in R.
这是我通常在读取要在 R 中使用的 .sql 文件时使用的函数。
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)
}
回答by Tim Abraham
I've found for queries with multiple lines, the read_file()function from the readr package works well. The only thing you have to be mindful of is to avoid single quotes (double quotes are fine). You can even add comments this way.
我发现对于多行查询，read_file()readr 包中的函数运行良好。您唯一需要注意的是避免使用单引号（双引号很好）。您甚至可以通过这种方式添加评论。
Example query, saved as query.sql
示例查询，另存为 query.sql
SELECT
COUNT(1) as "my_count"
-- comment goes here
FROM -- tabs work too
my_table
I can then store the results in a data frame with
然后我可以将结果存储在数据框中
df <- dbGetQuery(con, statement = read_file('query.sql'))
回答by TheNewGuy
You can use the read_file()function from the readrpackage.
您可以使用包中的read_file()函数readr。
fileName = read_file("C:/Users/me/Desktop/my_script.sql")
You will get a string variable fileNamewith the desired text.
您将获得fileName带有所需文本的字符串变量。
Note:Use /instead of \\\
注意：使用/代替\\\
回答by Rookatu
The answer by Matt Jewettis quite useful, but I wanted to add that I sometimes encounter the following warning when trying to read .sql files generated by sql server using that answer:
Matt Jewett的答案非常有用，但我想补充一点，在尝试使用该答案读取 sql server 生成的 .sql 文件时，有时会遇到以下警告：
Warning message: In readLines(con, n = 1) : line 1 appears to contain an embedded nul
警告消息：在 readLines(con, n = 1) 中：第 1 行似乎包含嵌入的 nul
The first line returned by readLinesis often "?t" in these cases (i.e. the UTF-16 byte order mark) and subsequent lines are not read properly. I solved this by opening the sql file in Microsoft SQL Server Management Studioand selecting
在这些情况下，by 返回的第一行readLines通常是“ ?t”（即 UTF-16 字节顺序标记），随后的行无法正确读取。我通过在Microsoft SQL Server Management Studio 中打开 sql 文件并选择
File -> Save As ...
文件 -> 另存为...
then on the small downarrow next to the save button selecting
然后在保存按钮旁边的小向下箭头上选择
Save with Encoding ...
使用编码保存...
and choosing
并选择
Unicode (UTF-8 without signature) - Codepage 65001
Unicode（无签名的 UTF-8） - 代码页 65001
from the Encoding dropdown menu.
从编码下拉菜单。
If you do not have Microsoft SQL Server Management Studioand are using a Windows machine, you could also try opening the file with the default text editor and then selecting
如果您没有Microsoft SQL Server Management Studio并且使用的是 Windows 计算机，您还可以尝试使用默认文本编辑器打开文件，然后选择
File -> Save As ...
文件 -> 另存为...
Encoding: UTF-8
编码：UTF-8
to save with a .txt file extension.
以 .txt 文件扩展名保存。
Interestingly changing the file within Microsoft SQL Server Management Studioremoves the BOM (byte order mark) altogether, whereas changing the file within the text editor converts the BOM to the UTF-8 BOM but nevertheless causes the query to be properly read using the referenced answer.
有趣的是，在Microsoft SQL Server Management Studio中更改文件会完全删除 BOM（字节顺序标记），而在文本编辑器中更改文件会将 BOM 转换为 UTF-8 BOM，但仍会导致使用参考答案正确读取查询.
