R language Use Rmysql connection and read/write MySQL database test pass

R language uses Rmysql to connect and read and write MySQL database

Simply say the installation process, generally will not have a problem, the focus is rmysql the use of the way.

System Environment Description

Redhat System: Linux 460-42.6.32-431.29.2.el6.x86_64

System code: LANG=ZH_CN. UTF-8 (Chinese UTF-8 format)

MySQL version: MySQL Ver 14.14 distrib 5.1.73, Forredhat-linux-gnu (x86_64) using ReadLine 5.1

Install MySQL

1. See if the installation

Yum List installed mysql*

2. View an existing installation package

Yumlist mysql*

3. Install the MySQL server side

Yuminstall Mysql-devel

Yuminstall Mysql-server

4. Set MySQL default character and engine

Vim/etc/my.cnf

Add under [mysqld]

Default-character-set=utf8

Default-storage-engine=innodb

5. mysql Startup and shutdown

/etc/init.d/mysqld start

/etc/init.d/mysqld stop

6. Set the boot MySQL boot

/sbin/chkconfig--List

/sbin/chkconfig Add mysqld

/sbin/chkconfig mysqld on

Reference: MySQL installation and use under Redhat

Installing Rmysql

Install.packages ("Rmysql")

manipulating databases with Rmysql

[Plain]View PlainCopyprint?

1. Library (Rmysql)
2. Help (package= "Rmysql") #查看RMySQL的说明文档, there are rmysql all available methods
3. #创建数据库连接
4. Con <-dbconnect (MySQL (), host= "host", Dbname= "Test_dw", user= "user", password= "password")
5. #获取连接信息, view all tables under Database, and delete the TestName table
6. Summary (Con)
7. Dbgetinfo (Con)
8. Dblisttables (Con)
9. Dbremovetable (Con, "test")

[Plain]View PlainCopyprint?

1. #写数据库表
2. Fruits <-data.frame (id=1:5,name=c ("Apple", "banana", "pear", "corn", "watermelon"), Price=c (8.8,4.98,7.8,6,2.1), Status=c ("None", "Discount", "No "," Sold Out "," wholesale "))
3. Dblisttables (Con)
4. Dbwritetable (Con, "fruits", fruits)
5. Dblisttables (Con)

[Plain]View PlainCopy print?

1. #读数据库
2. Dbreadtable (Con, "fruits") #中文出现乱码, this is because the character encoding format is not uniform problem
3. Dbsendquery (Con, ' Set NAMES UTF8 ') #修正, formerly: Dbsendquery (Con, ' Set NAMES uft8 ')

Dbreadtable (Con, "fruits") #没有乱码问题了

Dbsendquery (Con, ' Set NAMES uft8 ') This setting is an old reminder error, it may take more than a few tries to do, don't know why.

Note: should be: Dbsendquery (Con, ' SET NAMES UTF8 ')

[Plain]View PlainCopyprint?

1. #写数据表, overwrite append
2. TestA <-data.frame (Id=1:6,e=c ("A", "B", "C", "D", "E", "F"), C=c ("I", "the", "World", "bounds", "change", "get"))
3. Testb <-data.frame (Id=7:13,e=c ("G", "H", "I", "J", "K", "L", "M"), C=c ("odd", "wonderful", "heel", "difficult", "to", "word", "metaphor"))
4. #直接写testA写入test表中
5. Dbwritetable (Con, "test", testa,row.names=f)
6. Dbreadtable (Con, "test")
7. #追加写testB追加在test表后
8. Dbwritetable (Con, "test", testb,append=t,row.names=f)
9. Dbreadtable (Con, "test")
10. #覆盖写testB覆盖test表
11. Dbwritetable (Con, "test", testb,overwrite=t,row.names=f)
12. Dbreadtable (Con, "test")

[Plain]View PlainCopyprint?

1. Two methods of #用SQL语句查询dbGetQuery () and Dbsendquery ()
2. Dbgetquery (Con, "select * from Fruits limit 3")
4. Res <-dbsendquery (Con, "select *from fruits")
5. Data <-dbfetch (res, n=2) #取前2条数据, n=-1 when all data is obtained
6. Data
7. Data <-dbfetch (res, n=-1) #取余下所有数据
8. Data
9. Dbclearresult (RES)
10. Dbdisconnect (Con) #断开连接

[Plain]View PlainCopyprint?

2. #用SQL语句批量查询
4. Con <-dbconnect (MySQL (), host= "host", Dbname= "Test_dw", user= "user", password= "password", client.flag= Client_ multi_statements) #client. Flag setting to support bulk queries
6. Dbsendquery (Con, ' SET NAMES uft-8 ')
8. SQL <-"select * from Fruits; SELECT * FROM Test "
10. Res1 <-dbsendquery (con,sql)
12. Dbfetch (res1, n =-1)
14. if (Dbmoreresults (Con)) {
16. Res2 <-Dbnextresult (Con)
18. Dbfetch (res2, n =-1)
20. }
22. Dblistresults (Con)
24. Dbclearresult (RES1)
26. Dbclearresult (Res2)
30. Dbdisconnect (Con)

Reference: Rmysql Database Programming Guide

R language Use Rmysql connection and read/write MySQL database test pass