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