dvha.db¶

SQL Connector¶

Tools used to communicate with the SQL database

class dvha.db.sql_connector.DVH_SQL(*config, db_type=None, group=1)[source]¶

Bases: object

This class is used to communicate to the SQL database

Parameters

config (dict) – optional SQL login credentials, stored values used if nothing provided. Allowed keys are ‘host’, ‘port’, ‘dbname’, ‘user’, ‘password’
db_type (str, optional) – either ‘pgsql’ or ‘sqlite’
group (int, optional) – use a group-specific connection, either 1 or 2

change_mrn(old, new)[source]¶

Edit all mrns in database

Parameters

old (str) – current mrn
new (str) – new mrn

change_uid(old, new)[source]¶

Edit study instance uids in database

Parameters

old (str) – current study instance uid
new (str) – new study instance uid

check_table_exists(table_name)[source]¶

Check if a table exists

Parameters: table_name (st) – the SQL table to check
Returns: True if table_name exists
Return type: bool

close()[source]¶: Close the SQL DB connection

delete_dvh(roi_name, study_instance_uid)[source]¶

Delete a specified DVHs table row

Parameters

roi_name (str) – the roi name for the row to be deleted
study_instance_uid (str) – the associated study instance uid

delete_rows(condition_str, ignore_tables=None)[source]¶

Delete all rows from all tables not in ignore_table for a given condition. Useful when deleting a plan/patient

Parameters

condition_str (str: str) – a condition in SQL syntax
ignore_tables (list, optional) – tables to be excluded from row deletions

does_db_exist()[source]¶

Check if database exists

Returns: True if the database exists
Return type: bool

drop_table(table)[source]¶

Delete a table in the database if it exists

Parameters: table (str) – SQL table

drop_tables()[source]¶: Delete all tables in the database if they exist

execute_file(sql_file_name)[source]¶

Executes lines within provided text file to SQL

Parameters: sql_file_name (str) – absolute file path of a text file containing SQL commands

execute_str(command_str)[source]¶

Execute and commit a string in proper SQL syntax, can handle multiple lines split by

Parameters: command_str (str) – command or commands to be executed and committed

export_to_sqlite(file_path, callback=None, force=False)[source]¶

Create a new SQLite database and import this database’s data

Parameters

file_path (str) – Path where the new SQLite database will be created
callback (callable, optional) – optional function to be called on each row insertion. Should accept table (str), current row (int), total_row_count (int) as parameters
force (bool, optional) – ignore duplicate StudyInstanceUIDs if False

get_column_names(table_name)[source]¶

Get all of the column names for a specified table

Parameters: table_name (str) – SQL table
Returns: All columns names in table_name
Return type: list

get_dicom_file_paths(mrn=None, uid=None)[source]¶

Lookup the dicom file paths of imported data

Parameters

mrn (str, optional) – medical record number
uid (str, optional) – study instance uid

Returns

Query return from DICOM_Files table

Return type

list

get_max_value(table, column, condition=None)[source]¶

Get the maximum value in the database for a given table and column

Parameters

table (str) – SQL table
column (str) – SQL column
condition (str, optional) – Condition in SQL syntax

Returns

The maximum value for table.column

Return type

any

get_min_value(table, column, condition=None)[source]¶

Get the minimum value in the database for a given table and column

Parameters

table (str) – SQL table
column (str) – SQL column
condition (str, optional) – Condition in SQL syntax

Returns

The minimum value for table.column

Return type

any

get_ptv_counts()[source]¶

Get number of PTVs for each study instance uid

Returns: PTV counts stored by study_instance_uid
Return type: dict

get_roi_count_from_query(uid=None, dvh_condition=None)[source]¶

Counts the DVH rows that match the provided conditions

Parameters

uid (str, optional) – study instance uid
dvh_condition (str, optional) – condition in SQL syntax for the DVHs table

Returns

Number of rows in the DVHs table matching the provided parameters

Return type

int

get_row_count(table, condition=None)[source]¶

Parameters

table (str) – SQL table
condition (str) – SQL condition

Returns

Number of rows in table meeting condition

Return type

int

get_sql_function_value(func, table, column, condition=None, first_value_only=True)[source]¶

Helper function used by get_min_values and get_max_values

Parameters

