在r中使用sqldf时无法连接数据库

I loaded a csv file to my R, and when I Tried to use sqldf to select some column, it always went to

我将一个csv文件加载到我的R中,当我尝试使用sqldf选择一些列时,它总是去

Error in .local(drv, ...) : 
  Failed to connect to database: Error: Access denied for user 
  'User'@'localhost' (using password: NO)
Error in !dbPreExists : invalid argument type

I don't know how to fix it.

我不知道如何解决它。

Here is my script:

这是我的脚本:

library("RMySQL")
library(sqldf)
acs<-read.csv("getdata_data_ss06pid.csv",head = T)
sqldf("select pwgtp1 from acs where AGEP < 50")

2 个解决方案

#1

It doesn't seem like you need to load the RMySQL library when using sqldf, since you have already read the data into memory, which seems to be the problem here:

在使用sqldf时似乎不需要加载RMySQL库,因为您已经将数据读入内存,这似乎是这里的问题:

library(RMySQL)
library(sqldf)
sqldf("select * from df limit 6")

Error in .local(drv, ...) : Failed to connect to database: Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Error in !dbPreExists : invalid argument type

.local(drv,...)出错:无法连接到数据库:错误:无法通过套接字'/tmp/mysql.sock'连接到本地MySQL服务器(2)错误!dbPreExists:无效的参数类型

However if RMySQL library is detached, sqldf works:

但是,如果分离RMySQL库,sqldf可以工作:

detach("package:RMySQL", unload=TRUE)
sqldf("select * from df limit 6")
#   time  type
# 1    1 type1
# 2    2 type1
# 3    3 type2
# 4    4 type1
# 5    5 type2
# 6    6 type1

#2

TL;DR

Instead of unlaoding the RMySql package, explicitly set the sqldf default driver option to SQLite before calling the sqldf function:

在调用sqldf函数之前,显式将sqldf默认驱动程序选项设置为SQLite,而不是取消编码RMySql包:

options(sqldf.driver = "SQLite")
sqldf("select * from df limit 6")

Explanation

If not explicitly defined, the sqldf package decides which DB driver to use as follows:

如果没有明确定义,sqldf包决定使用哪个DB驱动程序,如下所示:

If not specified then the "dbDriver" option is checked and if that is not set then sqldf checks whether RPostgreSQL, RMySQL or RH2 is loaded in that order and the driver corresponding to the first one found is used. If none are loaded then "SQLite" is used. dbname=NULL causes the default to be used.

如果未指定,则选中“dbDriver”选项,如果未设置,则sqldf检查是否按顺序加载RPostgreSQL,RMySQL或RH2,并使用与找到的第一个驱动程序对应的驱动程序。如果没有加载,则使用“SQLite”。 dbname = NULL导致使用默认值。

In your case, RMySql has already been loaded and sqldf will try to use the MySQL DB and write into a schema called test. Detaching and unloading the RMySQL package is one option, but not necessary. As mentioned by @GaborGrothendieck in his comment, the easiest fix is to simply tell sqldf which DB driver to use explicitly, i.e.

在您的情况下,已经加载了RMySql,并且sqldf将尝试使用MySQL DB并写入名为test的模式。分离和卸载RMySQL包是一种选择,但不是必需的。正如@GaborGrothendieck在评论中所提到的,最简单的解决方法是简单告诉sqldf明确使用哪个DB驱动程序,即

sqldf("select * from df limit 6", drv="SQLite")

To not always having to add drv="SQLite", you can permanently set the default driver for the session to SQLite:

要不总是必须添加drv =“SQLite”,您可以将会话的默认驱动程序永久设置为SQLite:

options(sqldf.driver = "SQLite")

#1