Python DBSession.query方法代码示例

# 需要导入模块: from biorepo.model import DBSession [as 别名]
# 或者: from biorepo.model.DBSession import query [as 别名]
    def change_project_owner(self, p_id, new_owner_id, mail, key):
        '''
        Allow to change the owner of one given project (include its sample(s) and measurement(s))
        '''
        try:
            p_target = DBSession.query(Projects).filter(Projects.id == p_id).first()
            #just one lab by project, so the first is the good one
            project_lab = p_target.labs[0]
            new_owner = DBSession.query(User).filter(User.id == new_owner_id).first()
            if project_lab in new_owner.labs:
                list_samples = p_target.samples
                for s in list_samples:
                    list_meas = s.measurements
                    for m in list_meas:
                        m.user_id = new_owner.id
                        DBSession.add(m)
                        DBSession.flush()
                p_target.user_id = new_owner.id
                DBSession.add(p_target)
                DBSession.flush()
                print "Update done."

            else:
                raise Exception("The new owner is not a member of this lab project. Impossible to continue the operation.")
        except:
            print_traceback()

# 需要导入模块: from biorepo.model import DBSession [as 别名]
# 或者: from biorepo.model.DBSession import query [as 别名]
    def search_to_json(self, *args, **kw):
        #TODO : sort by column on user's click
        user_lab = session.get("current_lab", None)
        #get parameters from ajax request
        search_value = kw.get("search[value]", None)
        if search_value == '':
            search_value = None
        #word lenght > 2 to avoid DDoS in your server....
        elif search_value is not None:
            list_search_words = [x for x in search_value.split(" ") if len(x) > 2]

        draw = int(kw.get("draw", 1))
        start_point = int(kw.get("start", 0))
        data_by_page = int(kw.get("length", 50))
        stop_point = start_point + data_by_page

        if user_lab:
            lab = DBSession.query(Labs).filter(Labs.name == user_lab).first()
            measurements_total = DBSession.query(Measurements).join(Measurements.attributs).filter(and_(Attributs.lab_id == lab.id, Attributs.deprecated == False)).all()
            measurements = DBSession.query(Measurements).join(Measurements.attributs).filter(and_(Attributs.lab_id == lab.id, Attributs.deprecated == False)).distinct()[start_point:stop_point]
            if search_value is not None:
                final_request = self.search_engine(list_search_words, lab)
                #query mixed with results from all the table of interest
                paginated_request = final_request[start_point:stop_point]
                searching_tosort = [SW(meas).to_json_test() for meas in paginated_request]
                searching = sorted(searching_tosort, key=lambda k: (k['User'], k['Type']))
                return json.dumps({"draw": draw, "recordsTotal": len(measurements_total), "recordsFiltered": len(final_request), "data": searching})

            searching_tosort = [SW(meas).to_json_test() for meas in measurements]
            searching = sorted(searching_tosort, key=lambda k: (k['User'], k['Type']))

        return json.dumps({"draw": draw, "recordsTotal": len(measurements_total), "recordsFiltered": len(measurements_total), "data": searching})

# 需要导入模块: from biorepo.model import DBSession [as 别名]
# 或者: from biorepo.model.DBSession import query [as 别名]
 def get_dl_url(self, mail, key, meas_id):
     '''
     provide dl URL from measurements id
     '''
     list_ids = meas_id.split(',')
     user = DBSession.query(User).filter(User._email == mail).first()
     if user is None:
         return {'ERROR': "User " + mail + " not in BioRepo."}
     else:
         dico_urls = {}
         user_labs = user.labs
         for labo in user_labs:
             lab = DBSession.query(Labs).filter(Labs.name == labo.name).first()
             for m_id in list_ids:
                 meas = DBSession.query(Measurements).join(Measurements.attributs)\
                 .filter(and_(Attributs.lab_id == lab.id, Attributs.deprecated == False))\
                 .filter(Measurements.id == m_id).first()
                 if meas is not None:
                     list_fus = meas.fus
                     if len(list_fus)>0:
                         for f in list_fus:
                             sha1 = f.sha1
                             if meas.status_type:
                                 dico_urls[m_id] = "/biorepo/public/public_link?m_id=" + str(m_id) + "&sha1=" + str(sha1)
                             else:
                                 dico_urls["ERROR " + str(m_id)] = "This file is registered as private. BioRepo can't produce a public link."
                     else:
                         dico_urls["ERROR " + str(m_id)] = "No file attached with this measurements. The saved description is : " + str(meas.description)
                 else:
                     dico_urls["ERROR " + str(m_id)] = "This measurements id does not exist or you can access to it with your current rights."
         #TODO test and add to devdoc #API
         return dico_urls