func – SQL compatible function
table (str) – SQL table
column (str) – SQL column
condition (str, optional) – Condition in SQL syntax (Default value = None)
first_value_only (bool, optional) – if true, only return the first value, otherwise all values returned

Returns

Results of cursor.fetchone() or cursor.fetchone()[0] if first_value_only is True

Return type

list, any

get_unique_values(table, column, *condition, **kwargs)[source]¶

Uses SELECT DISTINCT to get distinct values in database

Parameters

table (str) – SQL table
column (str) – SQL column
condition (str, optional) – Condition in SQL syntax
kwargs – option to ignore null values in return

Returns

Unique values in table.column

Return type

list

ignore_dvh(variation, study_instance_uid, unignore=False)[source]¶

Change an uncategorized roi name to ignored so that it won’t show up in the list of uncategorized rois, so that the user doesn’t have to evaluate its need everytime they cleanup the misc rois imported

Parameters

variation (str) – roi name
study_instance_uid (str) – the associated study instance uid
unignore (bool, optional) – if set to True, sets the variation to ‘uncategorized’

static import_db(cnx_src, cnx_dst, callback=None, force=False)[source]¶

Parameters

cnx_src (DVH_SQL) – the source DVHA DB connection
cnx_dst (DVH_SQL) – the destination DVHA DB connection
callback (callable, optional) – optional function to be called on each row insertion. Should accept table (str), current row (int), total_row_count (int) as parameters
force (bool, optional) – ignore duplicate StudyInstanceUIDs if False

initialize_database()[source]¶: Ensure that all of the latest SQL columns exist in the database

insert_data_set(data_set)[source]¶

Insert an entire data set for a plan

Parameters: data_set (dict) – a dictionary of data with table names for keys, and a list of row data for values

insert_row(table, row)[source]¶

Generic function to import data to the database

Parameters

table (str) – SQL table name
row (dict) – data returned from DICOM_Parser.get_<table>_row()

is_mrn_imported(mrn)[source]¶

Check all tables to see if MRN is used

Parameters: mrn (str) – medical record number
Returns: True if mrn exists in any table
Return type: bool

is_mrn_in_table(table_name, mrn)[source]¶

Parameters

table_name (str) – SQL table name
mrn (str) – medical record number

Returns

True if mrn exists in the mrn column of table_name

Return type

bool

is_roi_imported(roi_name, study_instance_uid)[source]¶

Check if a study is already using a specified roi name

Parameters

roi_name (str) – roi name to check
study_instance_uid (str) – restrict search to this study_instance_uid

Returns

True if roi_name is used in the DVHs table for the given study_instance_uid

Return type

bool

is_sql_table_empty(table)[source]¶

Check if specified SQL table is empty

Parameters: table (str) – SQL table
Returns: True if table is empty
Return type: bool

is_sqlite_column_datetime(table_name, column)[source]¶

Check if a sqlite column is a datetime data type

Parameters

table_name (str) – SQL table
column (str) – SQL column

Returns

True if the table_name.column store datetime data

Return type

bool

is_study_instance_uid_in_table(table_name, study_instance_uid)[source]¶

Check if a study instance uid exists in the provided table

Parameters

table_name (str) – SQL table name
study_instance_uid (str) – study instance uid

Returns

True if study_instance_uid exists in the study_instance_uid column of table_name

Return type

bool

is_uid_imported(uid)[source]¶

Check all tables to see if study instance uid is used

Parameters: uid (str) – study instance uid
Returns: True if uid exists in any table
Return type: bool

is_value_in_table(table_name, value, column)[source]¶

Check if a str value exists in a SQL table

Parameters

table_name (sr) – SQL table name
value (str) – value of interest (str only)
column – SQL table column

Returns

True if value exists in table_name.column

Return type

bool

property now¶

Get a datetime object for now

Returns: The current time reported by the database
Return type: datetime

process_value(value)[source]¶

query(table_name, return_col_str, *condition_str, **kwargs)[source]¶

A generalized query function for DVHA

Parameters

table_name (str) – DVHs’, ‘Plans’, ‘Rxs’, ‘Beams’, or ‘DICOM_Files’
return_col_str (str: str) – a csv of SQL columns to be returned
condition_str (str: str) – a condition in SQL syntax
kwargs – optional parameters order, order_by, and bokeh_cds

Returns

Returns a list of lists by default, or a dict of lists if bokeh_cds in kwargs and is true

Return type

list, dict

