Accessing Database (MS SQL Server/Oracle/MySQL) from R!

Folks,

In this blog we will learn how to access Oracle, MySQL & SQL Server databases from R!


Accessing Oracle Database using RJDBC Package

Below is my database hosted on AWS Cloud. See here how to setup Oracle Db on cloud (free tier).

11

R Packages required:-  install.packages(“RJDBC”)

Also download Oracle JDBC Drivers from here Link. After downloading, copy file location.

location.png

Commands:


driver <- JDBC("oracle.jdbc.driver.OracleDriver"," <paste JDBC Driver file location>")

dbConnect(driver , "jdbc:oracle:thin:@Host:Port:ServiceName or SID", "<username>", "<password>")

code1

R users have few more choices to access their Oracle Database: Using RODBC and ROracle packages.


MySQL Database

Here is my database hosted on AWS Cloud. See here how to setup MySQL Db on cloud (free tier).

1

a) Accessing MySQl Database using ODBC:

R Packages required:-  install.packages(“RODBC”)

For Windows: Go to start, search ODBC. Follow below steps of setup.

1

Click on “Add”.

2

Select MySQL ODBC Driver. If not present, then download using this link.

3

Provide your server details, username & password. Test your connection. Note down Data Source name.

4

Commands:


my_conn <- odbcConnect("<ODBC Data Source Name >")

sqlQuery(my_conn, paste("<Query>"))

code

b) Accessing MySQL DB using RMySQL Package:

R Packages required:-  install.packages(“RMySQL”)

Commands:


my_conn <- dbConnect(MySQL(), user="<username>", password="<password>", database= "<Database name>", host="<your host>")

result<- dbSendQuery(my_conn, "<Query>")

dbFetch(result)

code 2.png


Accessing SQL Server Database using ODBC

Here is my SQL Server database hosted locally.

server.png

a) Accessing SQL Server using ODBC:

For Windows: Go to start, search ODBC.

1

Follow below steps to setup ODBC datasource.

output_KUL4JC.gif

Commands:


my_conn <- odbcConnect("<Data Source Name>", uid="<username>", pwd="<password>")

sqlQuery(my_conn, "<Query>")

code2.png

b) Accessing SQL Server DB using RJDBC Package:

Suppose SQL Server database hosted on cloud. We can use below commands to connect.

R Packages required:-  install.packages(“RJDBC”) & Download SQL JDBC Drivers file from internet.


driver <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver",
"<location of SQL JDBC Drivers file on computer>")

conn <- dbConnect(driver , "jdbc:sqlserver://<serverName>", "<userID>", "<password>")

result <- dbGetQuery(conn, "<Query>")


Thanks!

Happy Learning!