R连接数据库-RMySQL

使用R数据库接口

install.packages("RMySQL")

连接数据库

conn <- dbConnect(MySQL(),host="192.168.160.192",dbname="ggg",user="sxj_hi",password="suxiaojun1314")

如果出现Plugin caching_sha2_password could not be loaded错误

SELECT user,host,authentication_string,plugin FROM mysql.user;
ALTER USER ‘sxj_hi’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘mysql1314’ ;
ALTER USER ‘sxj_hi’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘suxiaojun1314’;
FLUSH PRIVILEGES;

查看连接信息

summary(conn)

## <MySQLConnection:0,0>
##   User:   sxj_hi 
##   Host:   192.168.160.192 
##   Dbname: ggg 
##   Connection type: 192.168.160.192 via TCP/IP 
## 
## Results:

dbGetInfo(conn)

## $host
## [1] "192.168.160.192"
## 
## $user
## [1] "sxj_hi"
## 
## $dbname
## [1] "ggg"
## 
## $conType
## [1] "192.168.160.192 via TCP/IP"
## 
## $serverVersion
## [1] "8.0.13"
## 
## $protocolVersion
## [1] 10
## 
## $threadId
## [1] 180
## 
## $rsId
## list()

设置编码方式

dbSendQuery(conn,"set names gbk")

写入数据表，表可以存在或者不存在

dbWriteTable(conn,"mtcars",mtcars,overwrite=TRUE)
dbWriteTable(conn,"airquality",airquality,overwrite=TRUE)

如果出现The used command is not allowed with this MySQL version错误

show global variables like ‘local_infile’;
set global local_infile=‘ON’;

查看数据库中的表

dbListTables(conn)

## [1] "airquality" "mtcars"

查看整个表

dbReadTable(conn,"mtcars",row.names=1) #以第一列为行名

##                      mpg cyl  disp  hp drat    wt  qsec vs am gear carb
## Mazda RX4           21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
## Mazda RX4 Wag       21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
## Datsun 710          22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
## Hornet 4 Drive      21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
## Hornet Sportabout   18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2
## Valiant             18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1
## Duster 360          14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4
## Merc 240D           24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
## Merc 230            22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
## Merc 280            19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
## Merc 280C           17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4
## Merc 450SE          16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3
## Merc 450SL          17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3
## Merc 450SLC         15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3
## Cadillac Fleetwood  10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4
## Lincoln Continental 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4
## Chrysler Imperial   14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4
## Fiat 128            32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
## Honda Civic         30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
## Toyota Corolla      33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
## Toyota Corona       21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
## Dodge Challenger    15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2
## AMC Javelin         15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2
## Camaro Z28          13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4
## Pontiac Firebird    19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2
## Fiat X1-9           27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
## Porsche 914-2       26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
## Lotus Europa        30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
## Ford Pantera L      15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4
## Ferrari Dino        19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6
## Maserati Bora       15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8
## Volvo 142E          21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2

查询方法1-dbGetQuery()

res <- dbGetQuery(conn,"select * from mtcars limit 6")
class(res)

## [1] "data.frame"

res

##           row_names  mpg cyl disp  hp drat    wt  qsec vs am gear carb
## 1         Mazda RX4 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
## 2     Mazda RX4 Wag 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
## 3        Datsun 710 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
## 4    Hornet 4 Drive 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
## 5 Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
## 6           Valiant 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

查询方法2-dbSendQuery()

res <- dbSendQuery(conn,"select * from airquality limit 6")
class(res)

## [1] "MySQLResult"
## attr(,"package")
## [1] "RMySQL"

dbFetch(res,n=2) #获取前两条数据

##   row_names Ozone Solar.R Wind Temp Month Day
## 1         1    41     190  7.4   67     5   1
## 2         2    36     118  8.0   72     5   2

dbFetch(res,n=-1) #-1指全部数据，已被获取过，则为剩余全部数据

##   row_names Ozone Solar.R Wind Temp Month Day
## 1         3    12     149 12.6   74     5   3
## 2         4    18     313 11.5   62     5   4
## 3         5    NA      NA 14.3   56     5   5
## 4         6    28      NA 14.9   66     5   6

dbClearResult(res) #清除查询结果

删除表

dbRemoveTable(conn,"mtcars")

dbListTables(conn)

## [1] "airquality"

关闭连接

dbDisconnect(conn)

R连接数据库-RMySQL

_ggg

2018年10月31日

使用R数据库接口

关于sqldf