5

I'm thinking of importing data from database directly into r using RPostgresQL package. So far, I used to write queries in Postico (a PostgreSQL client) software and export as csv and then import the csv file into R.
This is what I've written so far and no clue how to proceed next.

library('RPostgreSQL')
pg=dbDriver("PostgreSQL")
con = dbConnect(pg, user="msahil515", password="",
            host="localhost", port=5432, dbname="msahil515")

How do I load tables from the database into R after this or how to write queries in R to extract only necessary data from database?

zero323
  • 322,348
  • 103
  • 959
  • 935
Mohamad Sahil
  • 165
  • 2
  • 12
  • 1
    `?dbSendQuery` / `?dbGetQuery` I mean, you _have_ to read R manual pages at some point, right? – hrbrmstr Nov 06 '18 at 18:38
  • @hrbrmstr Yeah, I tried with `dbWriteTable(con,"some_table_name",table_name,row.names=F)` but I get an error that the object `table_name` not found. – Mohamad Sahil Nov 06 '18 at 18:54
  • Why are you using `dbWriteTable()` when your question says you want to load tables and extract data? – hrbrmstr Nov 06 '18 at 19:24
  • Sorry, my bad! I wasn't even sure if R was connected to the correct database. `dbGetQuery()` worked for me. Thanks! – Mohamad Sahil Nov 06 '18 at 19:50

1 Answers1

7

Here is a straight answer to your question. This definitely can be extended

library('RPostgreSQL')

#create connection object
con <- dbConnect(drv =PostgreSQL(), 
                 user="msahil515", 
                 password="",
                 host="localhost", 
                 port=5432, 
                 dbname="msahil515")

dbListTables(con)   #list all the tables 

#query the database and store the data in datafame
first_results <- dbGetQuery(con, "SELECT * from FOO limit 10;")

dbDisconnect(con)   #disconnect from database
x85ms16
  • 587
  • 7
  • 17
  • 1
    Also, i recommend writing your credintials in .Renviron or .Rprofile instead of hardcoding on your credentials on the scripts. – x85ms16 Jun 25 '19 at 19:34
  • 1
    This answer was close but did not work for me : you need to change the driver name to "PostgreSQL" (as mentioned in the question, this might be a typo). – cbo Jul 20 '20 at 09:29