# 需要导入模块: from biorepo.model import DBSession [as 别名]
# 或者: from biorepo.model.DBSession import query [as 别名]
    def change_bis_sample(self, sending_s, reception_s):
        '''
        Allow to move all the measurements of one sample to an other one
        and delete the sending sample after the operation.
        Usefull for the "_bis" samples in spreadsheet.
        @param principal : sending_s (sample id sending the measurements), reception_s (sample id receptioning the measurements)

        '''
        try:
            # samples queries
            from_sample = DBSession.query(Samples).filter(Samples.id == int(sending_s)).first()
            to_sample = DBSession.query(Samples).filter(Samples.id == int(reception_s)).first()
            # get the measurements lists
            from_att = from_sample.attributs
            to_att = to_sample.attributs
            # lab checking
            if from_att[0].lab_id != to_att[0].lab_id:
                raise Exception("Samples from different labs. Impossible to move these measurements.")
            # get list of measurements objects
            meas_to_move = from_sample.measurements
            meas_in_place = to_sample.measurements
            # move the measurements
            for m in meas_to_move:
                if m not in meas_in_place:
                    (to_sample.measurements).append(m)
            DBSession.delete(from_sample)
            DBSession.add(to_sample)
            DBSession.flush()
            print "---> Sample " + sending_s + " was deleted and its measurements are now into the sample " + reception_s
        except:
            print_traceback()

# 需要导入模块: from biorepo.model import DBSession [as 别名]
# 或者: from biorepo.model.DBSession import query [as 别名]
    def get_samples_from_project(self, mail, key, p_id):
        '''
        Get a JSON with all samples from a given project
        input : mail and biorepo key, p_id (project id)
        output : {"project id": [{"sample id": {"name": "my sample name", "type": "4C-seq",
        "protocole": "my sample protocole", "dynamic field": "my dynamic field, ..."}}, ...]}
        '''
        user = DBSession.query(User).filter(User._email == mail).first()
        user_lab = user.labs
        list_samples = []
        dico_final = {}
        target = DBSession.query(Projects).filter(Projects.id == p_id).first()
        if target is None:
            return {'ERROR': "This project ID does not exist."}
        lab_target = target.labs[0]
        #check if the project is owned by the user or his lab
        access_ok = False
        for l in user_lab:
            if l.id == lab_target.id:
                access_ok = True
        if access_ok:
            samples = target.samples
            if len(samples) == 0:
                return {'ERROR': 'This project id : ' + str(target.id) + ' is empty.'}
            for s in samples:
                dico_sample = {}
                dico_dynamic = {}
                sample_attributs = s.attributs
                sample_a_values = s.a_values
                for att in sample_attributs:
                    att_id = att.id
                    att_key = att.key
                    for val in sample_a_values:
                        value = val.value
                        if val.attribut_id == att_id:
                            #for the true weird checkbox
                            if value == "true":
                                dico_dynamic[att_key] = att_key
                            else:
                                dico_dynamic[att_key] = value
                #check the weird checkbox widget with "false" value
                if len(sample_attributs) != len(dico_dynamic.keys()):
                    for att in sample_attributs:
                        att_key = att.key
                        att_widget = att.widget
                        if att_key not in dico_dynamic.keys() and att_widget == "checkbox":
                            dico_dynamic[att_key] = "Not " + str(att_key)
                        elif att_key not in dico_dynamic.keys() and att_widget != "checkbox":
                            dico_dynamic[att_key] = "Not specified"

                dico_sample = {"name": s.name, "type": s.type, "protocole": s.protocole}
                dico_sample.update(dico_dynamic)
                list_samples.append({s.id: dico_sample})
            dico_final[p_id] = list_samples
            return dico_final

        else:
            return {'ERROR': "This project is not a project from your lab, you cannot access to it."}

