How to connect Database using R?


To connect the database with R programming.

Package and Function:

  R Package : RMySQL

  R Function :dbConnect(MySQL(),user=root,password=,dbname=sangeeth,host=localhost)to create a connection object to MySQL database

  R Function : dbListTables(connection object) -- to list the tables available in the database

  R Function : dbSendQuery(connection,select * from table) -- toselectdata from table

  R Function : dbSendQuery(connection,INSERT INTO `tab_2`(``,`name`,`age`,`income`)VALUES([value-1],[value-2],[value-3],[value-4])) -- to insert data into table

  R Function : dbSendQuery(connection,UPDATE `tab_2` SET``=[value-1],`name`=[value-2],`age`=[value-3] ,`income`=[value-4] WHERE 1)-- to update data into table

  R Function : dbWriteTable(connection,connection, iris, iris[,],overwrite =TRUE)-- to create table in MySQL

  R Function : dbSendQuery(connection,drop table if exists table) -- to drop table from MySQL

#Loading Required Packages


#Create connection object to MySQL database

#List tables available in this database

#Query “tab_1″ table to get all its rows
tab_2_row<-dbSendQuery(connection,”select * from tab_2″)

#Select with age using query
tab_2_age<-dbSendQuery(connection,”select * from tab_2 where age=’22′”)

#Insert data into tables
dbSendQuery(connection,”INSERT INTO `tab_2`(``, `name`, `age`, `income`) VALUES (‘9′,’kushi’,’20’,’195′)”)

#Update rows in the table
dbSendQuery(connection , “UPDATE `tab_2` SET ``=’4′,`name`=’loganathan’,`age`=’56’,`income`=’834′ WHERE name=’loganathan'”)

#Creating table in MySQL
dbWriteTable(connection, “iris”, iris[,],overwrite=TRUE)

#Dropping table in MySQL
dbSendQuery(connection,”drop table if exists tab_1″)

Leave Comment

Your email address will not be published. Required fields are marked *

clear formSubmit