How to connect Database using R?

Description

To connect the database with R programming.

Package and Function:

  • R Package : RMySQL
  • R Function :dbConnect(MySQL(),
    user=’root’, password=’’, dbname=’
    sangeetha’,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”) — to
    selectdata from table
  • R Function : dbSendQuery(connection,
    “INSERT INTO `tab_2`(`s.no`,
    `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 `s.no`=[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

#install.packages(“RMySQL”)
library(“RMySQL”)

#Create connection object to MySQL database
connection<-dbConnect(MySQL(),user=’root’,password=”root123″,dbname=”sangeetha”,host=”localhost”)

#List tables available in this database
dbListTables(connection)

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

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

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

#Update rows in the table
dbSendQuery(connection , “UPDATE `tab_2` SET `s.no`=’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