# 需要导入模块: from biorepo.model import DBSession [as 别名]
# 或者: from biorepo.model.DBSession import query [as 别名]
    def searchlists_to_json(self, *args, **kw):
        user_lab = session.get("current_lab", None)
        if user_lab:
            lab = DBSession.query(Labs).filter(Labs.name == user_lab).first()
            one_meas = DBSession.query(Measurements).join(Measurements.attributs).filter(and_(Attributs.lab_id == lab.id, Attributs.deprecated == False)).first()
            search_grid, hidden_positions, positions_not_searchable = build_search_grid(one_meas)
            searchlists = json.dumps([hidden_positions, positions_not_searchable])

            return searchlists

# 需要导入模块: from biorepo.model import DBSession [as 别名]
# 或者: from biorepo.model.DBSession import query [as 别名]
def get_permissions(admin):
    '''
    Get the right permissions for an user.
    @param admin : True if the user is an admin.
    @type admin : a boolean.
    '''
    if admin:
        return DBSession.query(Permission).all()
    return DBSession.query(Permission).filter(Permission.name != 'admin').all()

# 需要导入模块: from biorepo.model import DBSession [as 别名]
# 或者: from biorepo.model.DBSession import query [as 别名]
    def multi_meas_delete(self, p_id, s_id, mail, key):
        '''
        deleted ALL the measurements for a given sample
        /!\ IRREVERSIBLE /!\
        '''
        try:
            project = DBSession.query(Projects).filter(Projects.id == p_id).first()
            sample = DBSession.query(Samples).filter(Samples.id == s_id).first()
            user = DBSession.query(User).filter(User._email == mail).first()
            #checking
            print "--- Check your inputs... ---"
            if project is None:
                print "Project " + str(p_id) + " not found."
            if sample is None:
                print "Sample " + str(s_id) + " not found."
            if user is None:
                print "Your mail " + mail + " is not recorded in BioRepo."

            if project.id == sample.project_id and user.id == project.user_id:
                print "--- Begin the purge... ---"
                list_meas = sample.measurements
                print "Today, " + str(len(list_meas)) + " will die..."
                print "--------------------------"
                for m in list_meas:
                    list_fus = m.fus
                    for f in list_fus:
                        #delete the file on the server only if it is not used by anyone else anymore
                        if len(f.measurements) == 1 and not (f.path).startswith(HTS_path_data()) and not (f.path).startswith(HTS_path_archive()):
                            path_fu = f.path + "/" + f.sha1
                            mail = user._email
                            mail_tmp = mail.split('@')
                            path_mail = "AT".join(mail_tmp)
                            path_symlink = f.path + "/" + path_mail + "/" + f.sha1
                            DBSession.delete(f)
                            path_symlink = f.path + "/" + path_mail + "/" + f.sha1
                            try:
                                os.remove(path_symlink)
                            except:
                                print "---- path_symlink deleted yet ----"
                                pass
                            os.remove(path_fu)
                        elif (f.path).startswith(HTS_path_data()) or (f.path).startswith(HTS_path_archive()):
                            DBSession.delete(f)
                            #TODO send back something to hts to notify that it's not into biorepo anymore
                    print str(m.name) + "(" + str(m.id) + ") ... Sorry ... PAN."
                    DBSession.delete(m)
                    DBSession.flush()
                print "--- They are all died T_T ---"

            else:
                print "It's not your project/sample. The FBI was notified. Run."
        except:
            print_traceback()
            print "Something went wrong...Please, don't cry."

