{"id":87,"date":"2016-10-03T17:52:17","date_gmt":"2016-10-03T10:52:17","guid":{"rendered":"http:\/\/arikuncoro.xyz\/wordpress\/?p=87"},"modified":"2016-11-25T15:57:04","modified_gmt":"2016-11-25T15:57:04","slug":"import-from-a-database-in-r","status":"publish","type":"post","link":"https:\/\/arikuncoro.xyz\/blog\/data-science\/r-python-sql-linux\/import-from-a-database-in-r\/","title":{"rendered":"Import from a Database in R"},"content":{"rendered":"<figure id=\"attachment_89\" aria-describedby=\"caption-attachment-89\" style=\"width: 386px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/arikuncoro.xyz\/blog\/wp-content\/uploads\/2016\/10\/import.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-89\" src=\"http:\/\/arikuncoro.xyz\/blog\/wp-content\/uploads\/2016\/10\/import-300x129.png\" alt=\"Importing database to R\" width=\"386\" height=\"166\" \/><\/a><figcaption id=\"caption-attachment-89\" class=\"wp-caption-text\">Importing database to R<\/figcaption><\/figure>\n<p>When you use R for data analysis, sometimes you have\u00a0to import data\u00a0from a Database (e.g. SQL), instead of just import data by reading the csv, excel, or mat file. To do this, we have to do several steps. Good thing <a href=\"http:\/\/datacamp.com\">Datacamp<\/a> course provide us step-by-step guidance.<\/p>\n<p>There are 5 steps you need to do if you did not install RMySQL package yet. If you have installed the package, you could skip step 1. In step 2, you need to establish connection with database with dbconnect() function. Then, in step 3\u00a0you can list the database tables using dbListTables(), in which three tables are available: users, tweats, and comments. In Step 4, you can import the table and assign into a data frame variable. Last but not least, if you have finish importing data, the polite way must be performed is disconnecting the database using dbDisconnect() function.<\/p>\n<p>You can find the R-script described as follow:<\/p>\n<pre class=\"lang:r decode:true\">#Step 1, install the RMySQL package (only if you did not install the package)\u00a0\r\ninstall.packages(\"RMySQL\")\r\n\r\n\r\n#Step 2, Establish a connection \r\nlibrary(DBI)\r\ncon &lt;- dbConnect(RMySQL::MySQL(), \r\ndbname = \"tweater\", \r\nhost = \"courses.csrrinzqubik.us-east-1.rds.amazonaws.com\", \r\nport = 3306,\r\nuser = \"student\",\r\npassword = \"datacamp\")\r\n\r\n\r\n#Step 3, List the database tables\r\ntables &lt;- dbListTables(con)\r\nstr(tables) #display structure of tables\r\n\r\n#Step 4, Import data from a table\r\nusers &lt;- dbReadTable(con,\"users\")\r\nusers #print users\r\n\r\ntweats &lt;- dbReadTable(con,\"tweats\")\r\ntweats #print tweats\r\n\r\ncomments &lt;- dbReadTable(con,\"comments\")\r\ncomments #print comments\r\n\r\n#Step5, disconnect database \r\ndbDisconnect(con)<\/pre>\n<p>Hope it helps! ;D<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When you use R for data analysis, sometimes you have\u00a0to import data\u00a0from a Database (e.g. SQL), instead of just import &#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[85],"tags":[90,94,102,103,107,108,109],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/arikuncoro.xyz\/blog\/wp-json\/wp\/v2\/posts\/87"}],"collection":[{"href":"https:\/\/arikuncoro.xyz\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/arikuncoro.xyz\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/arikuncoro.xyz\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/arikuncoro.xyz\/blog\/wp-json\/wp\/v2\/comments?post=87"}],"version-history":[{"count":2,"href":"https:\/\/arikuncoro.xyz\/blog\/wp-json\/wp\/v2\/posts\/87\/revisions"}],"predecessor-version":[{"id":502,"href":"https:\/\/arikuncoro.xyz\/blog\/wp-json\/wp\/v2\/posts\/87\/revisions\/502"}],"wp:attachment":[{"href":"https:\/\/arikuncoro.xyz\/blog\/wp-json\/wp\/v2\/media?parent=87"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/arikuncoro.xyz\/blog\/wp-json\/wp\/v2\/categories?post=87"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/arikuncoro.xyz\/blog\/wp-json\/wp\/v2\/tags?post=87"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}