使用 ODBC 包将数据从 SQL Server 保存或读取到 R [英] Save or Read data from SQL Server in to R using ODBC package

问题描述

如果我想将 R 连接到数据库，我还需要为 R 用户安装驱动程序吗?

If I want to connect R to a database, do I still need to install driver for R user?

我已成功连接服务器，但无法读取/写入/获取表.

I had done successful connection with server but unable to read/write/fetch the tables.

library(odbc)
con <- dbConnect(odbc::odbc(), 
         .connection_string = 'driver={SQL Server};server=DW01;database=AF_DW;trusted_connection=true')

现在我可以在 RStudio 连接中看到 AF_DW.

Now I can see AF_DW in RStudio connections.

dbListFields(con, "Visits")

我可以看到表Visits"中的所有变量

I can see all the variables in the table "Visits"

data <- dbReadTable(con, "Visits")

出现错误:nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC SQL Server 驱动程序][SQL Server]无效的对象名称访问".[Microsoft][ODBC SQL Server 驱动程序][SQL Server]无法准备语句.'SELECT * FROM 访问")

Got an Error: nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Visits'. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. 'SELECT * FROM "Visits")

data3 <- dbGetQuery(con, "SELECT * FROM Visits")

遇到同样的错误

data4 <- dbSendQuery(con, "SELECT * FROM Visits")

出现同样的错误

con_in_R <- dbFetch(dbSendQuery(con,"SELECT * FROM Visits"))

错误(函数(cond):在选择函数dbFetch"的方法时评估参数res"时出错:nanodbc/nanodbc.cpp:1655:42000:[Microsoft][ODBC SQL Server Driver][SQL服务器]无效的对象名称访问".[Microsoft][ODBC SQL Server 驱动程序][SQL Server]无法准备语句.SELECT * FROM Visits"

Error in (function (cond) : error in evaluating the argument 'res' in selecting a method for function 'dbFetch': nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Visits'. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. 'SELECT * FROM Visits'

感谢您的帮助.谢谢.

推荐答案

根据您的图片，Visits 并未存储在默认的 dbo 架构中，因为您的所有查询都假定但在 eCW 架构下.

According to you image, Visits is not stored in the default dbo schema as all your queries assume but under the eCW schema.

与大多数 RDBMS 一样，SQL Server 遵循对象(表、存储过程、函数)的三部分命名约定:[database].[schema].[object] 其中数据库不是必需的dbo 默认不需要特定于数据库的连接和架构.

Like most RDBMS's, SQL Server follows the three part name convention for objects (tables, stored procedures, functions): [database].[schema].[object] where database is not necessary for a database specific connection and schema not necessary for dbo default.

因此，您需要在尝试的查询中引用架构和表名.

Therefore, you need to reference schema and table name in your attempted queries.

s <- Id(schema = "eCW", table = "Visits")

# READ FROM NON-DEFAULT SCHEMA TABLE
data3 <- dbReadTable(con, s) 
data3 <- dbGetQuery(con, "SELECT * FROM [eCW].[Visits]")

# WRITE TO NON-DEFAULT SCHEMA TABLE
dbWriteTable(conn, s, mydataframe)
dbWriteTable(con, SQL("eCW.Visits"), mydataframe)

这篇关于使用 ODBC 包将数据从 SQL Server 保存或读取到 R的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！