# 需要导入模块: from biorepo.model import DBSession [as 别名]
# 或者: from biorepo.model.DBSession import query [as 别名]
    def get_my_lab_projects(self, mail, key):
        '''
        Get a JSON with all user's projects by lab
        input : mail an biorepo key
        output : {'lab':{'project id':{'name': "my_project_name", 'description': "my project description",
        "owner": "project owner name"}}}
        '''
        dico_lab_projects = {}
        dico_by_labs = {}
        user = DBSession.query(User).filter(User._email == mail).first()
        if user is None:
            return {'ERROR': "User " + mail + " not in BioRepo."}
        else:
            user_labs = user.labs
            if len(user_labs) == 1:
                for lab in user_labs:
                    lab_projects = lab.projects
                if isinstance(lab_projects, list):
                    for p in lab_projects:
                        u = DBSession.query(User).filter(User.id == p.user_id).first()
                        owner = u.name
                        dico_lab_projects[p.id] = {'name': p.project_name, 'description': p.description,
                                'owner': owner}
                else:
                    u = DBSession.query(User).filter(User.id == p.user_id).first()
                    owner = u.name
                    dico_lab_projects[lab_projects.id] = {'name': lab_projects.project_name, 'description': lab_projects.description,
                                'owner': owner}
                if len(lab_projects) == 0:
                    return {'ERROR': "No projects found for " + lab.name}
                dico_by_labs[lab.name] = dico_lab_projects
                return dico_by_labs

            elif len(user.labs) > 1:
                for l in user.labs:
                    lab_projects = l.projects
                    if isinstance(lab_projects, list):
                        for p in lab_projects:
                            u = DBSession.query(User).filter(User.id == p.user_id).first()
                            owner = u.name
                            dico_lab_projects[p.id] = {'name': p.project_name, 'description': p.description,
                                'owner': owner}
                    else:
                        u = DBSession.query(User).filter(User.id == p.user_id).first()
                        owner = u.name
                        dico_lab_projects[lab_projects.id] = {'name': lab_projects.project_name, 'description': lab_projects.description,
                                'owner': owner}
                    dico_by_labs[l.name] = dico_lab_projects
                    dico_lab_projects = {}
                return dico_by_labs

            else:
                return {'ERROR': "This user " + mail + " has no lab. Contact the administrator please."}

# 需要导入模块: from biorepo.model import DBSession [as 别名]
# 或者: from biorepo.model.DBSession import query [as 别名]
    def index(self, *args, **kw):
        user_lab = session.get("current_lab", None)
        user_projects = []
        u_projects = []
        u_samples = []
        u_meas = []
        u_children = []
        u_global = []

        dico_final = {}
        #TODO : admin view - watch for a dl link
        if user_lab:
            lab = DBSession.query(Labs).filter(Labs.name == user_lab).first()
            lab_users = lab.users
            for u in lab_users:
                projects = DBSession.query(Projects).filter(Projects.user_id == u.id).all()
                if len(projects) > 0:
                    for p in projects:
                        for lab in p.labs:
                            if lab.name == user_lab:
                                user_projects.append(p)
                    for proj in user_projects:
                        for sample in proj.samples:
                            if len(sample.measurements) > 0:
                                for meas in sample.measurements:
                                    if len(meas.children) == 0:
                                        u_meas.append({"name": str(meas.name) + "(" + str(meas.id) + ")"})
                                    else:
                                        for child in meas.children:
                                            u_children.append({"name": str(child.name) + "(" + str(child.id) + ")"})
                                        u_meas.append({"name": str(meas.name) + "(" + str(meas.id) + ")", "children": u_children})
                                        u_children = []
                                u_samples.append({"name": str(sample.name) + "(" + str(sample.type) + ")", "children": u_meas})
                                u_meas = []
                            else:
                                u_samples.append({"name": str(sample.name + "(" + str(sample.type) + ")")})
                        if len(proj.samples) > 0:
                            u_projects.append({"name": str(proj.project_name), "children": u_samples})
                            u_samples = []
                        else:
                            u_projects.append({"name": str(proj.project_name)})
                    u_global.append({"name": u.firstname + " " + u.name, "children": u_projects})
                    u_projects = []
                    user_projects = []
                #uncomment these 4 lines if you want to see every lab users registered in BioRepo
                #else:
                    #u_global.append({"name": u.firstname + " " + u.name})
                    #u_projects = []
                    #user_projects = []
            dico_final["name"] = user_lab
            dico_final["children"] = u_global
        return {"data": json.dumps(dico_final)}

