def loaded_annotations(id): """ Show LOJ of variants and annotations tables. Display all annotations for every registered user. Display only annotations for the selected genome. Provide editing link only for logged user. """ db = get_db() technologies_tmp = db.execute( "SELECT technology FROM files WHERE genome_id = ?", (id, ), ).fetchall() technologies = [i[0].lower() for i in technologies_tmp] if 'illumina' in technologies: illumina = True else: illumina = False if 'pacbio' in technologies: pacbio = True else: pacbio = False if 'nanopore' in technologies: nanopore = True else: nanopore = False if 'tenx' in technologies: tenx = True else: tenx = False annotations = db.execute( ## LOJ to get data from 4 tables via respective ids: "SELECT" " a.id, a.variant_id," " a.bionano_presence, a.bionano_depth, a.bionano_spanning, a.bionano_partial," " a.illumina_presence, a.illumina_depth, a.illumina_paired_end, a.illumina_soft_clipped, a.illumina_split_read," " a.nanopore_presence, a.nanopore_depth, a.nanopore_soft_clipped, a.nanopore_split_read," " a.pacbio_presence, a.pacbio_depth, a.pacbio_soft_clipped, a.pacbio_split_read," " a.tenx_presence, a.tenx_depth, a.tenx_soft_clipped, a.tenx_split_read, a.tenx_linked_read," " a.user_id, a.comments," " g.id, g.genome," " u.username," " v.chromosome, v.sv_start, v.sv_end, v.sv_type, v.genome_id," " GROUP_CONCAT(f.technology), f.genome_id" " FROM annotations a" " LEFT OUTER JOIN variants AS v ON v.id = a.variant_id" " LEFT OUTER JOIN user AS u ON u.id = a.user_id" " LEFT OUTER JOIN genomes AS g ON g.id = v.genome_id" " LEFT OUTER JOIN files AS f ON f.genome_id = g.id" # " WHERE g.id = ?" " WHERE f.genome_id = ?" " GROUP BY v.chromosome, v.sv_start, v.sv_end" " ORDER BY v.chromosome, v.sv_start, v.sv_end asc", (id, ), ).fetchall() return render_template("annotation/loaded_annotations.html", annotations=annotations, illumina=illumina, pacbio=pacbio, nanopore=nanopore, tenx=tenx)
def get_annotation(id, check_author=True): """Get an annotation and its author by id. Checks that the id exists and optionally that the current user is the author. :param id: id of annotation to get :param check_author: require the current user to be the author :return: the annotation with author information :raise 404: if a annotation with the given id doesn't exist :raise 403: if the current user isn't the author """ annotation = (get_db().execute( "SELECT id, comments, created, user_id, variant_id," " bionano_presence, bionano_depth, bionano_spanning, bionano_partial, " " illumina_depth, illumina_paired_end, illumina_soft_clipped, illumina_split_read," " nanopore_depth, nanopore_soft_clipped, nanopore_split_read," " pacbio_depth, pacbio_soft_clipped, pacbio_split_read," " tenx_depth, tenx_soft_clipped, tenx_split_read, tenx_linked_read" " FROM annotations" " WHERE id = ?", (id, ), ).fetchone()) if annotation is None: abort(404, "Annotation id {0} doesn't exist.".format(id)) # if check_author and annotation["user_id"] != g.user["id"]: # abort(403) return annotation
def load_logged_in_user(): """If a user id is stored in the session, load the user object from the database into ``g.user``.""" user_id = session.get("user_id") if user_id is None: g.user = None else: g.user = (get_db().execute("SELECT * FROM user WHERE id = ?", (user_id, )).fetchone())
def create_annotation(id): """Create a new annotation, added to loaded annotations for all users. id is for current genome. """ if request.method == "POST": chromosome = request.form["chromosome"] sv_start = request.form["sv_start"] sv_end = request.form["sv_end"] sv_type = request.form["sv_type"] genome_id = id error = None if not chromosome or not sv_start or not sv_end or not sv_type: error = "All fields are required." if error is not None: flash(error) else: db = get_db() # INSERTs only if unique constraints met; chromosome, sv_start, sv_end, sv_type, genome_id: db.execute( "INSERT OR IGNORE INTO variants" " (chromosome, sv_start, sv_end, sv_type, genome_id)" " VALUES (?, ?, ?, ?, ?);", (chromosome, sv_start, sv_end, sv_type, genome_id)) variant_id = db.execute("SELECT last_insert_rowid()").fetchone()[0] # INSERT variant_id into annotations table only for all users but for only this variant_id: registered_user_ids = db.execute( "SELECT id FROM user ORDER BY id ASC").fetchall() for user_id in registered_user_ids: # db.execute("INSERT OR IGNORE INTO annotations (variant_id, user_id) VALUES (?,?)", (variant_id, user_id[0])) db.execute( "INSERT OR IGNORE INTO annotations" " (variant_id, user_id," " bionano_presence, bionano_depth, bionano_spanning, bionano_partial," " illumina_presence, illumina_depth, illumina_paired_end, illumina_soft_clipped, illumina_split_read," " nanopore_presence, nanopore_depth, nanopore_soft_clipped, nanopore_split_read," " pacbio_presence, pacbio_depth, pacbio_soft_clipped, pacbio_split_read," " tenx_presence, tenx_depth, tenx_soft_clipped, tenx_split_read, tenx_linked_read)" " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", (variant_id, user_id[0], "Unsure", "", "", "", "Unsure", "", "", "", "", "Unsure", "", "", "", "Unsure", "", "", "", "Unsure", "", "", "", "")) db.commit() db.close() return redirect( url_for('annotation.loaded_annotations', id=genome_id)) # Obtain the current genome_id for "go back to annotations" (/genome_id/loaded_annotations) in template: genome_id = id return render_template("annotation/create_annotation.html", genome_id=genome_id)
def get_genome(id): """Get a genome by its id. :param id: id of genome to get :return: the genome :raise 404: if a genome with the given id doesn't exist """ genome = (get_db().execute( "SELECT * FROM genomes WHERE id = ?", (id, ), ).fetchone()) if genome is None: abort(404, "Genome id {0} doesn't exist.".format(id)) return genome
def delete_genomes(id): """Delete a genome. Ensures that the genome exists. """ get_genome(id) db = get_db() db.execute("DELETE FROM genomes WHERE id = ?", (id, )) variant_id = [ i for i in db.execute("SELECT id FROM variants WHERE genome_id = ?", ( id, )).fetchall() ] db.execute("DELETE FROM variants WHERE genome_id = ?", (id, )) for i in variant_id: db.execute("DELETE FROM annotations WHERE variant_id = ?", (i[0], )) db.commit() return redirect(url_for("annotation.loaded_genomes"))
def delete(id): """Delete an annotation. Ensures that the annotation exists and that the logged in user is the author of the annotation. """ get_annotation(id) db = get_db() # Obtain genome_id to redirect to loaded_annotations: genome_id = db.execute( ## LOJ to get data from multiple tables: "SELECT" " v.genome_id" " FROM annotations a" " LEFT OUTER JOIN variants AS v ON v.id = a.variant_id").fetchone()[0] # Delete annotation by id: db.execute("DELETE FROM annotations WHERE id = ?", (id, )) db.commit() # return redirect(url_for("annotation.index")) return redirect(url_for('annotation.loaded_annotations', id=genome_id))
def login(): """Log in a registered user by adding the user id to the session.""" if request.method == "POST": username = request.form["username"] password = request.form["password"] db = get_db() error = None user = db.execute("SELECT * FROM user WHERE username = ?", (username, )).fetchone() if user is None: error = "Incorrect username." elif not check_password_hash(user["password"], password): error = "Incorrect password." if error is None: # store the user id in a new session and return to the index session.clear() session["user_id"] = user["id"] return redirect(url_for("index")) flash(error) return render_template("auth/login.html")
def register(): """Register a new user. Validates that the username is not already taken. Hashes the password for security. """ if request.method == "POST": username = request.form["username"] password = request.form["password"] db = get_db() error = None if not username: error = "Username is required." elif not password: error = "Password is required." elif (db.execute("SELECT id FROM user WHERE username = ?", (username, )).fetchone() is not None): error = "User {0} is already registered.".format(username) if error is None: # the name is available, store it in the database and go to # the login page # also create user-specific annotations table db.execute( "INSERT INTO user (username, password) VALUES (?, ?)", (username, generate_password_hash(password)), ) # This line until commit handles register -> load -> register and register -> load -> register -> register ...: # Count number rows in annotations: count = db.execute( "SELECT count(*) FROM annotations").fetchone()[0] # A user has registered and loaded before second user or beyond has registered: if count > 0: # Get first and only one user_id instance (int, e.g., 1): set_user_id = db.execute( "SELECT user_id FROM annotations").fetchone()[0] # Copy annotations contents but only for the one and only user_id found above: db.execute( "CREATE TABLE copied AS SELECT * FROM annotations WHERE user_id = ?", (set_user_id, ), ) # Obtain user_id of current logged user: user_id = db.execute("SELECT id FROM user WHERE username = ?", (username, )).fetchone()[0] # Update copied table to have current logged user_id and not one from annotations: db.execute( "UPDATE copied SET user_id = ?", (user_id, ), ) # copy copied contents to annotations sans id to autoincrement in annotations: db.execute( "INSERT INTO annotations" " (user_id,variant_id,created," " bionano_depth,bionano_spanning,bionano_partial," " illumina_depth,illumina_paired_end,illumina_soft_clipped,illumina_split_read," " nanopore_depth,nanopore_soft_clipped,nanopore_split_read," " pacbio_depth,pacbio_soft_clipped,pacbio_split_read," " tenx_depth,tenx_soft_clipped,tenx_split_read,tenx_linked_read," " comments)" " SELECT" " user_id,variant_id,created," " bionano_depth,bionano_spanning,bionano_partial," " illumina_depth,illumina_paired_end,illumina_soft_clipped,illumina_split_read," " nanopore_depth,nanopore_soft_clipped,nanopore_split_read," " pacbio_depth,pacbio_soft_clipped,pacbio_split_read," " tenx_depth,tenx_soft_clipped,tenx_split_read,tenx_linked_read," " comments" " FROM copied") # Remove copied to ensure no error on next register: db.execute("DROP TABLE copied") db.commit() return redirect(url_for("auth.login")) flash(error) return render_template("auth/register.html")
def example(): """ Example dataset loaded from /static/data/example Post submission disabled for now """ form_detect = DetectForm() db = get_db() if request.method == "POST": illumina_presence = request.form["i_choices"] if "idepth_choices" in request.form: illumina_depth = request.form["idepth_choices"] else: illumina_depth = "" if "ipaired_choices" in request.form: illumina_paired_end = request.form["ipaired_choices"] else: illumina_paired_end = "" if "isoft_choices" in request.form: illumina_soft_clipped = request.form["isoft_choices"] else: illumina_soft_clipped = "" if "isplit_choices" in request.form: illumina_split_read = request.form["isplit_choices"] else: illumina_split_read = "" comments = request.form["comments"] error = None if error is not None: flash(error) else: db = get_db() db.execute( "UPDATE example" " SET illumina_presence = ?, illumina_depth = ?, illumina_paired_end = ?, illumina_soft_clipped = ?, illumina_split_read = ?," " comments = ?", (illumina_presence, illumina_depth, illumina_paired_end, illumina_soft_clipped, illumina_split_read, comments)) db.commit() return redirect(url_for("annotation.example")) # Prepopulating annotation form: form_detect.i_choices.choices = [('Yes', 'Yes'), ('No', 'No'), ('Unsure', 'Unsure')] i_detects = db.execute( "SELECT illumina_presence FROM example").fetchone()[0] form_detect.i_choices.default = str(i_detects) form_detect.idepth_choices.choices = BooleanField(default='unchecked') idepth_detects = db.execute( "SELECT illumina_depth FROM example").fetchone()[0] form_detect.idepth_choices.default = str(idepth_detects) form_detect.ipaired_choices.choices = BooleanField(default='unchecked') ipaired_detects = db.execute( "SELECT illumina_paired_end FROM example").fetchone()[0] form_detect.ipaired_choices.default = str(ipaired_detects) form_detect.isoft_choices.choices = BooleanField(default='unchecked') isoft_detects = db.execute( "SELECT illumina_soft_clipped FROM example").fetchone()[0] form_detect.isoft_choices.default = str(isoft_detects) form_detect.isplit_choices.choices = BooleanField(default='unchecked') isplit_detects = db.execute( "SELECT illumina_split_read FROM example").fetchone()[0] form_detect.isplit_choices.default = str(isplit_detects) form_detect.process() # process choices & default example = db.execute("SELECT * FROM example").fetchall() # Processing example setup.csv: if check_setup_csv('setup.csv') == True: db = get_db() with open('setup.csv', 'r') as f: try: reader = csv.reader(f) full_setup_csv = [col for col in reader] except IOError: flash("A correctly configured setup.csv file is required.") example_data = [i for i in full_setup_csv] else: flash( "A correctly configured setup.csv file is required. Did you remove cnv_annotator/static/data/example/setup.csv?" ) return render_template("annotation/example.html", form_detect=form_detect, example=example, example_data=example_data)
def loaded_genomes(): """ Loads genome files from setup.csv Show all genomes currently loaded for logged user """ # WILL NEED TO LOWERCASE HG38 if check_setup_csv('setup.csv') == True: db = get_db() with open('setup.csv', 'r') as f: try: reader = csv.reader(f) full_setup_csv = [col for col in reader] except IOError: flash("A correctly configured setup.csv file is required.") # Obtain genome names and place into genomes table: non_header = [ line for line in full_setup_csv if not line[0].startswith('#') ] genomes = list(set([line[0] for line in non_header])) for i in genomes: db.execute( "INSERT OR IGNORE INTO genomes" " (genome)" " VALUES (?)", (i, ), ) db.commit() # Obtain files associated with each genome and place into files table: # genome_id = db.execute("SELECT id FROM genomes").fetchall() genome_and_id = db.execute("SELECT id,genome FROM genomes").fetchall() genome_and_id_dict = {item[0]: item[1] for item in genome_and_id} to_db = [] for i in non_header: to_db.append([[key, i[1], i[2], i[3], i[4], i[5], i[6]] for (key, value) in genome_and_id_dict.items() if value == i[0]][0]) for i in to_db: db.execute( "INSERT OR IGNORE INTO files" " (genome_id,bam_cram,bai_crai,reference,reference_build,bed,technology)" " VALUES (?,?,?,?,?,?,?)", (i[0], i[1], i[2], i[3], i[4], i[5], i[6].lower()), ) db.commit() # Insert BED into variants table # Obtain genome, genome_id, and bed: # genome_id_and_bed = db.execute("SELECT genome,id,bed FROM genomes").fetchall() genome_id_and_bed = db.execute( "SELECT" " g.genome, g.id," " f.bed" " FROM files f" " LEFT OUTER JOIN genomes AS g ON f.genome_id = g.id").fetchall() # Open BED and obtain 4 cols along with genome_name and genome_id for this BED: for genome, genome_id, bed in genome_id_and_bed: if os.path.exists('cnv_annotator/static/data/public/' + bed): # print(genome, genome_id, bed) try: with open('cnv_annotator/static/data/public/' + bed, 'r') as fin: dr = csv.reader(fin, delimiter='\t') to_db = [(i[0], i[1], i[2], i[3], genome_id) for i in dr] except IOError: flash(".bed file does not exist") # INSERTs only if unique constraints met; genome_id, chromosome, sv_start, sv_end, sv_type: db.executemany( "INSERT OR IGNORE INTO variants (chromosome, sv_start, sv_end, sv_type, genome_id) VALUES (?, ?, ?, ?, ?);", to_db) db.execute( "INSERT OR IGNORE INTO annotations (variant_id) SELECT id FROM variants" ) db.execute( "UPDATE OR IGNORE annotations SET user_id = ?", (g.user['id'], ), ) db.execute("DELETE FROM annotations WHERE user_id IS NULL") db.execute( "UPDATE OR IGNORE annotations" " SET bionano_presence = ?, bionano_depth = ?, bionano_spanning = ?, bionano_partial = ?," " illumina_presence = ?, illumina_depth = ?, illumina_paired_end = ?, illumina_soft_clipped = ?, illumina_split_read = ?," " nanopore_presence = ?, nanopore_depth = ?, nanopore_soft_clipped = ?, nanopore_split_read = ?," " pacbio_presence = ?, pacbio_depth = ?, pacbio_soft_clipped = ?, pacbio_split_read = ?," " tenx_presence = ?, tenx_depth = ?, tenx_soft_clipped = ?, tenx_split_read = ?, tenx_linked_read = ?", ("Unsure", "", "", "", "Unsure", "", "", "", "", "Unsure", "", "", "", "Unsure", "", "", "", "Unsure", "", "", "", "")) db.commit() ## Handles register -> register -> ... -> load: # Get all user_ids which not yet have annotations assigned to them: tmp = db.execute( "SELECT id FROM user WHERE NOT EXISTS (SELECT user_id FROM annotations WHERE annotations.user_id = user.id)" ).fetchall() # Places these ids into a list: if len(tmp) > 0: user_id_list = [i[0] for i in tmp] # Iterate over list of registered ids not having annotations and assign annotations to them: for i in user_id_list: db.execute( "CREATE TEMPORARY TABLE tmp AS SELECT * FROM annotations") db.execute("UPDATE tmp SET id = NULL") db.execute( "UPDATE tmp SET user_id = ?", (i, ), ) db.execute( "INSERT OR IGNORE INTO annotations SELECT * FROM tmp") db.execute("DROP TABLE tmp") db.commit() genomes = db.execute("SELECT * from genomes").fetchall() else: flash("A correctly configured setup.csv file is required.") return render_template("annotation/loaded_genomes.html", genomes=genomes)
def update(id): """Update an annotation if the current user is the author.""" annotation = get_annotation(id) form_detect = DetectForm() db = get_db() if request.method == "POST": # bionano_presence = request.form["b_choices"] # if "bdepth_choices" in request.form: # bionano_depth = request.form["bdepth_choices"] # else: # bionano_depth = "" # if "bmols_choices" in request.form: # bionano_molecules_spanning = request.form["bmols_choices"] # else: # bionano_molecules_spanning = "" # if "bpartial_choices" in request.form: # bionano_partial_molecules = request.form["bpartial_choices"] # else: # bionano_partial_molecules = "" if "i_choices" in request.form: illumina_presence = request.form["i_choices"] else: illumina_presence = "" if "idepth_choices" in request.form: illumina_depth = request.form["idepth_choices"] else: illumina_depth = "" if "ipaired_choices" in request.form: illumina_paired_end = request.form["ipaired_choices"] else: illumina_paired_end = "" if "isoft_choices" in request.form: illumina_soft_clipped = request.form["isoft_choices"] else: illumina_soft_clipped = "" if "isplit_choices" in request.form: illumina_split_read = request.form["isplit_choices"] else: illumina_split_read = "" if "n_choices" in request.form: nanopore_presence = request.form["n_choices"] else: nanopore_presence = "" if "ndepth_choices" in request.form: nanopore_depth = request.form["ndepth_choices"] else: nanopore_depth = "" if "nsoft_choices" in request.form: nanopore_soft_clipped = request.form["nsoft_choices"] else: nanopore_soft_clipped = "" if "nsplit_choices" in request.form: nanopore_split_read = request.form["nsplit_choices"] else: nanopore_split_read = "" if "p_choices" in request.form: pacbio_presence = request.form["p_choices"] else: pacbio_presence = "" if "pdepth_choices" in request.form: pacbio_depth = request.form["pdepth_choices"] else: pacbio_depth = "" if "psoft_choices" in request.form: pacbio_soft_clipped = request.form["psoft_choices"] else: pacbio_soft_clipped = "" if "psplit_choices" in request.form: pacbio_split_read = request.form["psplit_choices"] else: pacbio_split_read = "" if "t_choices" in request.form: tenx_presence = request.form["t_choices"] else: tenx_presence = "" if "tdepth_choices" in request.form: tenx_depth = request.form["tdepth_choices"] else: tenx_depth = "" if "tsoft_choices" in request.form: tenx_soft_clipped = request.form["tsoft_choices"] else: tenx_soft_clipped = "" if "tsplit_choices" in request.form: tenx_split_read = request.form["tsplit_choices"] else: tenx_split_read = "" if "tlinked_choices" in request.form: tenx_linked_read = request.form["tlinked_choices"] else: tenx_linked_read = "" comments = request.form["comments"] error = None # Nothing is required as of yet: # if not bionano_depth: # error = "Bionano Depth is required." if error is not None: flash(error) else: db = get_db() variant_id = db.execute( "SELECT variant_id FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] # User already annotated: db.execute( "UPDATE annotations" # " SET bionano_presence = ?, bionano_depth = ?, bionano_spanning = ?, bionano_partial = ?," " SET illumina_presence = ?, illumina_depth = ?, illumina_paired_end = ?, illumina_soft_clipped = ?, illumina_split_read = ?," " nanopore_presence = ?, nanopore_depth = ?, nanopore_soft_clipped = ?, nanopore_split_read = ?," " pacbio_presence = ?, pacbio_depth = ?, pacbio_soft_clipped = ?, pacbio_split_read = ?," " tenx_presence = ?, tenx_depth = ?, tenx_soft_clipped = ?, tenx_split_read = ?, tenx_linked_read = ?," " comments = ?" " WHERE (user_id = ? and variant_id = ? and id = ?)", # (bionano_presence, bionano_depth, bionano_molecules_spanning, bionano_partial_molecules, (illumina_presence, illumina_depth, illumina_paired_end, illumina_soft_clipped, illumina_split_read, nanopore_presence, nanopore_depth, nanopore_soft_clipped, nanopore_split_read, pacbio_presence, pacbio_depth, pacbio_soft_clipped, pacbio_split_read, tenx_presence, tenx_depth, tenx_soft_clipped, tenx_split_read, tenx_linked_read, comments, g.user["id"], variant_id, id)) db.commit() return redirect(url_for("annotation.update", id=annotation['id'])) # Obtain total number of annotations to use for "Next Annotation": total_records = [ i[0] for i in db.execute('SELECT COALESCE(MAX(id), 0) FROM annotations') ][0] # Obtain genome id via LOJ: genome_id = db.execute( "SELECT genomes.id" " FROM annotations" " LEFT JOIN variants ON" " variants.id = ?" " LEFT JOIN genomes ON" " genomes.id = variants.genome_id", (annotation['variant_id'], ), ).fetchone()[0] # Obtain Illumina BAM/CRAM and indices: technologies_check_tmp = db.execute( "SELECT technology FROM files WHERE genome_id = ?", (genome_id, ), ).fetchall() technologies_check = [i[0] for i in technologies_check_tmp] if 'illumina' in technologies_check: illumina_str = 'illumina' genome_bam_cram = db.execute( "SELECT bam_cram" " FROM files" " WHERE genome_id = ? AND technology = ?", ( genome_id, illumina_str, ), ).fetchone()[0] genome_bai_crai = db.execute( "SELECT bai_crai" " FROM files" " WHERE genome_id = ?", (genome_id, ), ).fetchone()[0] else: genome_bam_cram = 'none' genome_bai_crai = 'none' # PacBio: if 'pacbio' in technologies_check: pacbio_genome_bam_cram = db.execute( "SELECT bam_cram" " FROM files" " WHERE genome_id = ? AND technology = ?", ( genome_id, 'pacbio', ), ).fetchone()[0] pacbio_genome_bai_crai = db.execute( "SELECT bai_crai" " FROM files" " WHERE genome_id = ? AND technology = ?", ( genome_id, 'pacbio', ), ).fetchone()[0] pacbio_technology = db.execute( "SELECT technology" " FROM files" " WHERE genome_id = ? AND technology = ?", ( genome_id, 'pacbio', ), ).fetchone()[0] else: pacbio_genome_bam_cram = 'none' pacbio_genome_bai_crai = 'none' pacbio_technology = 'none' # Nanopore: if 'nanopore' in technologies_check: nanopore_genome_bam_cram = db.execute( "SELECT bam_cram" " FROM files" " WHERE genome_id = ? AND technology = ?", ( genome_id, 'nanopore', ), ).fetchone()[0] nanopore_genome_bai_crai = db.execute( "SELECT bai_crai" " FROM files" " WHERE genome_id = ? AND technology = ?", ( genome_id, 'nanopore', ), ).fetchone()[0] nanopore_technology = db.execute( "SELECT technology" " FROM files" " WHERE genome_id = ? AND technology = ?", ( genome_id, 'nanopore', ), ).fetchone()[0] else: nanopore_genome_bam_cram = 'none' nanopore_genome_bai_crai = 'none' nanopore_technology = 'none' # TenX: if 'tenx' in technologies_check: tenx_genome_bam_cram = db.execute( "SELECT bam_cram" " FROM files" " WHERE genome_id = ? AND technology = ?", ( genome_id, 'tenx', ), ).fetchone()[0] tenx_genome_bai_crai = db.execute( "SELECT bai_crai" " FROM files" " WHERE genome_id = ? AND technology = ?", ( genome_id, 'tenx', ), ).fetchone()[0] tenx_technology = db.execute( "SELECT technology" " FROM files" " WHERE genome_id = ? AND technology = ?", ( genome_id, 'tenx', ), ).fetchone()[0] else: tenx_genome_bam_cram = 'none' tenx_genome_bai_crai = 'none' tenx_technology = 'none' genome_name = db.execute( "SELECT genome" " FROM genomes" " WHERE id = ?", (genome_id, ), ).fetchone()[0] reference = db.execute( "SELECT reference" " FROM files" " WHERE genome_id = ?", (genome_id, ), ).fetchone()[0] reference_build = db.execute( "SELECT reference_build" " FROM files" " WHERE genome_id = ?", (genome_id, ), ).fetchone()[0] bed = db.execute( "SELECT bed" " FROM files" " WHERE genome_id = ?", (genome_id, ), ).fetchone()[0] technology = db.execute( "SELECT technology" " FROM files" " WHERE genome_id = ?", (genome_id, ), ).fetchone()[0] # Obtain other parameters for IGV.js localization: igv_parameters = db.execute( "SELECT variants.chromosome, variants.sv_start, variants.sv_end" " FROM annotations" " LEFT JOIN variants ON" " variants.id = ?" " LEFT JOIN genomes ON" " genomes.id = variants.genome_id", (annotation['variant_id'], ), ).fetchone() # Prepopulating annotation form: # form_detect.b_choices.choices = [('Yes', 'Yes'), ('No', 'No'), ('Unsure', 'Unsure')] # b_detects = db.execute("SELECT bionano_presence FROM annotations WHERE id = ?", (id,),).fetchone()[0] # form_detect.b_choices.default = str(b_detects) # form_detect.bdepth_choices.choices = BooleanField(default='unchecked') # bdepth_detects = db.execute("SELECT bionano_depth FROM annotations WHERE id = ?", (id,),).fetchone()[0] # form_detect.bdepth_choices.default = str(bdepth_detects) # form_detect.bmols_choices.choices = BooleanField(default='unchecked') # bmols_detects = db.execute("SELECT bionano_spanning FROM annotations WHERE id = ?", (id,),).fetchone()[0] # form_detect.bmols_choices.default = str(bmols_detects) # form_detect.bpartial_choices.choices = BooleanField(default='unchecked') # bpartial_detects = db.execute("SELECT bionano_partial FROM annotations WHERE id = ?", (id,),).fetchone()[0] # form_detect.bpartial_choices.default = str(bpartial_detects) form_detect.i_choices.choices = [('Yes', 'Yes'), ('No', 'No'), ('Unsure', 'Unsure')] i_detects = db.execute( "SELECT illumina_presence FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.i_choices.default = str(i_detects) form_detect.idepth_choices.choices = BooleanField(default='unchecked') idepth_detects = db.execute( "SELECT illumina_depth FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.idepth_choices.default = str(idepth_detects) form_detect.ipaired_choices.choices = BooleanField(default='unchecked') ipaired_detects = db.execute( "SELECT illumina_paired_end FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.ipaired_choices.default = str(ipaired_detects) form_detect.isoft_choices.choices = BooleanField(default='unchecked') isoft_detects = db.execute( "SELECT illumina_soft_clipped FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.isoft_choices.default = str(isoft_detects) form_detect.isplit_choices.choices = BooleanField(default='unchecked') isplit_detects = db.execute( "SELECT illumina_split_read FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.isplit_choices.default = str(isplit_detects) form_detect.n_choices.choices = [('Yes', 'Yes'), ('No', 'No'), ('Unsure', 'Unsure')] n_detects = db.execute( "SELECT nanopore_presence FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.n_choices.default = str(n_detects) form_detect.ndepth_choices.choices = BooleanField(default='unchecked') ndepth_detects = db.execute( "SELECT nanopore_depth FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.ndepth_choices.default = str(ndepth_detects) form_detect.nsoft_choices.choices = BooleanField(default='unchecked') nsoft_detects = db.execute( "SELECT nanopore_soft_clipped FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.nsoft_choices.default = str(nsoft_detects) form_detect.nsplit_choices.choices = BooleanField(default='unchecked') nsplit_detects = db.execute( "SELECT nanopore_split_read FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.nsplit_choices.default = str(nsplit_detects) form_detect.p_choices.choices = [('Yes', 'Yes'), ('No', 'No'), ('Unsure', 'Unsure')] p_detects = db.execute( "SELECT pacbio_presence FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.p_choices.default = str(p_detects) form_detect.pdepth_choices.choices = BooleanField(default='unchecked') pdepth_detects = db.execute( "SELECT pacbio_depth FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.pdepth_choices.default = str(pdepth_detects) form_detect.psoft_choices.choices = BooleanField(default='unchecked') psoft_detects = db.execute( "SELECT pacbio_soft_clipped FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.psoft_choices.default = str(psoft_detects) form_detect.psplit_choices.choices = BooleanField(default='unchecked') psplit_detects = db.execute( "SELECT pacbio_split_read FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.psplit_choices.default = str(psplit_detects) form_detect.t_choices.choices = [('Yes', 'Yes'), ('No', 'No'), ('Unsure', 'Unsure')] t_detects = db.execute( "SELECT tenx_presence FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.t_choices.default = str(t_detects) form_detect.tdepth_choices.choices = BooleanField(default='unchecked') tdepth_detects = db.execute( "SELECT tenx_depth FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.tdepth_choices.default = str(tdepth_detects) form_detect.tsoft_choices.choices = BooleanField(default='unchecked') tsoft_detects = db.execute( "SELECT tenx_soft_clipped FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.tsoft_choices.default = str(tsoft_detects) form_detect.tsplit_choices.choices = BooleanField(default='unchecked') tsplit_detects = db.execute( "SELECT tenx_split_read FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.tsplit_choices.default = str(tsplit_detects) form_detect.tlinked_choices.choices = BooleanField(default='unchecked') tlinked_detects = db.execute( "SELECT tenx_linked_read FROM annotations WHERE id = ?", (id, ), ).fetchone()[0] form_detect.tlinked_choices.default = str(tlinked_detects) form_detect.process() # process choices & default # Obtain techs available for this genome: technologies_tmp = db.execute( "SELECT technology FROM files WHERE genome_id = ?", (genome_id, ), ).fetchall() technologies = [i[0].lower() for i in technologies_tmp] if 'illumina' in technologies: illumina = True else: illumina = False if 'pacbio' in technologies: pacbio = True else: pacbio = False if 'nanopore' in technologies: nanopore = True else: nanopore = False if 'tenx' in technologies: tenx = True else: tenx = False return render_template("annotation/update.html", annotation=annotation, total_records=total_records, genome_id=genome_id, genome_bam_cram=genome_bam_cram, genome_bai_crai=genome_bai_crai, genome_name=genome_name, reference=reference, reference_build=reference_build, bed=bed, technology=technology, form_detect=form_detect, igv_parameters=igv_parameters, illumina=illumina, pacbio=pacbio, nanopore=nanopore, tenx=tenx, pacbio_genome_bam_cram=pacbio_genome_bam_cram, pacbio_genome_bai_crai=pacbio_genome_bai_crai, nanopore_genome_bam_cram=pacbio_genome_bam_cram, nanopore_genome_bai_crai=pacbio_genome_bai_crai, tenx_genome_bam_cram=pacbio_genome_bam_cram, tenx_genome_bai_crai=pacbio_genome_bai_crai, pacbio_technology=pacbio_technology, nanopore_technology=nanopore_technology, tenx_technology=tenx_technology)