This tutorial is for you if you need to use a richer set of SQLfeatures such as data manipulation queries, parameterized queries andqueries performed using SQL’s transaction features. Seevignette("DBI", package = "DBI") for a more basic tutorialcovering connecting to DBMS and executing simple queries.
dbGetQuery() works by calling a number of functionsbehind the scenes. If you need more control you can manually build yourown query, retrieve results at your selected rate, and release theresources involved by calling the same functions.
These functions are:
dbSendQuery() sends the SQL query to the DBMS andreturns aresult object. The query is limited toSELECT statements. If you want to send other statements,such asINSERT,UPDATE,DELETE,etc, usedbSendStatement().dbFetch() is called with theresult objectreturned bydbSendQuery(). It also accepts an argumentspecifying the number of rows to be returned, e.g. n = 200.If you want to fetch all the rows, usen = -1.dbClearResult() is called when you have finishedretrieving data. It releases the resources associated with theresult object.library(DBI)con<-dbConnect( RMariaDB::MariaDB(),host ="db.relational-data.org",port =3306,username ="guest",password ="relational",dbname ="sakila")res<-dbSendQuery(con,"SELECT * FROM film WHERE rating = 'G'")df<-dbFetch(res,n =3)dbClearResult(res)head(df,3)## film_id title## 1 2 ACE GOLDFINGER## 2 4 AFFAIR PREJUDICE## 3 5 AFRICAN EGG## description## 1 A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China## 2 A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank## 3 A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico## release_year language_id original_language_id rental_duration rental_rate## 1 2006 1 NA 3 4.99## 2 2006 1 NA 5 2.99## 3 2006 1 NA 6 2.99## length replacement_cost rating special_features## 1 48 12.99 G Trailers,Deleted Scenes## 2 117 26.99 G Commentaries,Behind the Scenes## 3 130 22.99 G Deleted Scenes## last_update## 1 2006-02-15 04:03:42## 2 2006-02-15 04:03:42## 3 2006-02-15 04:03:42If your dataset is large you may want to fetch a limited number ofrows at a time. As demonstrated below, this can be accomplished by usinga while loop where the functiondbHasCompleted() is used tocheck for ongoing rows, anddbFetch() is used with then = X argument, specifying how many rows to return on eachiteration. Again, we calldbClearResult() at the end torelease resources.
res<-dbSendQuery(con,"SELECT * FROM film")while (!dbHasCompleted(res)) { chunk<-dbFetch(res,n =300)print(nrow(chunk))}## [1] 300## [1] 300## [1] 300## [1] 100dbSendQuery() can be used with parameterized SQLqueries. DBI supports two ways to avoid SQL injection attacks fromuser-supplied parameters: quoting and parameterized queries.
Quoting of parameter values is performed using the functiondbQuoteLiteral(), which supports many R data types,including date and time.1
In cases where users may be supplying table or column names to use inthe query for data retrieval, those names or identifiers must also beescaped. As there may be DBMS-specific rules for escaping theseidentifiers, DBI provides the functiondbQuoteIdentifier()to generate a safe string representation.
safe_id<-dbQuoteIdentifier(con,"rating")safe_param<-dbQuoteLiteral(con,"G")query<-paste0("SELECT title, ", safe_id," FROM film WHERE ", safe_id," = ", safe_param)query## [1] "SELECT title, `rating` FROM film WHERE `rating` = 'G'"## title rating## 1 ACE GOLDFINGER G## 2 AFFAIR PREJUDICE G## 3 AFRICAN EGG G## Showing 3 out of 178 rows.The same result can be had by usingglue::glue_sql(). Itperforms the same safe quoting on any variable or R statement appearingbetween braces within the query string.
id<-"rating"param<-"G"query<- glue::glue_sql("SELECT title, {`id`} FROM film WHERE {`id`} = {param}",.con = con)df<-dbGetQuery(con, query)head(df,3)## title rating## 1 ACE GOLDFINGER G## 2 AFFAIR PREJUDICE G## 3 AFRICAN EGG GRather than performing the parameter substitution ourselves, we canpush it to the DBMS by including placeholders in the query. DifferentDBMS use different placeholder schemes, DBI passes through the SQLexpression unchanged.
MariaDB uses a question mark (?) as placeholder and expects anunnamed list of parameter values. Other DBMS may use named parameters.We recommend consulting the documentation for the DBMS you are using. Asan example, a web search for “mariadb parameterized queries” leads tothe documentation for thePREPAREstatement which mentions:
Within the statement, “?” characters can be used as parameter markersto indicate where data values are to be bound to the query later whenyou execute it.
Currently there is no list of which placeholder scheme a particularDBMS supports.
Placeholders only work for literal values. Other parts of the query,e.g. table or column identifiers, still need to be quoted withdbQuoteIdentifier().
For a single set of parameters, theparams argument todbSendQuery() ordbGetQuery() can be used. Ittakes a list and its members are substituted in order for theplaceholders within the query.
params<-list("G")safe_id<-dbQuoteIdentifier(con,"rating")query<-paste0("SELECT * FROM film WHERE ", safe_id," = ?")query## [1] "SELECT * FROM film WHERE `rating` = ?"## film_id title## 1 2 ACE GOLDFINGER## 2 4 AFFAIR PREJUDICE## 3 5 AFRICAN EGG## description## 1 A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China## 2 A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank## 3 A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico## release_year language_id original_language_id rental_duration rental_rate## 1 2006 1 NA 3 4.99## 2 2006 1 NA 5 2.99## 3 2006 1 NA 6 2.99## length replacement_cost rating special_features## 1 48 12.99 G Trailers,Deleted Scenes## 2 117 26.99 G Commentaries,Behind the Scenes## 3 130 22.99 G Deleted Scenes## last_update## 1 2006-02-15 04:03:42## 2 2006-02-15 04:03:42## 3 2006-02-15 04:03:42Below is an example query using multiple placeholders with theMariaDB driver. The placeholders are supplied as a list of valuesordered to match the position of the placeholders in the query.
q_params<-list("G",90)query<-"SELECT title, rating, length FROM film WHERE rating = ? AND length >= ?"res<-dbSendQuery(con, query,params = q_params)dbFetch(res,n =3)## title rating length## 1 AFFAIR PREJUDICE G 117## 2 AFRICAN EGG G 130## 3 ALAMO VIDEOTAPE G 126When you wish to perform the same query with different sets ofparameter values,dbBind() is used. There are two ways tousedbBind(). Firstly, it can be used multiple times withsame query.
## film_id title## 1 2 ACE GOLDFINGER## 2 4 AFFAIR PREJUDICE## 3 5 AFRICAN EGG## description## 1 A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China## 2 A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank## 3 A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico## release_year language_id original_language_id rental_duration rental_rate## 1 2006 1 NA 3 4.99## 2 2006 1 NA 5 2.99## 3 2006 1 NA 6 2.99## length replacement_cost rating special_features## 1 48 12.99 G Trailers,Deleted Scenes## 2 117 26.99 G Commentaries,Behind the Scenes## 3 130 22.99 G Deleted Scenes## last_update## 1 2006-02-15 04:03:42## 2 2006-02-15 04:03:42## 3 2006-02-15 04:03:42## film_id title## 1 1 ACADEMY DINOSAUR## 2 6 AGENT TRUMAN## 3 12 ALASKA PHANTOM## description## 1 A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies## 2 A Intrepid Panorama of a Robot And a Boy who must Escape a Sumo Wrestler in Ancient China## 3 A Fanciful Saga of a Hunter And a Pastry Chef who must Vanquish a Boy in Australia## release_year language_id original_language_id rental_duration rental_rate## 1 2006 1 NA 6 0.99## 2 2006 1 NA 3 2.99## 3 2006 1 NA 6 0.99## length replacement_cost rating special_features## 1 86 20.99 PG Deleted Scenes,Behind the Scenes## 2 169 17.99 PG Deleted Scenes## 3 136 22.99 PG Commentaries,Deleted Scenes## last_update## 1 2006-02-15 04:03:42## 2 2006-02-15 04:03:42## 3 2006-02-15 04:03:42Secondly,dbBind() can be used to execute the samestatement with multiple values at once.
res<-dbSendQuery(con,"SELECT * FROM film WHERE rating = ?")dbBind(res,list(c("G","PG")))dbFetch(res,n =3)## film_id title## 1 2 ACE GOLDFINGER## 2 4 AFFAIR PREJUDICE## 3 5 AFRICAN EGG## description## 1 A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China## 2 A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank## 3 A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico## release_year language_id original_language_id rental_duration rental_rate## 1 2006 1 NA 3 4.99## 2 2006 1 NA 5 2.99## 3 2006 1 NA 6 2.99## length replacement_cost rating special_features## 1 48 12.99 G Trailers,Deleted Scenes## 2 117 26.99 G Commentaries,Behind the Scenes## 3 130 22.99 G Deleted Scenes## last_update## 1 2006-02-15 04:03:42## 2 2006-02-15 04:03:42## 3 2006-02-15 04:03:42Use a list of vectors if your query has multiple parameters:
q_params<-list(c("G","PG"),c(90,120))query<-"SELECT title, rating, length FROM film WHERE rating = ? AND length >= ?"res<-dbSendQuery(con, query,params = q_params)dbFetch(res,n =3)## title rating length## 1 AFFAIR PREJUDICE G 117## 2 AFRICAN EGG G 130## 3 ALAMO VIDEOTAPE G 126Always disconnect from the database when done.
For SQL queries that affect the underlying database, such as UPDATE,DELETE, INSERT INTO, and DROP TABLE, DBI provides two functions.dbExecute() passes the SQL statement to the DBMS forexecution and returns the number of rows affected.dbSendStatement() performs in the same manner, but returnsa result object. CalldbGetRowsAffected() with the resultobject to get the count of the affected rows. You then need to calldbClearResult() with the result object afterwards torelease resources.
In actuality,dbExecute() is a convenience function thatcallsdbSendStatement(),dbGetRowsAffected(),anddbClearResult(). You can use these functions if youneed more control over the query process.
The subsequent examples use an in-memory SQL database provided byRSQLite::SQLite(), because the remote database used inabove examples does not allow writing.
library(DBI)con<-dbConnect(RSQLite::SQLite(),":memory:")dbWriteTable(con,"cars",head(cars,3))dbExecute( con,"INSERT INTO cars (speed, dist) VALUES (1, 1), (2, 2), (3, 3)")## [1] 3rs<-dbSendStatement( con,"INSERT INTO cars (speed, dist) VALUES (4, 4), (5, 5), (6, 6)")dbGetRowsAffected(rs)## [1] 3## speed dist## 1 4 2## 2 4 10## 3 7 4## Showing 3 out of 9 rows.Do not forget to disconnect from the database at the end.
DBI allows you to group multiple queries into a single atomictransaction. Transactions are initiated withdbBegin() andeither made persistent withdbCommit() or undone withdbRollback(). The example below updates two tables andensures that either both tables are updated, or no changes are persistedto the database and an error is thrown.
con<-dbConnect(RSQLite::SQLite(),":memory:")dbWriteTable(con,"cash",data.frame(amount =100))dbWriteTable(con,"account",data.frame(amount =2000))withdraw<-function(amount) {# All operations must be carried out as logical unit:dbExecute(con,"UPDATE cash SET amount = amount + ?",list(amount))dbExecute(con,"UPDATE account SET amount = amount - ?",list(amount))}withdraw_transacted<-function(amount) {# Ensure atomicitydbBegin(con)# Perform operationwithdraw(amount)# Persist resultsdbCommit(con)}withdraw_transacted(300)After withdrawing 300 credits, the cash is increased and the accountis decreased by this amount. The transaction ensures that either bothoperations succeed, or no change occurs.
## amount## 1 400## amount## 1 1700We can roll back changes manually if necessary. Do not forget to calldbRollback() in case of error, otherwise the transactionremains open indefinitely.
withdraw_if_funds<-function(amount) {dbBegin(con)withdraw(amount)# Rolling back after detecting negative value on account:if (dbReadTable(con,"account")$amount>=0) {dbCommit(con)TRUE }else {message("Insufficient funds")dbRollback(con)FALSE }}withdraw_if_funds(5000)## Insufficient funds## [1] FALSE## amount## 1 400## amount## 1 1700dbWithTransaction() simplifies using transactions. Passit a connection and the code you want to run as a transaction. It willexecute the code and calldbCommit() on success and calldbRollback() if an error is thrown.
withdraw_safely<-function(amount) {dbWithTransaction(con, {withdraw(amount)if (dbReadTable(con,"account")$amount<0) {stop("Error: insufficient funds",call. =FALSE) } })}withdraw_safely(5000)## Error: Error: insufficient funds## amount## 1 400## amount## 1 1700As usual, do not forget to disconnect from the database whendone.
That concludes the major features of DBI. For more details on thelibrary functions covered in this tutorial and thevignette("DBI", package = "DBI") introductory tutorial seethe DBI specification atvignette("spec", package = "DBI").If you are after a data manipulation library that works at a higherlevel of abstraction, check outdplyr. It is a grammar of datamanipulation that can work with local dataframes and remote databasesand uses DBI under the hood.
An older method,dbQuoteString(), was usedto quote string values only. ThedbQuoteLiteral() methodforwards todbQuoteString() for character vectors. Users donot need to distinguish between these two cases.↩︎