db_table() describes a database table for data_edit(). You can
usually skip it and pass a dbplyr lazy table directly, e.g.
data_edit(tbl(con, "artists")); use db_table() when the key
cannot be detected automatically or the query is not a simple table
reference.
Arguments
- x
a
dbplyrlazy table (e.g.dplyr::tbl(con, "artists"), optionally withfilter(),select()orarrange()applied) or aDBIconnection.- table
name of the table to write to, a character string or a
DBI::Id(). Required whenxis a connection, otherwise detected from the lazy table.- key
name(s) of the column(s) that uniquely identify each row. When
NULLthe primary key is detected for SQLite, DuckDB and PostgreSQL databases.- limit
maximum number of rows to load into the editor at a time, set to
getOption("DataEditR.db_rows", 100000)by default. Usedplyr::filter()on the lazy table, or Rows in the editor, to choose which rows to load.- check
logical indicating whether updates and deletes should verify that rows have not changed in the database since they were loaded (optimistic concurrency), set to
TRUEby default.- readonly
TRUEto open the table read-only,FALSEto stop with an error when it cannot be written to. Set toNULLby default to open queries that cannot be written to (SQL, joins, tables without a primary key) read-only.
Value
an object of class editr_db_table to pass to
data_edit() or dataEditServer().
Details
Rows are matched to the database through the key columns. Edits are staged in the editor and only written when you press Done and confirm the commit. All changes are written in a single transaction: if any row has been changed by someone else since it was loaded (or no longer exists), the whole commit is rolled back and nothing is written. Adding, removing and renaming columns is disabled for database tables.
Tables do not have to fit in memory: at most limit rows are loaded
at a time, and Rows in the toolbar loads other rows (those matching
a filter, in a chosen order), with the filter and sorting done by the
database. Queries that cannot be written back (SQL, joins, or tables
without a primary key, e.g. read_parquet() with DuckDB) are opened
read-only, so large files can still be explored.
Author
Dillon Hammill, dillon.hammill21@gmail.com
Examples
if (interactive() && requireNamespace("RSQLite", quietly = TRUE)) {
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbExecute(con, "CREATE TABLE cars (id INTEGER PRIMARY KEY,
model TEXT, mpg REAL)")
DBI::dbAppendTable(con, "cars", data.frame(
model = rownames(mtcars), mpg = mtcars$mpg))
# key detected automatically
data_edit(dplyr::tbl(con, "cars"))
# explicit table description
data_edit(db_table(con, table = "cars", key = "id"))
}