# 需要导入模块: from biorepo.model import DBSession [as 别名]
# 或者: from biorepo.model.DBSession import query [as 别名]
    def new(self, *args, **kw):
        tmpl_context.widget = new_attribut_form
        #take the logged user
        user = handler.user.get_user_in_session(request)
        #take the logged user samples
        samples = DBSession.query(Samples).join(Projects).join(User).filter(User.id == user.id).all()
        meas = DBSession.query(Measurements).all()
        attributs = DBSession.query(Attributs).all()
        tmpl_context.samples = samples
        tmpl_context.meas = meas
        tmpl_context.attributs = attributs

        return dict(page='samples', value=kw, title='New Sample', model='Sample')

# 需要导入模块: from biorepo.model import DBSession [as 别名]
# 或者: from biorepo.model.DBSession import query [as 别名]
 def gimme_projects_plz(self, mail, *args, **kw):
     user = DBSession.query(User).filter(User._email == mail).first()
     if user is not None:
         user_id = user.id
         projects = DBSession.query(Projects).filter(Projects.user_id == user_id).all()
         dico_projects_by_user = {}
         if len(projects) != 0:
             for p in projects:
                 dico_projects_by_user[p.id] = {"project_name": p.project_name}
         else:
             dico_projects_by_user = {0: {"project_name": "No projects found into BioRepo for this user"}}
     else:
         dico_projects_by_user = {"error": str(mail) + " is not regristred in BioRepo"}
     return dico_projects_by_user

# 需要导入模块: from biorepo.model import DBSession [as 别名]
# 或者: from biorepo.model.DBSession import query [as 别名]
    def post_edit(self, *args, **kw):
        id_project = kw["IDselected"]
        project = DBSession.query(Projects).filter(Projects.id == id_project).first()
        if kw['project_name'] == '':
            flash("Bad Project : Your project must have a name", "error")
            raise redirect("./edit/" + id_project)
        project.project_name = kw["project_name"]
        project.description = kw["description"]
        samples_ids = kw.get("samples", None)
        if samples_ids is not None:
            if not isinstance(samples_ids, (list, tuple)):
                samples_ids = [int(samples_ids)]
            else:
                #from unicode to integer for comparison
                list_tmp = []
                for i in samples_ids:
                    i = int(i)
                    list_tmp.append(i)
                samples_ids = list_tmp
        else:
            samples_ids = []

        #check if sample is not deleted
        try:
            if kw["selected_samples"] != "[]":
                old_selected = [int(x) for x in kw["selected_samples"].replace("[", "").replace("]", "").split(",")]
            else:
                old_selected = []
        except:
            flash("Samples id error, please contact the administrator to report your bug", 'error')
            print "Something changed with this Turbogears version.... controllers/project.py l180 --> JSON solution is better"
            raise redirect("./")

        list_names = []
        for o in old_selected:
            if o not in samples_ids:
                sample1 = DBSession.query(Samples).filter(Samples.id == o).first()
                list_names.append(str(sample1.name))
        if len(list_names) > 0:
            flash("If you choose to delete : " + str(list_names).replace("[", "").replace("]", "") + " from the project, this sample will be removed. The sample deletion is not enabled here. Please do it directly in the sample page delete option.", 'error')
            raise redirect("./edit/" + id_project)

        list_samples = []
        for s in samples_ids:
            sample = DBSession.query(Samples).filter(Samples.id == s).first()
            list_samples.append(sample)

        project.samples = list_samples

        raise redirect("./")

