我是通过R连接到数据库的新手，我正在尝试找到最大程度地减少错误和问题的最佳实践。我将表从R上传到Postgres数据库，我需要将权限设置为我知道的一个名称的某个组。
我正在尝试找出各种DBI功能的不同行为和最佳实践，以免我不小心犯错而弄乱数据库。
我不知道我是否应该使用dbexecute（）或dbsendquery（）。我已经阅读了两个功能的R文档，并了解他们执行SQL命令以修改连接的数据库。我知道dbexecute（）告诉我受影响的行的数量，但是dbsendquery（）似乎也是如此。 dbexecute（）似乎使用dbsendstatement（），但这并不能帮助我理解差异，因为它看起来相似。
我无法解释以下两个示例中看到的行为。他们俩都在做同样的事情吗？他们俩都在工作吗？一种方法比另一种更好或更安全吗？
示例1
res <- dbExecute(con,'set role certain_group')
print(res) # output is: [1] 0
dbClearResult(res) # output is: Error in (function (classes, fdef, mtable) :
# unable to find an inherited method for function ‘dbClearResult’ for signature ‘"integer"’
示例2
res2 <- dbSendQuery(con,'set role certain_group')
print(res2) # output is: <PqResult>
SQL set role certain_group
ROWS Fetched: 0 [complete]
Changed: 0
dbClearResult(res) # no output in console
最终注意：我更喜欢使用rpostgres软件包，而不是其他选项。
I'm new to connecting to databases via R, and I am trying to find best practices to minimize errors and problems. I am uploading a table from R to a postgres database, and I need to set the permissions to a certain group that I know the name of.
I'm trying to figure out the different behaviors and best practices for various DBI functions, so that I don't accidentally make a mistake and mess up the database.
I don't know whether I should use dbExecute() or dbSendQuery(). I've read the R documentation for both functions, and understand that they execute sql commands to modify the connected database. I understand that dbExecute() tells me the number of rows affected, but dbSendQuery() seems to also. dbExecute() seems to use dbSendStatement(), but this does not help me understand the difference because it seems similar.
I can't explain the behavior I see in these two examples below. Are they both doing the same thing? Are they both working? Is one way better or safer than the other?
Example 1
res <- dbExecute(con,'set role certain_group')
print(res) # output is: [1] 0
dbClearResult(res) # output is: Error in (function (classes, fdef, mtable) :
# unable to find an inherited method for function ‘dbClearResult’ for signature ‘"integer"’
Example 2
res2 <- dbSendQuery(con,'set role certain_group')
print(res2) # output is: <PqResult>
SQL set role certain_group
ROWS Fetched: 0 [complete]
Changed: 0
dbClearResult(res) # no output in console
Final note: I prefer to use the RPostgres package as opposed to other options.
发布评论
评论（1）
在SQL中，大多数命令属于两种类型：影响数据的操作查询（即，
insert，update， delete ，drop /code>）或返回数据的结果集（即， select）。在R的DBI中，不同的方法每个文档触发了这两种类型的命令：
dbexecute主要用于操作查询，实际上是dbsendStatement+dbgetRowsed+ + + + + + + +dbclearresult。每个文档：dbgetquery主要用于迁移到数据帧的结果集查询，实际上是dbsendquery+ +的包装器dbfetch+dbclearresult。每个文档：以清晰的结果设置为“
dbexecute”anddbsendquery应运行任何类型的SQL语句，但其返回值不同。取决于包装味（即，odbc，roracle，rmysql，rpostgresql），您可能需要使用dbsendquery运行操作语句，尤其是使用dbbind绑定参数。但是dbexecute永远不会返回数据框架！您的Postgres特定
SET语句是一个特殊的操作查询。因此，只需调用dbexecute即可运行并检索受影响的任何行。另外，调用 dbsendquery +dbgetRowsed+dbclearresult可能达到与dbexececute的相同结果。In SQL, most commands fall under two types: action queries that affect data (i.e.,
INSERT,UPDATE,DELETE,DROP) or resultset queries that return data (i.e.,SELECT).In R's DBI, different methods trigger these two types of commands per documentation:
dbExecuteis mainly used for action queries and is actually a wrapper fordbSendStatement+dbGetRowsAffected+dbClearResult. Per docs:dbGetQueryis mainly used for resultset queries migrating results to a data frame and is actually a wrapper fordbSendQuery+dbFetch+dbClearResult. Per docs:With that said, both
dbExecuteanddbSendQueryshould run any type of SQL statement but their return values differ. Depending on the package flavor (i.e.,odbc,ROracle,RMySQL,RPostgreSQL), you may need to usedbSendQueryto run action statements particularly for binding parameters withdbBind. ButdbExecutewill never return a data frame!Your Postgres-specific
SETstatement is a special action query. Therefore, simply calldbExecuteto run and retrieve any rows affected. Alternatively, calldbSendQuery+dbGetRowsAffected+dbClearResultto possibly achieve the same result asdbExecute.