1 / 4

R and MySQL Database Tutorial

R and MySQL Database Tutorial.

gharrison
Download Presentation

R and MySQL Database Tutorial

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. R and MySQL Database Tutorial

  2. # Creating a database using RMySQL in R >library(RMySQL) #Load RMySQL packagesThe function dbConnect create a connection to a DBMS.The function requires : - a character string specifying the name of DBMS driver, e.g., "RSQLite", "RMySQL", "RPostgreSQL", or an existing DBIConnection object. -authorization arguments needed by the DBMS instance; these typically include user, password, dbname, host, etc.>mydb <- dbConnect(MySQL(), user=’io’, password=’1234', host='localhost') # Connect to the local host.The function dbSendQuery only submits and synchronously executes the SQL statement to the database engine. It does not extracts any records>dbSendQuery(mydb, "CREATE DATABASE bookstore;") # Create database bookstore>dbSendQuery(mydb, "USE bookstore” ) # Use database bookstore

  3. # Creating a table in a database using RMySQL . >dbSendQuery(mydb, "drop table if exists books, authors") # Remove table if exist>dbSendQuery(mydb, " CREATE TABLE books ( book_id INT, title VARCHAR(50),author VARCHAR(50));") # creating tables in bookstore: >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);") >dbSendQuery(mydb, "CREATE TABLE authors (author_id INT AUTO_INCREMENT PRIMARY KEY, author_last VARCHAR(50), author_first VARCHAR(50),country VARCHAR(50));")

  4. # Adding data into tables # Fetch data from tables and close connection >dbSendQuery(mydb, "INSERT INTO authors (author_last, author_first, country) VALUES('Kumar','Manoj','India');") The functiondbFetchrecords from a previouslyexecutedquery. >res<-dbSendQuery(mydb, "SELECT LAST_INSERT_ID();") >last_id <-dbFetch(res) The funtiondbClearResultfreesallresources (local and remote) associated with a result set. It some cases (e.g., very large result sets) this can be a criticalstep to avoidexhaustingresources (memory, file descriptors, etc.) >dbClearResult(res) The funtiondbDisconnectcloses the connection, discardsallpending work, and freesresources (e.g., memory, sockets). >dbDisconnect (mydb)

More Related