def query(self, table_name, return_col_str, *condition_str, **kwargs):
"""
A generalized query function for DVHA
:param table_name: 'DVHs', 'Plans', 'Rxs', 'Beams', or 'DICOM_Files'
:type table_name: str
:param return_col_str: a csv of SQL columns to be returned
:type return_col_str: str
:param condition_str: a condition in SQL syntax
:type condition_str: str
:param kwargs: optional parameters order, order_by, and bokeh_cds
:return: results of the query
kwargs:
order: specify order direction (ASC or DESC)
order_by: the column order is applied to
bokeh_cds: structure data into a format readily accepted by bokeh's ColumnDataSource.data
"""
order, order_by = None, None
if kwargs:
if 'order' in kwargs:
order = kwargs['order']
if 'order_by' in kwargs:
order_by = kwargs['order_by']
if not order:
order = 'ASC'
query = "Select %s from %s;" % (return_col_str, table_name)
if condition_str and condition_str[0]:
query = "Select %s from %s where %s;" % (
return_col_str, table_name, condition_str[0])
if order and order_by:
query = "%s Order By %s %s;" % (query[:-1], order_by, order)
try:
self.cursor.execute(query)
results = self.cursor.fetchall()
except Exception as e:
raise SQLError(str(e), query)
if 'bokeh_cds' in kwargs and kwargs['bokeh_cds']:
keys = [c.strip() for c in return_col_str.split(',')]
results = {
key: [results[r][i] for r in range(len(results))]
for i, key in enumerate(keys)
}
for key in list(results):
if 'time' in key or 'date' in key:
results[key] = [str(value) for value in results[key]]
return results
def update(self, table_name, column, value, condition_str):
"""
Change the data in the database.
:param table_name: 'DVHs', 'Plans', 'Rxs', 'Beams', or 'DICOM_Files'
:type table_name: str
:param column: SQL column to be updated
:type column: str
:param value: value to be set
:type value: str
:param condition_str: a condition in SQL syntax
:type condition_str: str
"""
try:
float(value)
value_is_numeric = True
except ValueError:
value_is_numeric = False
if '::date' in str(value):
amend_type = [
'', '::date'
][self.db_type == 'pgsql'] # sqlite3 does not support ::date
value = "'%s'%s" % (
value.strip('::date'), amend_type
) # augment value for postgresql date formatting
elif value_is_numeric:
value = str(value)
elif 'null' == str(value.lower()):
value = "NULL"
else:
value = "'%s'" % str(value) # need quotes to input a string
update = "Update %s SET %s = %s WHERE %s" % (table_name, column, value,
condition_str)
try:
self.cursor.execute(update)
self.cnx.commit()
except Exception as e:
raise SQLError(str(e), update)