def select_cmd( conn: sqlite3.Connection, fields=("chr", "pos", "ref", "alt"), source="variants", filters={}, order_by=None, order_desc=True, group_by=[], having={}, # {"op":">", "value": 3 } limit=50, offset=0, **kwargs, ): """Select query Command This following VQL command: `SELECT chr,pos FROM variants WHERE pos > 3` will execute : `select_cmd(conn, ["chr", "pos"], variants", {"AND": [{"pos","=",3}]}))` Args: conn (sqlite3.Connection): sqlite3 connection fields (list, optional): list of fields filters (dict, optional): nested tree of condition source (str, optional): virtual table source order_by (list, optional): order by field name order_desc (bool, optional): Descending or Ascending Order limit (int, optional): record count offset (int, optional): record count per page Yields: variants (dict) """ query = build_full_sql_query( conn, fields=fields, source=source, filters=filters, order_by=order_by, order_desc=order_desc, limit=limit, offset=offset, group_by=group_by, having=having, **kwargs, ) LOGGER.debug("command:select_cmd:: %s", query) for i in conn.execute(query): # THIS IS INSANE... SQLITE DOESNT RETURN ALIAS NAME WITH SQUARE BRACKET.... # I HAVE TO replace [] by () and go back after... # TODO : Change VQL Syntax from [] to () would be a good alternative # @See QUERYBUILDER # See : https://stackoverflow.com/questions/41538952/issue-cursor-description-never-returns-square-bracket-in-column-name-python-2-7-sqlite3-alias yield { k.replace("(", "[").replace(")", "]"): v for k, v in dict(i).items() }
def create_cmd( conn: sqlite3.Connection, target: str, source="variants", filters=dict(), count=None, **kwargs, ): """Create a selection from the given source, filtered by filters This following VQL command: `CREATE boby FROM variants WHERE pos > 3` will execute : `create_cmd(conn, "boby", "variants", {"AND":[{"pos",">",3}]})` Args: conn (sqlite3.Connection): sqlite3 connection target (str): target selection name source (str): source selection name filters (dict): filters used to select the variants from source count (int): precomputed variant count Returns: dict: {"id": selection_id} if lines have been inserted, or empty dict in case of error """ if target is None: return {} sql_query = build_full_sql_query( conn, fields=["id"], source=source, filters=filters, limit=None, **kwargs, ) LOGGER.debug("command:create_cmd:: %s", sql_query) selection_id = sql.create_selection_from_sql(conn, sql_query, target, count=count, from_selection=False) return dict() if selection_id is None else {"id": selection_id}
def count_cmd( conn: sqlite3.Connection, fields=["chr", "pos", "ref", "alt"], source="variants", filters={}, group_by=[], having={}, **kwargs, ): """Count command This following VQL command: `COUNT FROM variants WHERE pos > 3` will execute : `count_cmd(conn, "variants", {"AND": [{"pos","=",3}]}))` Args: conn (sqlite3.Connection): sqlite3 connection source (str, optional): virtual source table filters (dict, optional): nested tree of condition Returns: dict: Count of variants wgstith "count" as a key """ # See #177: Check if fields has annotations # If an annotation field is selected, the variant count stored in the selection # table (count without taking account of annotations) is different. # This leads to a fault in the pagination hiding the latest variants if # more than 50 must be displayed. variants_fields = set( field["name"] for field in sql.get_field_by_category(conn, "variants")) if set(fields).issubset(variants_fields) and not filters and not group_by: # All fields are in variants table # Returned stored cache variant LOGGER.debug("command:count_cmd:: cached from selections table") return { "count": conn.execute( f"SELECT count FROM selections WHERE name = '{source}'"). fetchone()[0] } query = build_full_sql_query( conn, fields=fields, source=source, filters=filters, limit=None, offset=None, order_by=None, group_by=group_by, having=having, **kwargs, ) # THIS IS INSANE... SQLITE DOESNT RETURN ALIAS NAME WITH SQUARE BRACKET.... # I HAVE TO replace [] by () and go back after... # TODO : Change VQL Syntax from [] to () would be a good alternative # @See QUERYBUILDER # See : https://stackoverflow.com/questions/41538952/issue-cursor-description-never-returns-square-bracket-in-column-name-python-2-7-sqlite3-alias LOGGER.debug("command:count_cmd:: %s", query) return {"count": sql.count_query(conn, query)}
def load(self): """Start async queries to get variants and variant count Called by: - on_change_query() from the view. - sort() and setPage() by the model. See Also: :meth:`loaded` """ if self.conn is None: return self._set_loading(True) LOGGER.debug("Start loading") offset = (self.page - 1) * self.limit # Add fields from group by # self.clear() # Assume variant = [] self.total = 0 # LOGGER.debug("Page queried: %s", self.page) # Store SQL query for debugging purpose self.debug_sql = build_full_sql_query( self.conn, fields=self.fields, source=self.source, filters=self.filters, limit=self.limit, offset=offset, order_desc=self.order_desc, order_by=self.order_by, group_by=self.group_by, having=self.having, ) # Create load_func to run asynchronously: load variants load_func = functools.partial( cmd.select_cmd, fields=self.fields, source=self.source, filters=self.filters, limit=self.limit, offset=offset, order_desc=self.order_desc, order_by=self.order_by, group_by=self.group_by, having=self.having, ) # Create count_func to run asynchronously: count variants count_function = functools.partial( cmd.count_cmd, fields=self.fields, source=self.source, filters=self.filters, group_by=self.group_by, ) # Assign async functions to runnables self.variant_runnable.function = lambda conn: list(load_func(conn)) self.count_runnable.function = count_function # Assign unique ID for this run self.query_number = next(self.query_counter) self.variant_runnable.query_number = self.query_number self.count_runnable.query_number = self.query_number # Start the run LOGGER.debug("Start pools; query number %s", self.query_number) self.pool.start(self.variant_runnable) self.pool.start(self.count_runnable)