dplyr as a database interface

General
1

Is it possible to run insert queries against PostgreSQL, using dplyr as a database interface ?

2

Check this out for an introduction:
https://dbplyr.tidyverse.org/articles/dbplyr.html

3

quoting from the tutorial:

"The goal of dbplyr is to automatically generate SQL for you so that you’re not forced to use it. However, SQL is a very large language and dbplyr doesn’t do everything. It focusses on SELECT statements, the SQL you write most often as an analyst."

Which led me to the thinking that only data retrieval is supported, can anyone confirm that.

4

You can copy from a data frame to a db:
https://dbplyr.tidyverse.org/reference/copy_to.src_sql.html

And you can generate a temporary table:
https://dbplyr.tidyverse.org/reference/memdb_frame.html

I don't think you can insert into tables using dbplyr, but you can use SQL code to do so:
https://dbplyr.tidyverse.org/reference/build_sql.html

1 Like
5

You don't really need dbplyr to run insert queries as DBI::dbWriteTable() will do.

On second thought (editing my previous answer) you can use dplyr - dplyr::db_insert_into(). My apologies for confusion about where the db_insert_into() function lives.

In PostgreSQL context DBI sometimes struggles with schemas, so you might want to consider a little workaround with a temporary table and executing SQL code directly via DBI::dbSendQuery():

dbWriteTable(con, SQL('tmp_tbl'), a_rstats_data_frame, overwrite = T) # a temporary table
dbSendQuery(con, 'insert into cruel_and_unusual_schema.final_table select * from tmp_tbl;') #flip the temporary table over 
dbSendQuery(con, 'drop table tmp_tbl;') #clean up...
1 Like
6

Fair point. I was purely basing my answer on using dplyr/dbplyr syntax. My last link would have required DBI::dbSendQuery() anyway.

1 Like
7

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.