Closed ixxmu closed 3 years ago
今天是生信星球陪你的第784天
大神一句话,菜鸟跑半年。我不是大神,但我可以缩短你走弯路的半年~
就像歌儿唱的那样,如果你不知道该往哪儿走,就留在这学点生信好不好~
这里有豆豆和花花的学习历程,从新手到进阶,生信路上有你有我!
豆豆写于2021.1.29
之前我们探索了:在Mac上探索MySQL的安装与使用
下面内容将切换使用R和终端(简称“T”),意思就是在对应的应用(Rstudio + ternimal)中运行
install.packages(c('DBI','RMySQL'))
library(DBI)
# 用 guest 账户登录连接数据库 testdb
con <- DBI::dbConnect(RMySQL::MySQL(), dbname = 'testdb', host = "localhost", port = 3306, user = "guest", password = "guest123")
# 首先看一下我们用户当前的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| testdb |
+--------------------+
2 rows in set (0.00 sec)
# 然后切换到这个数据库
mysql> use testdb;
Database changed
# 查看该数据库下面的表格,可以看到当前为空
mysql> show tables;
Empty set (0.00 sec)
接下来,我们试图从R中写入一些表格到我们的数据库中
dbWriteTable(con, "mtcars", mtcars)
此时你可能会遇到两种报错:
第一种:
说明你的数据库当前存在这个文件,你可以试图修改第二个参数即名称
第二种:
我也搜索了一下解决方案,问题主要出在local_infile
这个文件上:https://stackoverflow.com/questions/59993844/error-loading-local-data-is-disabled-this-must-be-enabled-on-both-the-client
mysql> show global variables like 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
1 row in set (0.00 sec)
# 看这里是OFF
# 我们需要将他激活,结果又报错:因为我们需要使用root用户来运行这个
mysql> set global local_infile=true;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
# 重新登录root,然后激活,重新检查
mysql> mysql -u root -p
mysql> set global local_infile=true;
mysql> show global variables like 'local_infile';
# 退出,重新登录guest
mysql> exit
mysql> mysql --local_infile=1 -u guest -p
再次运行,就会成功
> dbWriteTable(con, "mtcars", mtcars)
[1] TRUE
mysql> USE testdb
mysql> SELECT * FROM mtcars;
dbGetQuery(con, "SELECT * FROM mtcars")
mysql> SHOW columns FROM mtcars;
+-----------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------+------+-----+---------+-------+
| row_names | text | YES | | NULL | |
| mpg | double | YES | | NULL | |
| cyl | double | YES | | NULL | |
| disp | double | YES | | NULL | |
| hp | double | YES | | NULL | |
| drat | double | YES | | NULL | |
| wt | double | YES | | NULL | |
| qsec | double | YES | | NULL | |
| vs | double | YES | | NULL | |
| am | double | YES | | NULL | |
| gear | double | YES | | NULL | |
| carb | double | YES | | NULL | |
+-----------+--------+------+-----+---------+-------+
12 rows in set (0.00 sec)
> dbGetQuery(con, "SHOW columns FROM mtcars")
Field Type Null Key Default Extra
1 row_names text YES <NA>
2 mpg double YES <NA>
3 cyl double YES <NA>
4 disp double YES <NA>
5 hp double YES <NA>
6 drat double YES <NA>
7 wt double YES <NA>
8 qsec double YES <NA>
9 vs double YES <NA>
10 am double YES <NA>
11 gear double YES <NA>
12 carb double YES <NA>
# 另外和R中内置的类型查看函数对比
str(mtcars)
'data.frame': 32 obs. of 11 variables:
$ mpg : num 21 21 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 ...
$ cyl : num 6 6 4 6 8 6 8 4 4 6 ...
$ disp: num 160 160 108 258 360 ...
$ hp : num 110 110 93 110 175 105 245 62 95 123 ...
$ drat: num 3.9 3.9 3.85 3.08 3.15 2.76 3.21 3.69 3.92 3.92 ...
$ wt : num 2.62 2.88 2.32 3.21 3.44 ...
$ qsec: num 16.5 17 18.6 19.4 17 ...
$ vs : num 0 0 1 1 0 1 0 1 1 1 ...
$ am : num 1 1 1 0 0 0 0 0 0 0 ...
$ gear: num 4 4 4 3 3 3 3 4 4 4 ...
$ carb: num 4 4 1 1 2 1 4 2 2 4 ...
主要看不同之处:
数据库将行名row_names
作为单独一列输入,格式是text;当然可以通过设置row.names=F
取消行名单独作为一列
当然,如果不想让行名作为单独的一列,在写入时可以设置:dbWriteTable(con, "mtcars", mtcars, row.names = FALSE)
如果原来存在这个名字的表,可以选择覆盖:overwrite=T
;或者在原表的基础上继续添加行:append=T
(这两个参数默认为FALSE)
If the append
argument is TRUE
, the rows in an existing table are preserved, and the new data are appended. If the table doesn't exist yet, it is created.
If the overwrite
argument is TRUE
, an existing table of the same name will be overwritten. This argument doesn't change behavior if the table does not exist yet.
library(DBI)
# 用 guest 账户登录连接数据库 demo
con <- DBI::dbConnect(RMySQL::MySQL(), dbname = 'testdb', host = "localhost", port = 3306, user = "guest", password = "guest123")
# 返回查询结果
table_desc <- dbGetQuery(con, "SHOW columns FROM mtcars")
# 写入/上传到mysql上,其中file_name就是文件名,file_content就是表格内容
dbWriteTable(con, file_name, file_content, append=F,row.names=F)
DBI 是一个用于数据库连接的基础包,
表的连接 dbConnect()
/dbDisconnect()
创建 dbCreateTable()
读 dbReadTable()
写 dbWriteTable()
删除 dbRemoveTable()
查 dbSendQuery()
/dbGetQuery()
:继续深入查看表是否存在 dbExistsTable()
、 表的各个字段 dbListFields()
、各个字段的存储类型 dbDataType()
等
因为要各司其职,协同合作
MySQL的优势就是可以存储比较大(成千上万)的数据库和表,这些内容在网页工具中,不适合放在R中去读取,因为R中的单纯读取都要耗费大量的时间。而且可能需要读取多次,或者需要同时用到多个表,因此会造成流程上的效率低下。一般来讲,MySQL主要负责数据库系统管理,优化流程;而R用于具体的对其中的每个表进行分析、统计,得出结果。
点击底部的“阅读原文”,获得更好的阅读体验哦😻
初学生信,很荣幸带你迈出第一步
🤓生信星球 🌎~ 一个不拽术语、通俗易懂的生信知识平台
https://mp.weixin.qq.com/s/PqfTufH8w6v52wbELDbHdw