def insert_table_attributes(attribute_data, **kwargs):
"""Write attributes to summary database.
Keyword arguments:
table_name (str) - name of SQLite table to write to
shot_number (int) - shot number
shot_timestamp
Arguments
*args - remaining args should be (attr, data) pairs
e.g. write_attributes_to_table('my_table', 12345, ('dens',10), ('Ti',9))
"""
# We use **kwargs because celery chaining pushes previous results to the
# first argument of successive tasks, and kwargs let us set up a partial
# where not arguments are not interchangeable (here, table_name and shot_number)
table_name = kwargs.get('table_name')
shot_number = kwargs.get('shot_number')
shot_timestamp = kwargs.get('shot_timestamp')
cursor = get_summary_cursor()
attr_str = 'shot,timestamp'
value_str = "{},'{}'".format(shot_number, shot_timestamp)
for attr in attribute_data:
attr_str += ',{}'.format(attr[0])
if attr[1] is None:
value_str += ',NULL'
else:
value_str += ',{}'.format(attr[1])
cursor.execute('INSERT OR REPLACE INTO {} ({}) VALUES ({})'.format(table_name, attr_str, value_str))
def update_table_attributes(attribute_data, **kwargs):
"""Write attributes to summary database.
Keyword arguments:
table_name (str) - name of SQLite table to write to
shot_number (int) - shot number
shot_timestamp
Arguments
*args - remaining args should be (attr, data) pairs (TODO: BUG: currently all implementations provide args=(((a, d), (a, d)),) )
e.g. write_attributes_to_table('my_table', 12345, ('dens',10), ('Ti',9))
"""
# We use **kwargs because celery chaining pushes previous results to the
# first argument of successive tasks, and kwargs let us set up a partial
# where not arguments are not interchangeable (here, table_name and shot_number)
table_name = kwargs.get("table_name")
shot_number = kwargs.get("shot_number")
shot_timestamp = kwargs.get("shot_timestamp")
cursor = get_summary_cursor()
attribute_data += (("timestamp", "'{}'".format(shot_timestamp)), )
set_str = ",".join("{}=NULL".format(a[0]) if (a[1] is None) else "{}={}".format(a[0], a[1]) for a in attribute_data)
cursor.execute("UPDATE {} SET {} WHERE shot={}".format(table_name, set_str, shot_number))
def get_max_shot(self):
"""Get the maximum shot number in the database table."""
cursor = get_summary_cursor()
cursor.execute("SELECT MAX(shot) FROM {}".format(self.table_name))
result = cursor.fetchone()[0]
return result
def insert_or_update_single_table_attribute(device_slug, shot_number, shot_timestamp, attribute_slug):
cursor = get_summary_cursor()
table_name = TABLE_NAME_TEMPLATE.format(device_slug)
if shot_exists(cursor, table_name, shot_number):
update_single_table_attribute(device_slug, shot_number, shot_timestamp, attribute_slug)
else:
insert_single_table_attribute(device_slug, shot_number, shot_timestamp, attribute_slug)
def delete_shot(self, shot_number):
"""Delete shot from summary table.
Arguments:
shot_number (int) - shot number.
"""
cursor = get_summary_cursor()
cursor.execute("DELETE FROM {} WHERE shot={}".format(self.table_name, shot_number))
def table_exists(self):
"""Check whether table exists.
Returns:
Bool: True if table exists, false if it does not.
"""
cursor = get_summary_cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='{}'".format(self.table_name))
table = cursor.fetchone()
return bool(table)
def get_attributes_from_table(self, filter_initial_attributes=False):
"""Get the attributes of the summary table.
Keyword arguments:
filter_initial_attributes (bool) - if True, then exclude attributes in INITIAL_TABLE_ATTRIBUTES
"""
cursor = get_summary_cursor()
cursor.execute("PRAGMA table_info({})".format(self.table_name))
attr_list = [a[1] for a in cursor.fetchall()]
if filter_initial_attributes:
for initial_attr in INITIAL_TABLE_ATTRIBUTES:
attr_list.remove(initial_attr[0])
return attr_list
def shot_exists(self, shot):
"""Check whether or not a shot already exists in the summary datbase.
Arguments:
shot - either an instance of h1ds.models.Shot or an integer (shot number)
Returns:
bool - True if shot exists in the summary database, False if it does not.
"""
try:
shot_number = shot.number
except AttributeError:
shot_number = shot
cursor = get_summary_cursor()
cursor.execute("SELECT shot from {} WHERE shot={}".format(self.table_name, shot_number))
return bool(cursor.fetchone())
def create_attribute(self, summary_attribute):
"""Add summary attribute to table.
Arguments:
summary_attribute: a SummaryAttribute instance or slug
This does nothing if the attribute already exists in the table
"""
try:
attr_slug = summary_attribute.slug
except AttributeError:
attr_slug = summary_attribute
if attr_slug in self.get_attributes_from_table():
return
cursor = get_summary_cursor()
cursor.execute("ALTER TABLE {} ADD COLUMN {}".format(self.table_name, attr_slug))
def create_table(self):
"""Create summary table."""
from h1ds_summary.models import SummaryAttribute
cursor = get_summary_cursor()
summary_attrs = SummaryAttribute.objects.filter(device=self.device).values_list('slug', flat=True)
table_attrs = ""
for attr_i, attr in enumerate(INITIAL_TABLE_ATTRIBUTES):
if attr_i > 0:
table_attrs += ","
table_attrs += "{} {}".format(attr[0], attr[1])
summary_attr_arg = ",".join(summary_attrs)
if summary_attr_arg:
table_attrs = ",".join([table_attrs, summary_attr_arg])
cursor.execute("CREATE TABLE %(table)s (%(attrs)s)" % {'table': self.table_name, 'attrs': table_attrs})
def do_query(self, select=['shot', 'timestamp'], where=None, as_dict=True):
"""Simple interface to SQL for given select and where statements
Keywork arguments:
select (list) - list of attributes to select
where (str) - optional SQL where statement
as_dict (bool) - if true then the responses will be returned as list of dicts: [{'attr':value, }, ]
Returns:
list of dicts (if as_dict is True)
raw response from sqlite (if as_dict is False)
"""
select_str = ",".join(select)
cursor = get_summary_cursor()
cursor.execute(
"SELECT {} FROM {} WHERE {} ORDER BY -shot".format(select_str, self.table_name, where)
)
data = cursor.fetchall()
if as_dict:
return [OrderedDict(zip(select, row)) for row in data]
else:
return data
