| Title: | A Fast, Easy-to-Use Database Interface |
|---|---|
| Description: | Provides select, insert, update, upsert, and delete database operations. Supports 'PostgreSQL', 'MySQL', 'SQLite', and more, and plays nicely with the 'DBI' package. |
| Authors: | Andrew Kane [aut, cre] |
| Maintainer: | Andrew Kane <[email protected]> |
| License: | MIT + file LICENSE |
| Version: | 0.3.2 |
| Built: | 2024-11-14 05:45:07 UTC |
| Source: | https://github.com/ankane/dbx |
Create a database connection
dbxConnect( url = NULL, adapter = NULL, storage_tz = NULL, variables = list(), ... )dbxConnect( url = NULL, adapter = NULL, storage_tz = NULL, variables = list(), ... )
url |
A database URL |
adapter |
The database adapter to use |
storage_tz |
The time zone timestamps are stored in |
variables |
Session variables |
... |
Arguments to pass to dbConnect |
# SQLite db <- dbxConnect(adapter="sqlite", dbname=":memory:") ## Not run: # Postgres db <- dbxConnect(adapter="postgres", dbname="mydb") # MySQL db <- dbxConnect(adapter="mysql", dbname="mydb") # Others db <- dbxConnect(adapter=odbc(), database="mydb") ## End(Not run)# SQLite db <- dbxConnect(adapter="sqlite", dbname=":memory:") ## Not run: # Postgres db <- dbxConnect(adapter="postgres", dbname="mydb") # MySQL db <- dbxConnect(adapter="mysql", dbname="mydb") # Others db <- dbxConnect(adapter=odbc(), database="mydb") ## End(Not run)
Delete records
dbxDelete(conn, table, where = NULL, batch_size = NULL)dbxDelete(conn, table, where = NULL, batch_size = NULL)
conn |
A DBIConnection object |
table |
The table name to delete records from |
where |
A data frame of records to delete |
batch_size |
The number of records to delete in a single statement (defaults to all) |
db <- dbxConnect(adapter="sqlite", dbname=":memory:") table <- "forecasts" DBI::dbCreateTable(db, table, data.frame(id=1:3, temperature=20:22)) # Delete specific records bad_records <- data.frame(id=c(1, 2)) dbxDelete(db, table, where=bad_records) # Delete all records dbxDelete(db, table)db <- dbxConnect(adapter="sqlite", dbname=":memory:") table <- "forecasts" DBI::dbCreateTable(db, table, data.frame(id=1:3, temperature=20:22)) # Delete specific records bad_records <- data.frame(id=c(1, 2)) dbxDelete(db, table, where=bad_records) # Delete all records dbxDelete(db, table)
Close a database connection
dbxDisconnect(conn)dbxDisconnect(conn)
conn |
A DBIConnection object |
db <- dbxConnect(adapter="sqlite", dbname=":memory:") dbxDisconnect(db)db <- dbxConnect(adapter="sqlite", dbname=":memory:") dbxDisconnect(db)
Execute a statement
dbxExecute(conn, statement, params = NULL)dbxExecute(conn, statement, params = NULL)
conn |
A DBIConnection object |
statement |
The SQL statement to use |
params |
Parameters to bind |
db <- dbxConnect(adapter="sqlite", dbname=":memory:") DBI::dbCreateTable(db, "forecasts", data.frame(id=1:3, temperature=20:22)) dbxExecute(db, "UPDATE forecasts SET temperature = 20") dbxExecute(db, "UPDATE forecasts SET temperature = ?", params=list(20)) dbxExecute(db, "UPDATE forecasts SET temperature = ? WHERE id IN (?)", params=list(20, 1:3))db <- dbxConnect(adapter="sqlite", dbname=":memory:") DBI::dbCreateTable(db, "forecasts", data.frame(id=1:3, temperature=20:22)) dbxExecute(db, "UPDATE forecasts SET temperature = 20") dbxExecute(db, "UPDATE forecasts SET temperature = ?", params=list(20)) dbxExecute(db, "UPDATE forecasts SET temperature = ? WHERE id IN (?)", params=list(20, 1:3))
Insert records
dbxInsert(conn, table, records, batch_size = NULL, returning = NULL)dbxInsert(conn, table, records, batch_size = NULL, returning = NULL)
conn |
A DBIConnection object |
table |
The table name to insert |
records |
A data frame of records to insert |
batch_size |
The number of records to insert in a single statement (defaults to all) |
returning |
Columns to return |
db <- dbxConnect(adapter="sqlite", dbname=":memory:") table <- "forecasts" DBI::dbCreateTable(db, table, data.frame(id=1:3, temperature=20:22)) records <- data.frame(temperature=c(32, 25)) dbxInsert(db, table, records)db <- dbxConnect(adapter="sqlite", dbname=":memory:") table <- "forecasts" DBI::dbCreateTable(db, table, data.frame(id=1:3, temperature=20:22)) records <- data.frame(temperature=c(32, 25)) dbxInsert(db, table, records)
Select records
dbxSelect(conn, statement, params = NULL)dbxSelect(conn, statement, params = NULL)
conn |
A DBIConnection object |
statement |
The SQL statement to use |
params |
Parameters to bind |
db <- dbxConnect(adapter="sqlite", dbname=":memory:") DBI::dbCreateTable(db, "forecasts", data.frame(id=1:3, temperature=20:22)) dbxSelect(db, "SELECT * FROM forecasts") dbxSelect(db, "SELECT * FROM forecasts WHERE id = ?", params=list(1)) dbxSelect(db, "SELECT * FROM forecasts WHERE id IN (?)", params=list(1:3))db <- dbxConnect(adapter="sqlite", dbname=":memory:") DBI::dbCreateTable(db, "forecasts", data.frame(id=1:3, temperature=20:22)) dbxSelect(db, "SELECT * FROM forecasts") dbxSelect(db, "SELECT * FROM forecasts WHERE id = ?", params=list(1)) dbxSelect(db, "SELECT * FROM forecasts WHERE id IN (?)", params=list(1:3))
Update records
dbxUpdate( conn, table, records, where_cols, batch_size = NULL, transaction = TRUE )dbxUpdate( conn, table, records, where_cols, batch_size = NULL, transaction = TRUE )
conn |
A DBIConnection object |
table |
The table name to update |
records |
A data frame of records to insert |
where_cols |
The columns to use for WHERE clause |
batch_size |
The number of records to update in a single transaction (defaults to all) |
transaction |
Wrap the update in a transaction (defaults to true) |
db <- dbxConnect(adapter="sqlite", dbname=":memory:") table <- "forecasts" DBI::dbCreateTable(db, table, data.frame(id=1:3, temperature=20:22)) records <- data.frame(id=c(1, 2), temperature=c(16, 13)) dbxUpdate(db, table, records, where_cols=c("id"))db <- dbxConnect(adapter="sqlite", dbname=":memory:") table <- "forecasts" DBI::dbCreateTable(db, table, data.frame(id=1:3, temperature=20:22)) records <- data.frame(id=c(1, 2), temperature=c(16, 13)) dbxUpdate(db, table, records, where_cols=c("id"))
Upsert records
dbxUpsert( conn, table, records, where_cols, batch_size = NULL, returning = NULL, skip_existing = FALSE )dbxUpsert( conn, table, records, where_cols, batch_size = NULL, returning = NULL, skip_existing = FALSE )
conn |
A DBIConnection object |
table |
The table name to upsert |
records |
A data frame of records to upsert |
where_cols |
The columns to use for WHERE clause |
batch_size |
The number of records to upsert in a single statement (defaults to all) |
returning |
Columns to return |
skip_existing |
Skip existing rows |
## Not run: db <- dbxConnect(adapter="postgres", dbname="dbx") table <- "forecasts" DBI::dbCreateTable(db, table, data.frame(id=1:3, temperature=20:22)) records <- data.frame(id=c(3, 4), temperature=c(20, 25)) dbxUpsert(db, table, records, where_cols=c("id")) ## End(Not run)## Not run: db <- dbxConnect(adapter="postgres", dbname="dbx") table <- "forecasts" DBI::dbCreateTable(db, table, data.frame(id=1:3, temperature=20:22)) records <- data.frame(id=c(3, 4), temperature=c(20, 25)) dbxUpsert(db, table, records, where_cols=c("id")) ## End(Not run)