Skip to contents

Read data from database as data.table

read_from_db.Rd

Query a database using a DBI connection and SQL string, returning results as a data.table. Use for efficient, robust database reads in weighting and analysis pipelines.

Usage

read_from_db(con, query, disconnect = NULL, verbose = TRUE)

Arguments

con

DBI connection object. Use connect_to_pops() for POPS connection.

query

character(1). SQL query string.

disconnect

logical. If TRUE, disconnects after query. Default NULL (no action).

verbose

logical. If TRUE, emits warnings. Default TRUE.

Value

data.table. Query result. Columns depend on query.

Details

  • Executes SQL query using DBI connection.

  • Converts result to data.table, handling errors and warnings.

  • Optionally disconnects from database after query.

  • Warns if timestamps may be converted to local time.

  • Returns a copy; does not modify by reference.

  • Emits warnings for connection state and time zone issues.

Settings

None.

See also

  • connect_to_pops

  • scripts/io/read_db.R

Other io utilities: clean_data_dict(), cut_and_label(), db_table_has_geometry(), fetch_acs(), fetch_all_hts_tables(), fetch_from_db(), fetch_hts_table(), fetch_pums(), fetch_study_region(), find_project_root(), fix_value_labels_on_load(), get_db_table_name(), print_params(), pums_checksum(), read_pums_codebook(), record_checksum(), sampled_latlon_to_bg()

Examples

## Not run:
con <- connect_to_pops("metcouncil_wave1")
#> Error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
#> 	Is the server running locally and accepting connections on that socket?
query <- "select household_id, participant, idx from ms_person where study_id = 52"
data <- read_from_db(con, query)
#> Error in h(simpleError(msg, call)): error in evaluating the argument 'conn' in selecting a method for function 'dbGetQuery': object 'con' not found
## End(Not run)