db_query: Query an RSQLite database.

In rcprd: Extraction and Management of Clinical Practice Research Datalink Data

Query an RSQLite database.

Description

Query an RSQLite database stored on the hard disk for observations with specific codes.

Usage

db_query(
  codelist,
  db_open = NULL,
  db = NULL,
  db_filepath = NULL,
  db_cprd = c("aurum", "gold"),
  tab = c("observation", "drugissue", "clinical", "immunisation", "test", "therapy",
    "hes_primary", "death"),
  codelist_vector = NULL
)

Arguments

codelist	Name of codelist to query the database with.
db_open	An open SQLite database connection created using RSQLite::dbConnect, to be queried.
db	Name of SQLITE database on hard disk, to be queried.
db_filepath	Full filepath to SQLITE database on hard disk, to be queried.
db_cprd	CPRD Aurum ('aurum') or gold ('gold').
tab	Name of table in SQLite database that is to be queried.
codelist_vector	Vector of codes to query the database with. This takes precedent over codelist if both are specified.

Details

Specifying db requires a specific underlying directory structure. The SQLite database must be stored in "data/sql/" relative to the working directory. If the SQLite database is accessed through db, the connection will be opened and then closed after the query is complete. The same is true if the database is accessed through db_filepath. A connection to the SQLite database can also be opened manually using RSQLite::dbConnect, and then using the object as input to parameter db_open. After wards, the connection must be closed manually using RSQLite::dbDisconnect. If db_open is specified, this will take precedence over db or db_filepath.

Specifying codelist requires a specific underlying directory structure. The codelist on the hard disk must be stored in "codelists/analysis/" relative to the working directory, must be a .csv file, and contain a column "medcodeid", "prodcodeid" or "ICD10" depending on the chosen tab. The codelist can also be read in manually, and supplied as a character vector to codelist_vector. If codelist_vector is defined, this will take precedence over codelist.

Value

A data.table with observations contained in the specified codelist.

Examples

## Create connection to a temporary database
aurum_extract <- connect_database(file.path(tempdir(), "temp.sqlite"))

## Add observation data from all observation files in specified directory
cprd_extract(db = aurum_extract,
filepath = system.file("aurum_data", package = "rcprd"),
filetype = "observation")

## Query database for a specific medcode
db_query(db_open = aurum_extract,
tab ="observation",
codelist_vector = "187341000000114")

## clean up
RSQLite::dbDisconnect(aurum_extract)
unlink(file.path(tempdir(), "temp.sqlite"))

rcprd documentation built on April 12, 2025, 1:57 a.m.