query_generic(query_str)[source]¶

A generic query function that executes the provided string

Parameters: query_str (str) – SQL command
Returns: Results of cursor.fetchall()
Return type: list

reinitialize_database()[source]¶: Delete all data and create all tables with latest columns

save_to_json(file_path, callback=None)[source]¶

Export SQL database to a JSON file

Parameters

file_path (str) – file_path to new JSON file
callback (callable, optional) – optional function to be called on each table insertion. Should accept table_name (str), table (int), table_count (int) as parameters

property sql_cmd_now¶

Get the SQL command for now, based on database type

Returns: SQL command for now
Return type: str

update(table_name, column, value, condition_str)[source]¶

Change the data in the database

Parameters

table_name (str) – DVHs’, ‘Plans’, ‘Rxs’, ‘Beams’, or ‘DICOM_Files’
column (str) – SQL column to be updated
value (str, float, int) – value to be set
condition_str (str) – a condition in SQL syntax

update_multicolumn(table_name, columns, values, condition_str)[source]¶

Change the data in the database

Parameters

table_name (str) – DVHs’, ‘Plans’, ‘Rxs’, ‘Beams’, or ‘DICOM_Files’
columns (list) – list of SQL column to be updated
values (list) – list value to be set
condition_str (str) – a condition in SQL syntax

vacuum()[source]¶: Call to reclaim space in the database

dvha.db.sql_connector.echo_sql_db(config=None, db_type='pgsql', group=1)[source]¶

Echo the database using stored or provided credentials

Parameters

config (dict, optional) – database login credentials
db_type (str, optional) – either ‘pgsql’ or ‘sqlite’
group (int, optional) – either group 1 or 2

Returns

True if echo is successful

Return type

bool

dvha.db.sql_connector.initialize_db()[source]¶: Initialize the database

dvha.db.sql_connector.is_file_sqlite_db(sqlite_db_file)[source]¶

Check if file is a sqlite database

Parameters: sqlite_db_file (str) – path to file to be checked
Returns: True if sqlite_db_file is a sqlite database
Return type: bool

dvha.db.sql_connector.truncate_string(input_string, character_limit)[source]¶

Used to truncate a string to ensure it may be imported into database

Parameters

input_string (str) – string to be truncated
character_limit (int) – the maximum number of allowed characters

Returns

truncated string

Return type

str

dvha.db.sql_connector.write_test(config=None, db_type='pgsql', group=1, table=None, column=None, value=None)[source]¶

Write test data to database, verify with a query

Parameters

config (dict, optional) – database login credentials
db_type (str, optional) – either ‘pgsql’ or ‘sqlite’
group (int, optional) – either group 1 or 2
table (str, optional) – SQL table
column (str, optional) – SQL column
value (str, optional) – test value

Returns

Write and Read test statuses

Return type

dict

SQL to Python¶

Query a DVHA SQL table and parse the return into a python object

class dvha.db.sql_to_python.QuerySQL(table_name, condition_str, unique=False, columns=None, group=1)[source]¶

Bases: object

Object to generically query a specified table. Each column is stored as a property of the object

For example, if you query ‘dvhs’ with condition string of “mrn = ‘some_mrn’” you can access any column name ‘some_column’ with QuerySQL.some_column which will return a list of values for ‘some_column’. All properties contain lists with the order of their values synced, unless unique=True

Parameters

table_name (str) – Beams’, ‘DVHs’, ‘Plans’, or ‘Rxs’
condition_str (str) – condition in SQL syntax
unique (bool, optional) – If True, only unique values stored
columns (list, optional) – A list of SQL column names to be included in the return. If left as None, all columns will be returned
group (int, optional) – either 1 or 2

cursor_to_list(force_date=False)[source]¶

Convert a cursor return into a list of values

Parameters: force_date (bool, optional) – Apply dateutil.parser to values
Returns: queried data
Return type: list

dvha.db.sql_to_python.get_database_tree()[source]¶

Query SQL to get all columns of each table

Returns: column data sorted by table
Return type: dict

dvha.db.sql_to_python.get_unique_list(input_list)[source]¶

Remove duplicates in list and retain order

Parameters: input_list (list) – any list of objects
Returns: input_list without duplicates
Return type: list

dvha.db¶

SQL Connector¶

SQL to Python¶

DB Updater¶

DICOM Parser¶