# 需要导入模块: from biorepo.model import DBSession [as 别名]
# 或者: from biorepo.model.DBSession import query [as 别名]
    def drop_to_vitalit(self, mail, key, meas_ids):
        '''
        Copy-paste file(s) attached to the measurement(s) id given in input to a /scratch accessible path for the user
        '''
        list_ids = list(set(meas_ids.split(',')))
        msg = "--- DO NOT REPLY TO THIS MESSAGE PLEASE ---\n\n"
        user = DBSession.query(User).filter(and_(User._email == mail, User.key == key)).first()
        if user is None:
            return {'ERROR': "Your mail or you key is not correct."}
        labs = user.labs
        user_labs = []
        for lab in labs:
            user_labs.append(lab.name)

        if list_ids is None or len(list_ids) == 0:
            return {'ERROR': "You have to give one or several id(s) with the meas_ids key."}
        #building dico with paths and filenames
        dic_paths = {}
        for i in list_ids:
            meas = DBSession.query(Measurements).filter(Measurements.id == i).first()
            #check lab rights
            att_meas = meas.attributs[0]
            lab_to_check = DBSession.query(Labs).filter(Labs.id == att_meas.lab_id).first()
            if lab_to_check.name not in user_labs:
                msg = msg + "ERROR : This measurement (" + str(meas.id) + ") doesn't belong to your lab. You can't access to it.\n"
            #check status (public/private)
            elif not meas.status_type:
                msg = msg + "ERROR : This measurement (" + str(meas.id) + ")is not public. You can't move it from BioRepo."
            else:
                if len(meas.fus) > 0:
                    for fu in meas.fus:
                        path_fu = fu.path + "/" + fu.sha1
                        filename = fu.filename
                        dic_paths[filename] = path_fu
        #check /scratch repository
        public_path = path_dropbox(user_labs[0])
        #copy-paste the file(s)
        for k, v in dic_paths.iteritems():
            try:
                src = v
                dest = public_path + "/" + k
                copyfile(src, dest)
                msg = msg + k + " is now accessible here : " + dest + "\n"
            except:
                print ">>> COPY ERROR WITH " + k
                msg = msg + "ERROR " + k + " encountered a problem during the copy-paste process. Contact your admin for this file please.\n"
                print_traceback()
        #send mail with public path(s)
        sendMail(user._email, msg, "[BioRepo] Get your file(s) on Vital-IT /scratch")

# 需要导入模块: from biorepo.model import DBSession [as 别名]
# 或者: from biorepo.model.DBSession import query [as 别名]
def get_UCSC_link(obj_id):
    '''
    Return a HTML link to UCSC
    '''
    meas = DBSession.query(Measurements).filter(Measurements.id == obj_id).first()
    status = meas.status_type
    normal_ext = ["bed", "bedgraph", "wig"]
    #binary extension, except bam files
    binary_ext = ["bw", "bigwig", "bigbed", "bb"]
    if status and len(meas.fus) > 0:
        list_fus = meas.fus
        for x in list_fus:
            f_sha1 = x.sha1
            ext = x.extension
        #t is type. 1 == normal extension, 2 == binary extension, 3 == bam
        if ext.lower() in normal_ext:
            return'''
            <a class='action UCSC_link'  href="%s" target="_blank" title="view in UCSC" style="text-decoration:none" target="_blank"></a> ''' % (url('./public/UCSC_link', params=dict(sha1=f_sha1, meas_id=obj_id, t=1)))
        elif ext.lower() in binary_ext:
            return'''
            <a class='action UCSC_link'  href="%s" target="_blank" title="view in UCSC" style="text-decoration:none" target="_blank"></a> ''' % (url('./public/UCSC_link', params=dict(sha1=f_sha1, meas_id=obj_id, t=2)))
        elif ext.lower() == "bam":
            return'''
            <a class='action UCSC_link'  href="%s" target="_blank" title="view in UCSC" style="text-decoration:none" target="_blank"></a> ''' % (url('./public/UCSC_link', params=dict(sha1=f_sha1, meas_id=obj_id, t=3)))
    return ''