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

Source: Internet
Author: User
Tags mysql version

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")
    3. Res <-dbsendquery (Con, "select *from fruits")
    4. Data <-dbfetch (res, n=2) #取前2条数据, n=-1 when all data is obtained
    5. Data
    6. Data <-dbfetch (res, n=-1) #取余下所有数据
    7. Data
    8. Dbclearresult (RES)
    9. Dbdisconnect (Con) #断开连接

[Plain]View PlainCopyprint?
  1. #用SQL语句批量查询
  2. Con <-dbconnect (MySQL (), host= "host", Dbname= "Test_dw", user= "user", password= "password", client.flag= Client_ multi_statements) #client. Flag setting to support bulk queries
  3. Dbsendquery (Con, ' SET NAMES uft-8 ')
  4. SQL <-"select * from Fruits; SELECT * FROM Test "
  5. Res1 <-dbsendquery (con,sql)
  6. Dbfetch (res1, n =-1)
  7. if (Dbmoreresults (Con)) {
  8. Res2 <-Dbnextresult (Con)
  9. Dbfetch (res2, n =-1)
  10. }
  11. Dblistresults (Con)
  12. Dbclearresult (RES1)
  13. Dbclearresult (Res2)
  14. Dbdisconnect (Con)


Reference: Rmysql Database Programming Guide

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.