def to_update_sql(self, mass_params=None, inherits=None, *args, **kwargs): ''' Generates SQL for use with ``UPDATE {table_name} set ... where glycan_id = {id};``. Called by :meth:`update` ''' inherits = dict(inherits or {}) inherits.update(inherits) template = '''UPDATE {table_name} SET mass = {mass}, structure = "{structure}" /*rest*/ WHERE glycan_id = {id};''' ext_names = list(inherits) ext_values = ["{}".format(v) for k, v in self._collect_ext_data().items()] ext_parts = ', '.join(["{} = {}".format(name, value) for name, value in zip(ext_names, ext_values)]) if len(ext_parts) > 0: ext_parts = ", " + ext_parts template = template.replace("/*rest*/", ext_parts) values = {} values['id'] = self.id values['mass'] = self.mass(**(mass_params or {})) values['structure'] = pickle.dumps(self) values['table_name'] = self.__table_name yield template.format(**values)
def to_sql(self, id=None, mass_params=None, inherits=None): ''' Translates the :class:`GlycanRecord` instance into SQL. Parameters ---------- id: int The primary key to use, overwriting :attr:`id` if present. Optional mass_params: tuple Parameters to pass to :meth:`.mass`. The output is stored in the SQL record as the `mass` value inherits: dict Mapping of inherited column_data properties to include in the record Yields ------ str: The SQL insert statement adding this record to the database ''' inherits = dict(inherits or {}) inherits.update(inherits) template = '''INSERT INTO {table_name} (glycan_id, mass, structure /*rest*/) VALUES ({id}, {mass}, "{structure}" /*values*/);''' ext_names = ', '.join(inherits) if len(ext_names) > 0: ext_names = ', ' + ext_names ext_values = ', '.join(["{}".format(v) for k, v in self._collect_ext_data().items()]) if len(ext_values) > 0: ext_values = ', ' + ext_values if id is not None: self.id = id template = template.replace("/*rest*/", ext_names).replace("/*values*/", ext_values) values = {} values['id'] = self.id values['mass'] = self.mass(**(mass_params or {})) _bound_db = getattr(self, "_bound_db", None) self._bound_db = None values['structure'] = pickle.dumps(self) self._bound_db = _bound_db values['table_name'] = self.__table_name yield template.format(**values)
def set_metadata(self, key, value): """Set a key-value pair in the database's metadata table Parameters ---------- key : str Key naming value value : any Value to store in the metadata table """ self.execute("INSERT OR REPLACE INTO metadata (name, content) VALUES (?, ?);", (key, pickle.dumps(value))) self.commit()