How to import data from PostgreSQL database to R?

user_8804093 0
Viewed 3626

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?

rpostgresql postico r postgresql
1 Answers

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
user_94159460
Related