R-rmysql Package Introduction Learning

Reference content:

Rmysql Database Programming Guide
R language uses Rmysql to connect and read and write MySQL database

Rmysql package installation and loading advantages issues, try to follow the prompts simple installation and loading can be used, the subsequent query data to solve.

3.2.1 Connection Database

dbConnect(MySQL(),host="localhost",dbname,user="",password="", ...)

library(RMySql)#可能是安装RMySQL的问题，导致直接library(RMySql)提示不存在RMySql包，通过下列方式可以加载成功（又挖一个坑...）library("RMySQL", lib.loc="/Library/Frameworks/R.framework/Versions/3.3/Resources/library")# Quarkcon <- dbConnect(MySQL(),host="localhost",dbname="Quark",user="root",password="root")#获取连接信息，查看database下所有表summary(con) dbGetInfo(con) dbListTables(con)#断开连接dbDisconnect(con)

3.2.2 Writing data

dbWriteTable(conn, name, value, row.names=T...)

• Append=t on the basis of the database table

• Overwrite=t overwrite the original table in the database

TestA <-data.frame (id=1:6,e=c ("A","B","C","D","E","F"), C=c ("I","Of","The World","Bounds","Change","Get"))Testb <-data.frame (id=7:13,e=c ("G","H","I","J","K","L","M"), C=c ("Odd","Wonderful","Heel","Difficult","To","Say","Metaphor"))#直接写testA写入test表中 dbwritetable (Con,"Test",testa,row.names=t) dbreadtable (Con,"Test")#追加写testB追加在test表后 dbwritetable (Con,"Test",testb,append=t,row.names=f) dbreadtable (Con,"Test")#覆盖写testB覆盖test表 dbwritetable (Con,"Test",testb,overwrite=t,row.names=f) dbreadtable (Con,"test") 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","None","sold out","wholesale") Dblisttables (Con) dbwritetable (con,"fruits", fruits,overwrite=t, row.names=f) dbreadtable (con,"fruits")           

3.2.3 reading data

Submits a query to the database and returns the results.

• Dbreadtable (conn, name, ...) get all the data from the Read table directly

• DBGETQUERY (conn, statement, ...) querying data with SQL statements

   #读数据库   #解决中文乱码问题     #dbSendQuery (Con, ' SET NAMES uft8 ')   dbreadtable (Con," test ")   #用SQL语句查询dbGetQuery () and Dbsendquery () two methods  Dbgetquery (Con,  "SELECT * FROM Test limit 3") res <-dbsendquery (con, " SELECT * FROM Test ") data <- dbfetch (res, n=    2)  Span class= "hljs-comment" >  #取前2条数据 , n=-1 for all data <-dbfetch (res, N=-1)  #取余下所有数据 data  Dbclearresult (res)     

batch queries with SQL statements , Client.flag settings that support bulk queries

con <- dbConnect(MySQL(),host="localhost",dbname="Quark",user="root",password="root",client.flag= CLIENT_MULTI_STATEMENTS) #client.flag设置这样支持批量查询#dbSendQuery(con,‘SET NAMES uft-8‘)sql <- "SELECT * FROM fruits;SELECT * FROM test"res1 <- dbSendQuery(con,sql)dbFetch(res1, n = -1)if (dbMoreResults(con)) { res2 <- dbNextResult(con) dbFetch(res2, n = -1)}dbListResults(con)dbClearResult(res1)dbClearResult(res2)

3.2.3 Deleting a table

dbRemoveTable(con,"test")

R-rmysql Package Introduction Learning