Tags

, , , , 

library(RMySQL)
# Hello visitors, this tutorials is to connect and working with mysql server using RMySQl package
# Although, it is bit tricky and difficult to instal this package on lastest version on R, but you should do it before using this tutorial
# If in case, you find it difficult to instal RMySQl on your computer, please send me a message, so that I can develop a tutorials for that as well


library(RMySQL)

# mydb = dbConnect(MySQL(), user='manoj', password='password', host='localhost')

# creating a database using RMySQL in R

# dbSendQuery(mydb, "CREATE DATABASE bookstore;")

# dbSendQuery(mydb, "USE bookstore")

# reconnecting to database we just created using following command in R :

mydb = dbConnect(MySQL(), user='manoj', password='password', host='localhost', dbname="bookstore")

dbSendQuery(mydb, "drop table if exists books, authors")
# creating tables in bookstore:

dbSendQuery(mydb, "
CREATE TABLE books (
book_id INT,
title VARCHAR(50),
author VARCHAR(50));")
# Show table using R:

dbListTables(mydb)
## [1] "books"
# Considering our bookstore a bit more, we realize that we need to add a few more columns for data elements: publisher, publication year, ISBN number, genre (e.g., novel, poetry, drama), description of book, etc. 

# We also realize that we want MySQL to automatically assign a number to the book_id column so that we don't have to bother creating one for each row or worry about duplicates. 

# Additionally, we've decided to change the author column from the actual author's name to an identification number that we'll join to a separate table containing a list of authors. 

# This will reduce typing, and will make sorting and searching easier, as the data will be uniform. 

# To make these alterations to the table that we've already created, enter the following SQL command through R :

dbSendQuery(mydb, "ALTER TABLE books
CHANGE COLUMN book_id book_id INT AUTO_INCREMENT PRIMARY KEY,
CHANGE COLUMN author author_id INT,
ADD COLUMN description TEXT,
ADD COLUMN genre ENUM('novel','poetry','drama', 'tutorials', 'text', 'other'),
ADD COLUMN publisher_id INT,
ADD COLUMN pub_year VARCHAR(4),
ADD COLUMN isbn VARCHAR(20);")
# if R gives you an error:
#   Error in mysqlExecStatement(conn, statement, ...) : 
#   RS-DBI driver: (connection with pending rows, close resultSet before continuing)
# reconnect database using:

mydb = dbConnect(MySQL(), user='manoj', password='password', host='localhost', dbname="bookstore")

# and then run the above command of table alteration

# Now, Before moving on to adding data to our books table, let's quickly set up the authors table.

dbSendQuery(mydb, "CREATE TABLE authors
(author_id INT AUTO_INCREMENT PRIMARY KEY,
author_last VARCHAR(50),
author_first VARCHAR(50),
country VARCHAR(50));")
# Adding data into tables

dbSendQuery(mydb, "INSERT INTO authors
(author_last, author_first, country)
VALUES('Kumar','Manoj','India');")
# fetching last data insert id number:

last_id = fetch(dbSendQuery(mydb, "SELECT LAST_INSERT_ID();"))

# Inserting data into books table and using last insert ID number:

dbSendQuery(mydb, "INSERT INTO books
(title, author_id, isbn, genre, pub_year)
VALUES('R and MySQL', 1,'6900690075','tutorials','2014');")
# Note that I have not provided publisher's id and description in the above data insert, if you want you can modify that... at least try!
# Also note that we just entered for the author by using the LAST_INSERT_ID() function. you should also try:
# SELECT author_id, author_first FROM authors;
# and insert data using that....
try1 = fetch(dbSendQuery(mydb, "SELECT book_id, title, description
FROM books
WHERE genre = 'tutorials';"))
# That's all for now... will come back soon with some advanced tutorials on R and MySQL

 

RMySQL

RMySQL