Unlock 30% off on Manual Testing Annual Plans this Holiday Season.

Copied to Clipboard!

How to use all_dbs method in pytest-django

Best Python code snippet using pytest-django_python

chembddb.py

Source:chembddb.py

1from flask import Flask, render_template, url_for, request,redirect2import pymysql3import os4import sys5import pandas as pd6from copy import deepcopy7from chembddb import molidentfiers8from chembddb.units import insert_unit_list,fetch_unit_list, create_unit_list, unit_converter9import json10try:11    import pybel12except:13    from openbabel import pybel14from flask import send_from_directory15import numpy as np16import time17#cur = ''18#con = ''19all_dbs = []20app = Flask(__name__)21upload_directory=os.getcwd()22app.config['UPLOAD FOLDER']=upload_directory23def post_process(sql, from_db):24    """25    Helper function to post process the results from the database26    Parameters27    ----------28    sql: str29        sql query that was created by the search function30    from_db: tuple of tuples31        results from the database32    Returns33    -------34    data: pandas dataframe35        dataframe containing the processed results ready to be displayed36    columns: list of str37        list of formatted and cleaned column names38    """39    abc = 040    if 'MW' in sql and 'property' not in sql:41        data = pd.DataFrame(list(from_db), columns=['ID','SMILES','MW'])42        columns = list(data.columns)43    elif 'MW' not in sql and 'property' not in sql:44        data = pd.DataFrame(list(from_db),columns = ['ID','SMILES'])45        columns = list(data.columns)46    else:47        data = pd.DataFrame(list(from_db), columns=['Molecule_id','SMILES','Method','Functional','Basis_set','forcefield','Property','Value'])48        data['ID_SMI']=data['Molecule_id'].astype(str)+','+data['SMILES']49        data['Property']=data['Property']+'-' +data['Method']+'('+data['Functional']+'/'+data['Basis_set']+')('+data['forcefield']+')'50        data = data[data.columns[-3:]]51        data=data.pivot_table(index='ID_SMI',columns='Property',values='Value')52        data = data.reset_index()53        data[['ID','SMILES']]=data['ID_SMI'].str.split(',',expand=True)54        columns=['ID','SMILES']55        for i in data.columns[1:-2]:56            columns.append(i)57        data=data[columns]58        columns=[c.replace('(NA/NA)','') for c in columns]59        columns=[c.replace('(na/na)','') for c in columns]60        columns=[c.replace('(NA)','') for c in columns]61        columns=[c.replace('(na)','') for c in columns]62    return data, columns63@app.route('/')64def begin():65    return redirect(url_for('connect'))66def connect_mysql(host,user,pw):67    global cur, con68    try:69        con = pymysql.connect(host = host, user=user, password = pw)70        cur = con.cursor()71        cur.execute('show databases;')72        all_dbs_tup=cur.fetchall()73        all_dbs = []74        for i in all_dbs_tup:75            if '_chembddb' in i[0]:76                m=i[0]77                all_dbs.append((m[:-9],))78        return cur,all_dbs79    except:80        return 'invalid','credentials'81    82@app.route('/connect',methods=['GET','POST'])83def connect():84    """establishes mysql connection based on the credentials provided during setup85    Parameters86    ----------87    Returns88    -------89    cur: cursor object90        pointer to the MySQL database; used to execute all SQL queries91    """ 92    global cur,all_dbs, unit_list,con93    if request.method=='POST':94        cred=request.form95        cred = cred.to_dict(flat=False)96        cur,all_dbs = connect_mysql(host = cred['host'][0], user=cred['username'][0], pw = cred['password'][0])97        if cur == 'invalid' and all_dbs == 'credentials':98            return render_template('connect.html',err_msg='Invalid Credentials. Did not connect to MySQL')99        else:100            print(all_dbs)101            if any('unit_list' in i for i in all_dbs):102                all_dbs.pop(all_dbs.index(('unit_list',)))103                unit_list = fetch_unit_list(cur)104            else:105                unit_list = create_unit_list(cur,con)106            return render_template('connect.html',success_msg='Connection Established',host=cred['host'][0],user=cred['username'][0],password=cred['password'][0],all_dbs=all_dbs)107    else:108        return render_template('connect.html')109    110@app.route('/setup',methods=['GET','POST'])111def setup(host=-1,user='',pw='',db=''):112    """113    Function to setup the database with the chembddb schema114    Parameters115    ----------116    host: str default=''117        the hostname is the domain name or server name118    user: str default=''119        the username for mysql120    pw: str default=''121        the password for mysql122    db: str default=''123        the name of the database that needs to be set up124    """125    if host != -1:126        # for python module127        b, a = connect_mysql(host=host,user=user,pw=pw)128        if b == 'invalid' and a == 'credentials':129            return 'invalid credentials'130        else:131            db = db +'_chembddb'132    elif request.method=='POST':133        # for UI134        db_details=request.form135        db_details=db_details.to_dict(flat=False)136        db=db_details['dbname'][0]+'_chembddb'137    else:138        # Default landing page for setup139        all_dbs=[]140        cur.execute('show databases;')141        all_dbs_tup=cur.fetchall()142        for i in all_dbs_tup:143            if '_chembddb' in i[0] and 'unit_list' not in i[0]:144                m=i[0]145                all_dbs.append((m[:-9],))146        return render_template('setup.html',all_dbs=all_dbs)147    all_dbs=[]148    cur.execute('show databases;')149    all_dbs_tup=cur.fetchall()150    for i in all_dbs_tup:151        if '_chembddb' in i[0] and 'unit_list' not in i[0]:152            m=i[0]153            all_dbs.append((m[:-9],))154    cur.execute('USE INFORMATION_SCHEMA')155    result=cur.execute('SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME=\'%s\''%db)156    if result == 0:157        cur.execute('CREATE DATABASE %s;'%db)158        cur.execute('USE %s;'%db)159        cur.execute('CREATE TABLE `%s`.`Molecule` (`id` INT NOT NULL AUTO_INCREMENT,`SMILES` VARCHAR(300) DEFAULT \'NONE\', `Standard_InChI` VARCHAR(400) DEFAULT \'NONE\',`Standard_InChI_Key` VARCHAR(100) DEFAULT \'NONE\',`CAS_Registry_Number` VARCHAR(200) DEFAULT \'NONE\',`IUPAC_Name` VARCHAR(400) DEFAULT \'NONE\',`Other_name` VARCHAR(1000) DEFAULT \'NONE\',`Chemical_Formula` VARCHAR(100) DEFAULT \'NONE\',`MW` FLOAT, PRIMARY KEY (`id`));'%db)160        # cur.execute('CREATE TABLE `%s`.`Credit`(`id` INT NOT NULL AUTO_INCREMENT,`DOI` VARCHAR(100) UNIQUE DEFAULT \'None\',`details` VARCHAR(100) DEFAULT \'None\',PRIMARY KEY (`id`));'%db)161        cur.execute('CREATE TABLE `%s`.`Property`(`id` INT NOT NULL AUTO_INCREMENT,`Property_str` VARCHAR(100) NOT NULL UNIQUE,`Unit` VARCHAR(100) NOT NULL,PRIMARY KEY (`id`));'%db)162        cur.execute('CREATE TABLE `%s`.`Model`(`id` INT NOT NULL AUTO_INCREMENT,`method_name` VARCHAR(100) NOT NULL UNIQUE,`options` VARCHAR(500),PRIMARY KEY (`id`));'%db)163        cur.execute('CREATE TABLE `%s`.`Functional`(`id` INT NOT NULL AUTO_INCREMENT,`name` VARCHAR(100) DEFAULT \'NONE\',PRIMARY KEY (`id`));'%db)164        cur.execute('CREATE TABLE `%s`.`Basis_set`(`id` INT NOT NULL AUTO_INCREMENT,`name` VARCHAR(100) DEFAULT \'NONE\',PRIMARY KEY (`id`));'%db)165        cur.execute('CREATE TABLE `%s`.`Forcefield`(`id` INT NOT NULL AUTO_INCREMENT,`name` VARCHAR(100) DEFAULT \'NONE\',PRIMARY KEY (`id`));'%db)166        # cur.execute('CREATE TABLE `%s`.`Topology`(`id` INT NOT NULL AUTO_INCREMENT,`geometry` VARCHAR(100) NOT NULL,`symbols` VARCHAR(100),`method` VARCHAR(100),`steps` INT,PRIMARY KEY (`id`));'%db)167        cur.execute('CREATE TABLE `%s`.`Value`(`id` INT NOT NULL AUTO_INCREMENT,`num_value` FLOAT NOT NULL,`model_id` INT NOT NULL,`property_id` INT NOT NULL,`molecule_id` INT NOT NULL,`functional_id` INT, `basis_id` INT,`forcefield_id` INT,PRIMARY KEY (`id`));'%db)168        cur.execute('CREATE TABLE `%s`.`Configuration`(`id` INT DEFAULT 0,`conf` VARCHAR(200) DEFAULT \'NONE\',`unit_dict` VARCHAR(500) DEFAULT \'NONE\',PRIMARY KEY (`id`));'%db)169        cur.execute('ALTER TABLE `%s`.`Value` ADD CONSTRAINT `Value_fk0` FOREIGN KEY (`model_id`) REFERENCES `Model`(`id`) on DELETE CASCADE;'%db)170        cur.execute('ALTER TABLE `%s`.`Value` ADD CONSTRAINT `Value_fk1` FOREIGN KEY (`property_id`) REFERENCES `Property`(`id`) on DELETE CASCADE;'%db)171        cur.execute('ALTER TABLE `%s`.`Value` ADD CONSTRAINT `Value_fk2` FOREIGN KEY (`molecule_id`) REFERENCES `Molecule`(`id`) on DELETE CASCADE;'%db)172        cur.execute('ALTER TABLE `%s`.`Value` ADD CONSTRAINT `Value_fk3` FOREIGN KEY (`functional_id`) REFERENCES `Functional`(`id`) on DELETE CASCADE;'%db)173        cur.execute('ALTER TABLE `%s`.`Value` ADD CONSTRAINT `Value_fk4` FOREIGN KEY (`basis_id`) REFERENCES `Basis_set`(`id`) on DELETE CASCADE;'%db)174        cur.execute('ALTER TABLE `%s`.`Value` ADD CONSTRAINT `Value_fk5` FOREIGN KEY (`forcefield_id`) REFERENCES `Forcefield`(`id`) on DELETE CASCADE;'%db)175        cur.execute('show databases;')176        all_dbs_tup=cur.fetchall()177        all_dbs=[]178        for i in all_dbs_tup:179            if '_chembddb' in i[0] and 'unit_list' not in i[0]:180                m=i[0]181                all_dbs.append((m[:-9],))182        if host == -1:183            # successful creation for UI184            return render_template('setup.html',dbname=db,all_dbs=all_dbs,success_msg='The database has been created.')185        else:186            # successful creation for python module187            return 'Success'188    else:189        if host == -1:190            # error handling for UI191            return render_template('setup.html',dbname=db,all_dbs=all_dbs,err_msg='Database already exists.')192        else:193            # error handling for python module194            return 'Failed! Database already exists.'195@app.route('/temp_insert',methods=['GET','POST'])196def temp_insert():197    global all_dbs, cur, data,db, mol_ids, con, snapshot, props, prop_names, unit_list, prop_type198    mi_cols=[]199    cur.execute('show databases;') 200    all_dbs_tup=cur.fetchall()201    all_dbs=[]202    for i in all_dbs_tup:203        if '_chembddb' in i[0] and 'unit_list' not in i[0]:204            m=i[0]205            all_dbs.append((m[:-9],))206    #print(request.form)207    if request.method=='POST' and 'upload_data' in request.form:208        # for UI with only the data file209        config_options=request.form210        config_options=config_options.to_dict(flat=False)211        db=config_options['dbname'][0]212        db = db+'_chembddb'213        files = request.files214        #files = files.to_dict(flat=False)215        cur.execute('USE {};'.format(db))216        cur.execute('SELECT ID,conf from Configuration')217        confs = cur.fetchall()218        conf = False219        if len(confs) > 0:220            #confs = confs[0][1].split(',')221            #confs = [confs[:7],confs[7:]]222            conf = True223        present = []224        identifiers = ['SMILES','Standard_Inchi_Key','Standard_Inchi','Chemical_Formula','IUPAC_Name','Other_name','CAS_Registry_Number']225        # to check which identifiers have entries in the database against them. The default value is 'NONE' so COUNT(DISTINCT) will give us number of unique entries for a particular column. If they are greater than 1 then the column has atleast one entry226        snapshot = []227        # IF SOMEONE MAKES A MISTAKE AND ADDS ONLY PROPERTY AND META DATA AND FORGETS TO ADD THE MOLECULES, THIS WILL NOT WORK.228        for id in identifiers:229            cur.execute('SELECT COUNT(DISTINCT '+ id +') FROM MOLECULE;')230            counts = cur.fetchall()231            #print(counts)232            if counts[0][0] > 1:233                present.append(id)234            # TODO: if value is not default and count is 1 then append to present235        if present !=[]:236            snapshot = [', '.join(p for p in present)]237            snapshot = [snapshot[0].split(',')]238            # check which properties are present in the database239            cur.execute('SELECT Property_str, Unit from Property;')240            properties = cur.fetchall()241            properties = ', '.join(i[0]+'('+i[1]+')' for i in properties)242            properties.replace(', na','')243            properties.replace('na,','')244            properties.replace('na','')245            246            properties = properties.split(',')247            snapshot.append(properties)248            cur.execute('SELECT method_name from Model;')249            methods = cur.fetchall()250            methods = ', '.join(i[0] for i in methods)251            methods = methods.replace(', na','')252            methods = methods.replace('na,','')253            methods = methods.replace('na','')254            methods = methods.split(',')255            snapshot.append(methods)256            cur.execute('SELECT name FROM Functional;')257            functionals = cur.fetchall()258            functionals = ', '.join(i[0] for i in functionals)259            functionals = functionals.replace(', na','')260            functionals = functionals.replace('na,','')261            functionals = functionals.replace('na','')262            263            functionals = functionals.split(',')264            snapshot.append(functionals)265            cur.execute('SELECT name FROM Basis_set;')266            basis = cur.fetchall()267            basis = ', '.join(i[0] for i in basis)268            basis = basis.replace(', na','')269            basis = basis.replace('na,','')270            basis = basis.replace('na','')271            272            basis = basis.split(',')273            snapshot.append(basis)274            cur.execute('SELECT name FROM Forcefield')275            forcefield = cur.fetchall()276            forcefield = ', '.join(i[0] for i in forcefield)277            forcefield = forcefield.replace(', na','')278            forcefield = forcefield.replace('na,','')279            forcefield = forcefield.replace('na','')280            forcefield = forcefield.replace(' ','')281            forcefield = forcefield.split(',')282            snapshot.append(forcefield)283        else:284            snapshot = False285        #print(snapshot)286        data_file = files['data_file']287        print(data_file.filename)288        if data_file.filename.rsplit('.',1)[1]!='csv':289            db.replace('_chembddb','')290            db=db.replace('_',' ')291            return render_template('temp_insert.html',all_dbs = all_dbs,title=db,err_msg='No data file provided or incorrect file format. (csv required)')292        else:293            data = pd.read_csv(data_file)294            cols = []295            for i in data.columns:296                cols.append(i.replace(' ','_'))297            print(data.columns)298            print(i)299            data.columns = cols300            return render_template('temp_insert.html',all_dbs=all_dbs,data_validated=True,cols = list(data.columns),conf=conf,snapshot=snapshot,snapshot_cols = ['Molecule Identifiers','Properties (Units)','Methods','Functionals','Basis Sets','Forcefields'])301    elif request.method == 'POST' and ('config' in request.form or 'use-config' in request.form):302        #print(data.head())303        config_options = request.form304        config_options=config_options.to_dict(flat=False)305        print(config_options)306            307        # loop throught he CSV file, check if the smiles value is in the table, if yes, fetch the corresponding id, same goes for property, same goes for method, if it does not exist, fetch the last id and create a new entry308        # populating and property table309        molecule_identifiers_cols = [] 310        molecule_identifiers = []311        cols = list(data.columns)312        for key in config_options.keys():313            if 'hidden' in key:314                molecule_identifiers_cols.append(config_options[key][0])315                molecule_identifiers.append(key[len('hidden_'):-3])316            else:317                continue318        #print(cols)319        print(molecule_identifiers)320        remaining_cols = list(set(cols)-set(molecule_identifiers_cols))321        mol_ids = {}322        for i in range(len(molecule_identifiers)):323            mol_ids[molecule_identifiers[i]] = molecule_identifiers_cols[i]324        if 'use-config' in config_options:325            #df = pd.read_csv(app.config['UPLOAD FOLDER']+'/'+db[:-9]+'_config.csv')326            #print(df)327            cur.execute('USE {};'.format(db))328            cur.execute('SELECT * from Configuration')329            confs = cur.fetchall()330            confs = confs[0][1].split(',')331            confs = [confs[:6],confs[6:]]332            print(confs)333            properties = []334            units = []335            methods = []336            functional = []337            basis = []338            forcefield = []339            for i in confs:340                properties.append(i[0])341                units.append(i[1])342                methods.append(i[2])343                functional.append(i[3])344                basis.append(i[4])345                forcefield.append(i[5])346            return render_template('temp_insert.html',all_dbs=all_dbs,conf_flag=True, l=len(properties), snapshot=snapshot,snapshot_cols=['Molecule Identifiers','Properties (Units)','Methods','Functionals','Basis Sets','Forcefields'],properties=properties,props = remaining_cols,prop_length=len(remaining_cols),title=db, unit=units,methods = methods, functional = functional, basis = basis, forcefield = forcefield)347        else:348            return render_template('temp_insert.html',props =remaining_cols, prop_length = len(remaining_cols), all_dbs=all_dbs, title=db,snapshot=snapshot,snapshot_cols = ['Molecule Identifiers','Properties (Units)','Methods','Functionals','Basis Sets','Forcefields'])349    elif request.method == 'POST' and ('meta-data' in request.form or 'download-submit' in request.form):350        meta_data = request.form351        meta_data = meta_data.to_dict(flat=False)352        props = []353        print(meta_data)354        for k in meta_data.keys():355            if 'prop_id' in k and 'type' not in k:356                props.append(meta_data[k][0])357        print(props)358        try:359            prop_type = meta_data['hidden_prop_id_type']360        except:361            prop_type = meta_data['prop_type_0']362        prop_names = meta_data['2_prop']363        print(prop_names)364        l = unit_list365        units = []366        for n in range(len(prop_names)):367            if prop_names[n].lower() in l.keys():368                units.append(list(l[prop_names[n].lower()].keys()))369            elif prop_type[n] == 'sol':370                units.append(list(l['solubility parameters'].keys()))371            elif prop_type[n] == 'energy':372                units.append(list(l['energy'].keys()))373            elif prop_type[n] == 'ratio':374                units.append(list(l['ratio'].keys()))375            else:376                units.append([''])377        to_drop=[]378        #print(units)379        return render_template('temp_insert.html',properties=prop_names,prop_length = len(prop_names),units = True, unit_list=units,title=db,all_dbs=all_dbs,snapshot=snapshot,snapshot_cols=['Molecule Identifiers','Properties (Units)','Methods','Functionals','Basis Sets','Forcefields'])380    381    elif request.method == 'POST' and 'meta-unit' in request.form:382        final_md = request.form383        final_md = final_md.to_dict(flat=False)384        print(unit_list)385        print(final_md)386        print(db)387        print(snapshot)388        print(mol_ids)389        print(data.columns)390        print(prop_names)391        print(props)392        unit_flag=False393        to_drop=[]394        units_from_ui = final_md['unit_id_0']395        cur.execute('Use {};'.format(db))396        #for md in final_md.keys():397        #    if 'unit_id' in md:398        #        if '(default)' in final_md[md]:399        #            unit_list.append(final_md[md][:-len('(default)')])400        #        else:401        #            unit_list.append(fi)402        403        #for p in range(len(prop_names)):404        #    if prop_names[p]+'('+final_md['unit']405        ## fetching properties from the table and checking them against the entered properties from the insert page. If they are not already existing in the table, insert them. 406        #entered_list=[]407        #cur.execute("SELECT Property_str from Property")408        #properties = cur.fetchall()409        #print(properties)410        #for prop, units in zip(props,meta_data['2_unit']):411        #    if any(prop in i for i in properties) or prop in entered_list:412        #        pass413        #    else:414        #        entered_list.append(prop)415        #        # TODO: try insert if does not exist using the SQL command416        #        print(entered_list)417        #        cur.execute("INSERT INTO Property(Property_str,Unit) VALUES(%s,%s)",[prop,units])418        #print('entered property')419        # populating the model table420        #entered_list=[]421        #for method in meta_data['2_method']:422        #    if any(method in i for i in models) or method in entered_list:423        #        pass424        #    else:425        #        entered_list.append(method)426        #        cur.execute("INSERT INTO Model(Method_name) VALUES(%s)",[method])427        #entered_list = []428        #for i in range(len(prop_names)):429        #    if '(default)' in final_md['2_unit'][i]:430        #        print(final_md['2_unit'][:-10])431        #        prop_unit = prop_names[i] + '('final_md['2_unit'][:-10]')'432        #        print(prop_unit)433        #        if prop_unit not in snapshot[1] and prop_unit not in entered_list:434        #            cur.execute('INSERT INTO Property(Property_str, Unit) VALUES(%s,%s)',[prop_names[i],final_md['2_unit'][:-10]])435        #            entered_list.append(prop_unit)436        #    else:437        #        if prop_names[i] in unit_list.keys():438        #            prop_unit = prop_names[i]+'('unit_list[prop_names[i]][0]+')'439        #            if prop_unit not in snapshot[1] and prop_unit not in entered_list:440        #                cur.execute('INSERT INTO Property(Property_str, Unit) VALUE(%s,%s)',[prop_names[i],unit_list[prop_names[i]][0]])441        #                entered_list.append(prop)442        #        else:443        #            # new unit444        # IF ',' in the unit then the unit is new (not in the unit)445        if snapshot!=False:446            # DB is not empty (there are some properties in the db)447            entered_list = []448            p = [s.split('(')[0].strip() for s in snapshot[1]] # properties in the database449            u = [s[s.index('('):s.index(')')] for s in snapshot[1]] # units in the database corresponding to that property450            for i in range(len(prop_names)):451                if (prop_names[i],units_from_ui[i]) not in entered_list:452                    print(prop_names[i])453                    print(unit_list.keys())454                    if prop_names[i] not in unit_list.keys() and prop_type[i] not in ['sol','energy']: # If property is not in our list455                        # creating unit entry for that property to add to our list and make that unit the default456                        d = {}457                        d[units_from_ui[i]+' (default)'] = 1.0458                        unit_list[prop_names[i]] = d459                        unit_flag=True460                        cur.execute('INSERT INTO Property(Property_str, unit) VALUE(%s,%s);',[prop_names[i],units_from_ui[i]])461                        # TODO: add to unit db and convert values462                    else: # if we already have the property in our list (of units)463                        if prop_names[i] not in p: # if the property is not in the database464                            if '(default)' in units_from_ui[i]:465                                u1 = units_from_ui[i]466                                cur.execute('INSERT INTO Property(Property_str,Unit) VALUES(%s,%s)',[prop_names[i],u1[:u1.index('(')-1]])467                            else:468                                if prop_type[i] == 'sol':469                                    all_units = list(unit_list['solubility parameters'].keys())470                                    def_unit = all_units[0]471                                elif prop_type[i] == 'energy':472                                    all_units = list(unit_list['energy'].keys())473                                    def_units = all_units[0]474                                else:475                                    all_units = list(unit_list[prop_names[i]].keys())476                                    def_unit = all_units[0] # fetching the default unit477                                if units_from_ui[i] not in all_units: # if we do not have this unit listed for the given property in our list478                                    conv_factor = float(units_from_ui[i].split(',')[1])479                                    units_from_ui[i] = units_from_ui[i].split(',')[0]480                                    if prop_type[i] == 'sol':481                                        unit_list['solubility parameters'][units_from_ui[i]] = conv_factor482                                    elif prop_type[i] == 'energy':483                                        unit_list['energy'][units_from_ui[i]] = conv_factor484                                    else:485                                        unit_list[prop_names[i]][units_from_ui[i]] = conv_factor486                                    unit_flag=True487                                else:488                                    # TODO: convert489                                    if prop_type[i] == 'sol':490                                        conv_factor = unit_list['solubility parameters'][units_from_ui[i]]491                                    elif prop_type[i] == 'energy':492                                        conv_factor = unit_list['energy'][units_from_ui]493                                    else:494                                        conv_factor = unit_list[prop_names[i]][units_from_ui[i]]495                                        496                                data[props[i]] = data[props[i]] * conv_factor497                                cur.execute('INSERT INTO Property(Property_str,Unit) VALUES(%s,%s)',[prop_names[i],def_unit])498                                print(def_unit)499                        else: # If the property is already in the database500                            if '(default)' not in units_from_ui[i]:501                                def_unit = list(unit_list[prop_names[i]].keys())[0] 502                                if units_from_ui[i] not in unit_list[prop_names[i]].keys():503                                    unit_list[prop_names[i][units_from_ui]] = 12 # TODO: add conv factor504                                else:505                                    # TODO: convert506                                    print('convert')507                    entered_list.append((prop_names[i],units_from_ui[i]))508            entered_list = []509            print(snapshot[2])510            m = [i.strip() for i in snapshot[2]]511            snapshot[2] = m512            print(final_md['2_method'])513            print('\nmethod\n')514            for method in final_md['2_method']:515                if method not in snapshot[2] and method not in entered_list:516                    cur.execute('INSERT INTO Model(Method_name) VALUES(%s)',[method])517                    entered_list.append(method)518            519            entered_list = []520            for func in final_md['2_functional']:521                if func not in snapshot[3] and func not in entered_list:522                    cur.execute('INSERT INTO Functional(name) VALUE(%s)',[func])523                    entered_list.append(func)524            entered_list = []525            for basis in final_md['2_basis']:526                if basis not in snapshot[4] and basis not in entered_list:527                    cur.execute('INSERT INTO Basis_set(name) VALUE(%s)',[basis])528                    entered_list.append(basis)529            entered_list = []530            for ff in final_md['2_forcefield']:531                if ff not in snapshot[5] and ff not in entered_list:532                    cur.execute('INSERT INTO Forcefield(name) VALUE(%s)',[ff])533                    entered_list.append(ff)534        else:535            #units = [u[:u.index('(')-1]]536            print('snapshot is false') # DB is empty; executed during first insert537            entered_list = []538            prop_names = [i.lower() for i in prop_names]539            for i in range(len(prop_names)):540                if (prop_names[i],units_from_ui[i]) not in entered_list:541                    if prop_names[i].lower() in unit_list.keys() or prop_type[i] in ['sol','energy']: # If we have this property in our list542                        entered_list.append((prop_names[i],units_from_ui[i]))543                        if 'default' in units_from_ui[i]: # If the unit entered by the user is the default unit for this property in our list544                            u = units_from_ui[i]545                            cur.execute('INSERT INTO Property(Property_str,Unit) VALUES(%s,%s)',[prop_names[i],u[:u.index('(')-1]])546                        else: # if the unit entered by the user is not the default unit in our list547                            if prop_type[i] == 'sol':548                                all_units = list(unit_list['solubility parameters'].keys())549                                def_unit = all_units[0]550                            elif prop_type[i] == 'energy':551                                all_units = list(unit_list['energy'].keys())552                                def_unit = all_units[0]553                            else:554                                all_units = list(unit_list[prop_names[i]].keys())555                                def_unit = all_units[0] # fetching the default unit556                            #print(list(unit_list[prop_names[i]].keys()))557                            if units_from_ui[i] not in all_units: # if we do not have this unit listed for the given property in our list558                                conv_factor = float(units_from_ui[i].split(',')[1])559                                units_from_ui[i] = units_from_ui[i].split(',')[0]560                                if prop_type[i] == 'sol':561                                    unit_list['solubility parameters'][units_from_ui[i]] = conv_factor562                                elif prop_type[i] == 'energy':563                                    unit_list['energy'][units_from_ui[i]] = conv_factor564                                else:565                                    unit_list[prop_names[i]][units_from_ui[i]] = conv_factor566                                unit_flag=True567                            else:568                                if prop_type[i] == 'sol':569                                    conv_factor = unit_list['solubility parameters'][units_from_ui[i]] 570                                elif prop_type[i] == 'energy':571                                    conv_factor = unit_list['energy'][units_from_ui[i]]572                                else:573                                    conv_factor = unit_list[prop_names[i]][units_from_ui[i]]574                            data[props[i]] = data[props[i]] * conv_factor575                            cur.execute('INSERT INTO Property(Property_str,Unit) VALUES(%s,%s)',[prop_names[i],def_unit])576                            print(def_unit)577                            print(unit_list)578                    else: # If the property is not in our list and it is not of the types energy or solubility parameters579                        print(prop_names[i])580                        conv_factor = float(units_from_ui[i].split(',')[1])581                        units_from_ui[i] = units_from_ui[i].split(',')[0]582                        unit_flag = True583                        d = {}584                        d[units_from_ui[i]+' (default)'] = 1.0585                        unit_list[prop_names[i]] = d586                        cur.execute('INSERT INTO Property(Property_str,Unit) VALUES(%s,%s)',[prop_names[i],units_from_ui[i]])587                        # TODO: add property and unit to unit_list and insert into DB588                    entered_list.append((prop_names[i],units_from_ui[i]))589            entered_list = []590            for method in final_md['2_method']:591                if method not in entered_list:592                    cur.execute('INSERT INTO Model(Method_name) VALUES(%s)',[method])593                    entered_list.append(method)594            595            entered_list = []596            for func in final_md['2_functional']:597                if func not in entered_list:598                    cur.execute('INSERT INTO Functional(name) VALUE(%s)',[func])599                    entered_list.append(func)600            entered_list = []601            for basis in final_md['2_basis']:602                if basis not in entered_list:603                    cur.execute('INSERT INTO Basis_set(name) VALUE(%s)',[basis])604                    entered_list.append(basis)605            entered_list = []606            for ff in final_md['2_forcefield']:607                if ff not in entered_list:608                    cur.execute('INSERT INTO Forcefield(name) VALUE(%s)',[ff])609                    entered_list.append(ff)610        611        ## populating the functional table612        #cur.execute("SELECT name FROM Functional")613        #functionals = cur.fetchall()614        #entered_list = []615        #for func in meta_data['2_functional']:616        #    if any(func in i for i in functionals) or func in entered_list:617        #        pass618        #    else:619        #        entered_list.append(func)620        #        cur.execute("INSERT INTO Functional(name) VALUES(%s)",[func])621        #print('entered functional')622        ## populating the basis set table623        #cur.execute("SELECT name FROM Basis_set")624        #basis_sets = cur.fetchall()625        #entered_list = []626        #for basis in meta_data['2_basis']:627        #    if any(basis in i for i in basis_sets) or basis in entered_list:628        #        pass629        #    else:630        #        entered_list.append(basis)631        #        cur.execute("INSERT INTO Basis_set(name) VALUES(%s)",[basis])632        #print('entered basis_set')633        ## populating the forcefield table634        #cur.execute("SELECT name FROM Forcefield")635        #forcefields = cur.fetchall()636        #entered_list = []637        #for ff in meta_data['2_forcefield']:638        #    if any(ff in i for i in forcefields) or ff in entered_list or ff=='None':639        #        pass640        #    else:641        #        entered_list.append(basis)642        #        cur.execute("INSERT INTO Forcefield(name) VALUES(%s)",[ff])643        644        #print('entered forcefield')645        # populating the molecule table646        cur.execute("SELECT "+ ''.join(i.lower()+',' for i in mol_ids.keys())+"MW from Molecule")647        molecules = cur.fetchall()648        new_entries=[]649        # check last id for molecule, add molecule index, melt dataframe, add property and method index using lambda and dictionary650        # check for molecule identifier (name, IUPAC, )651        pybel_identifiers = {'smiles':'smiles','standard_inchi_key':'inchikey','standard_inchi':'inchi'}652        #print(mol_ids)653        654        for mol in range(len(data)):655            mw_flag = False656            row = []657            for k,v in mol_ids.items():658                if k.lower() in pybel_identifiers.keys():659                    try:660                        m = pybel.readstring(pybel_identifiers[k.lower()],data.loc[mol][v])661                        if k.lower() == 'smiles':662                            iden = m.write('can').strip()663                            if mw_flag == False:664                                mw = m.molwt665                                mw = round(mw,3)666                                mw_flag = True667                        else:668                            iden = m.write(pybel_identifiers[k.lower()]).strip()669                            if mw_flag == False:670                                mw = m.molwt671                                mw = round(mw,3)672                                mw_flag = True673                    except:674                        db = db.replace('_',' ')675                        return render_template('temp_insert.html',title=db,all_dbs = all_dbs, err_msg='Invalid SMILES on row number {}.'.format(str(mol)))676                    row.append(iden)677                else:678                    row.append(data.loc[mol][v])679                    if mw_flag == False:680                        mw_flag = True681                        url = 'http://cactus.nci.nih.gov/chemical/structure/'682                        try:683                            url = url + data.loc[mol][v] + '/mw'684                            ans = urlopen(url).read().decode('utf8')685                        except HTTPError:686                            mw = NULL687            if mw_flag == True:688                row.append(mw)689            new_entries.append(row)690        ## temporary fix to enable for case-insensitivity in molecule-identifier691        molecules = [list(x) for x in molecules]692        new_entries = [list(x) for x in new_entries]693        #print(molecules)694        #print(new_entries)695        #for i in molecules:696        #    i[1] = i[1].lower()697        #for i in new_entries:698        #    i[1] = i[1].lower()699        molecules = tuple(tuple(x) for x in molecules)700        new_entries = tuple(tuple(x) for x in new_entries)701        required_entries = list(set(new_entries) - set(molecules))702        if len(mol_ids.keys())>1:703            mol_q = ''.join(i+',' for i in mol_ids.keys())704            vals = ''.join('%s,' for i in range(len(mol_ids)+1))705        else:706            mol_q = list(mol_ids.keys())[0] + ','707            vals = '%s,%s,'708        cur.executemany('INSERT INTO Molecule('+mol_q+'MW) VALUE('+vals[:-1]+')',required_entries)709        print('mol done')710        ### populating the credit table711        ### todo: figure out how to deal with the credit/publication712        ### cur.execute('INSERT INTO %s.Credit(DOI) VALUES(%s)'%db,' ')713        #print(list(mol_ids.values()))714        cols=[i for i in props]715        for i in list(mol_ids.values()):716            cols.append(i)717        data = data[cols]718        ##if smi_col!='':719        ##    cols.append(smi_col)720        ##if mol_identifier!='':721        ##    cols.append(mol_identifier)722        ##data = data[cols]723        # populating the values table724        cur.execute('SELECT id,Property_str from Property')725        all_props = cur.fetchall()726        prop_id = dict(map(reversed,all_props))727        cur.execute('SELECT id,Method_name from Model')728        all_models = cur.fetchall()729        model_id = dict(map(reversed,all_models))730        cur.execute('SELECT id,name from Functional')731        all_functionals = cur.fetchall()732        functional_id=dict(map(reversed,all_functionals))733        cur.execute('SELECT id,name from  Basis_set')734        all_basis = cur.fetchall()735        basis_id = dict(map(reversed,all_basis))736        cur.execute('SELECT id,name from Forcefield')737        all_ff = cur.fetchall()738        ff_id = dict(map(reversed,all_ff))739        mol_q = 'ID,'+ list(mol_ids.keys())[0]740        cur.execute("SELECT "+mol_q+" from Molecule")741        all_mols = cur.fetchall()742        molecule_id = dict(map(reversed,all_mols))743        #print(data.columns)744        #print(molecule_id)745        data['molecule_id']=data[list(mol_ids.values())[0]].apply(lambda a: molecule_id[pybel.readstring('smi',a).write('can').strip()])746        #print(data)747        748        data.drop(mol_ids.values(),1,inplace=True)749        data = data.melt('molecule_id')750        #print(prop_id)751        #print(data)752        data['property_id']=data['variable'].apply(lambda a: prop_id[prop_names[props.index(a)]]) #unable to recognize second column with same property name because it is using lambda to assign property ID753        #print(data.head())754        #print(props)755        print(model_id[final_md['2_method'][props.index(props[0])]])756        data['model_id']=data['variable'].apply(lambda a: model_id[final_md['2_method'][props.index(a)]])757        print(data)758        data['functional_id']=data['variable'].apply(lambda a: functional_id[final_md['2_functional'][props.index(a)]])759        data['basis_id']=data['variable'].apply(lambda a: basis_id[final_md['2_basis'][props.index(a)]])760        data['ff_id']=data['variable'].apply(lambda a: ff_id[final_md['2_forcefield'][props.index(a)]])761        data.drop('variable',1,inplace=True)762        to_drop = []763        id = tuple(data['molecule_id'])764        #print(data)765        cur.execute('select * from Value where molecule_id in {}'.format(str(id)))766        vals = cur.fetchall()767        vals = [list(x) for x in vals]768        vals = pd.DataFrame(vals, columns=['id','value','model_id', 'property_id','molecule_id', 'functional_id','basis_id','ff_id'])769        check_data = data.drop('value',1)770        vals = vals[check_data.columns]771        vals = [list(vals.loc[x]) for x in range(len(vals))]772        for i in range(len(data)):773            if list(check_data.loc[i]) in vals:774                to_drop.append(i)775        data.drop(to_drop,0,inplace=True)776        #print(data)777        if len(data) == 0:778            return render_template('temp_insert.html',title=db,all_dbs=all_dbs,err_msg='Duplicate entries for all molecules exist.')779        else:780            cur.executemany('INSERT INTO Value(molecule_id,num_value,property_id,model_id,functional_id,basis_id,forcefield_id) VALUES(%s,%s,%s,%s,%s,%s,%s)',data.values.tolist())781        db = db.replace('_chembddb','')782        db = db.replace('_',' ')783        if 'download-submit' in final_md:784            df = pd.DataFrame()785            df['Properties'] = [i[0] for i in props]786            df['Units'] = final_md['2_unit']787            df['Method'] = final_md['2_method']788            df['Functional'] = final_md['2_functional']789            df['Basis_set'] = final_md['2_basis']790            df['Forcefield'] = final_md['2_forcefield']791            df['conf'] = df[df.columns].apply(lambda x: ','.join(x), axis = 1)792            confs = ','.join(r for r in df['conf'])793            print(confs)794            cur.execute('INSERT INTO Configuration(ID, conf) VALUES(%s,%s)',[str(0),confs])795        if unit_flag == 'True':796            cur.execute('USE unit_list_chembddb;')797            cur.execute('INSERT INTO Main(id,unit_str) VALUE(1,%s);',[unit_list])798        con.commit()799        if to_drop!=[]:800            all_dbs.append(db)801            return render_template('temp_insert.html',title=db,all_dbs=all_dbs,init='True',err_msg='A few molecules were not entered due to duplicate entries',success_msg='The database has been successfully populated')802        else:803            return render_template('temp_insert.html',title=db,all_dbs=all_dbs,init='True',success_msg='The database has been successfully populated')804    else:805        # default landing page806        return render_template('temp_insert.html',all_dbs=all_dbs,init='True',snapshot='')807@app.route('/search',methods=['GET','POST'])808def search():809    #cur.execute('show databases;') 810    #all_dbs_tup=cur.fetchall()811    #all_dbs=[]812    #for i in all_dbs_tup:813    #    if '_chembddb' in i[0]:814    #        m=i[0]815    #        all_dbs.append((m[:-9],))816    global all_dbs817    return render_template('search.html',all_dbs=all_dbs)818@app.route('/search_db<db>',methods=['GET','POST'])819def search_db(db):820    #global cur,con821    all_dbs=[]822    cur.execute('show databases;')823    all_dbs_tup=cur.fetchall()824    #print(all_dbs_tup)825    for i in all_dbs_tup:826        if '_chembddb' in i[0] and 'unit_list' not in i[0]:827            m=i[0]828            all_dbs.append((m[:-9],))829    db=db[1:-1]830    db=db+'_chembddb'831    #cur.execute('USE INFORMATION_SCHEMA')832    #result=cur.execute('SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME=\'%s\''%db)833    #if result == 0:834        #error_message='Database does not exist'835        #return render_template('Search.html',all_dbs=all_dbs,err_msg=error_message)836    cur.execute('USE %s'%db)837    db = db.replace('_chembddb','')838    cur.execute('Select * from Property')839    properties=cur.fetchall()840    #print(properties)841    cur.execute('Select id,method_name from Model')842    results=cur.fetchall()843    cur.execute("Select * from Functional")844    functionals=cur.fetchall()845    cur.execute("Select * from basis_set")846    basis_sets=cur.fetchall()847    cur.execute("Select * from forcefield")848    forcefields=cur.fetchall()849    methods=[]850    # search_results useful only in case of smiles_search because the same results are used ... single db call851    global search_results852    global sql853    global ini854    global fin855    global n_res856    global noprev857    global nonext858    global keys859    global multiprop860    multiprop = False861    for i in results:862        methods.append(i[1])863    if request.method == 'POST' and 'search-query' in request.form:864        # This section is to create the query based on the options provided by the user865        from_form = request.form866        from_form = from_form.to_dict(flat=False)867        keys=[i for i in from_form if '_id' in i]868        min_max_err=False869        min_max_prop=[]870        props=[]871        p = []872        # tuple of tuples to list of tuples873        for pr in properties:874            p.append(list(pr))875        properties = p876        if len(keys)>0:877            sql='select value.molecule_id,molecule.SMILES,model.method_name,functional.name,basis_set.name,forcefield.name,Property.Property_str, value.num_value from molecule inner join Value on molecule.id=value.molecule_id inner join property on property.id=value.property_id inner join model on model.id=value.model_id inner join functional on functional.id=value.functional_id inner join basis_set on basis_set.id = value.basis_id inner join forcefield on forcefield.id=value.forcefield_id where '878            print(from_form)879            for k in keys:880                prop_id=int(from_form[k][0])881                props.append(prop_id)882                from_val=float(from_form[k[:-3]+'_from_val'][0])883                to_val=float(from_form[k[:-3]+'_to_val'][0])884                print(prop_id)885                properties[prop_id-1].append(from_val)886                properties[prop_id-1].append(to_val)887                if from_val > to_val:888                    min_max_err=True889                sql = sql[:sql.rfind('where')+6] + 'molecule_id in (select molecule_id from value where value.property_id={0} and value.num_value>{1} and value.num_value<{2}) and '.format(prop_id,from_val,to_val) + sql[sql.rfind('where')+6:]890            if len(keys)!=0:891                sql=sql[:-5]892            valsid=' and value.property_id in '893            for i in range(len(props)):894                if i > 0:895                    valsid = valsid + ',' + str(props[i])896                else:897                    valsid = valsid + '(' + str(props[i])898            valsid = valsid +')'899            sql = sql +valsid900        else:901            sql = 'select id, SMILES, MW from Molecule where '902        MW_to = None903        if 'MW' in from_form:904            from_val=float(from_form['MW_from_val'][0])905            to_val=float(from_form['MW_to_val'][0])906            MW_from = from_val907            MW_to = to_val908            if from_val > to_val:909                min_max_err=True910            if len(keys)!=0:911                sql=sql+" and molecule.MW > {} and molecule.MW < {} ".format(float(from_form['MW_from_val'][0]),float(from_form['MW_to_val'][0]))912            else:913                sql="select id,SMILES,MW from Molecule where Molecule.MW > {} and Molecule.MW < {} ".format(float(from_form['MW_from_val'][0]),float(from_form['MW_to_val'][0]))914                keys.append('MW')915        if 'smiles_search' in from_form:916            if len(keys)==0:917                sql = 'select id, SMILES from Molecule'918        if 'method' in from_form:919            met_id=0920            for m in results:921                if m[1]==from_form['method_name'][0]:922                    met_id=m[0]923            if len(keys)==0:924                sql=sql+" Value.model_id = {}".format(met_id)925            else:926                sql=sql+" and Value.model_id ={}".format(met_id)927        if 'func' in from_form:928            if len(keys)==0:929                sql=sql+' Value.functional_id={}'.format(from_form['functional_name'][0])930            else:931                sql=sql+' and Value.functional_id={}'.format(from_form['functional_name'][0])932        933        if 'basis' in from_form:934            if len(keys)==0:935                sql=sql+' Value.basis_id={}'.format(from_form['basis_set'][0])936            else:937                sql=sql+' and Value.basis_id={}'.format(from_form['basis_set'][0])938        939        if 'ff' in from_form:940            if len(keys)==0:941                sql=sql+' Value.forcefield_id={}'.format(from_form['forcefield'][0])942            else:943                sql=sql+' and Value.forcefield_id={}'.format(from_form['forcefield'][0])944        945        global counts946        counts_q = 'select count(*) '+sql[sql.find('from'):] +';'947        # because smiles_search will get all results from the db because substructure matching is required948        if ('property' not in sql and 'MW' not in sql) or len(keys)>1:949            counts = -1950        else:951            sql = sql + 'limit 50'952            cur.execute(counts_q)953            counts = cur.fetchall()954            counts = counts[0][0]955        sql=sql+';'956        # query creation ends here957        temp_col=[]              958        temp_met=[]959        if counts == 0:960            if min_max_err==True:961                n_res = 'Min value entered is > Max value entered for one of the fields above.' 962                columns=''963            else:               964                n_res = 'Number of results='+ str(counts)+'. No such candidates exist in your database'965                columns=''966            if 'MW' in from_form:967                return render_template('search_db.html',MW_from=MW_from, MW_to=MW_to,properties=properties,columns=columns,methods=methods,n_res=n_res,basis=basis_sets,functionals=functionals,forcefields=forcefields,all_dbs=all_dbs,title=db)968            else:969                return render_template('search_db.html',properties=properties,columns=columns,methods=methods,n_res=n_res,basis=basis_sets,functionals=functionals,forcefields=forcefields,all_dbs=all_dbs,title=db)970        else:971            # executing the query972            cur.execute(sql)973            data1=cur.fetchall()974            data, columns = post_process(sql, data1)975            # the following section is just to format the column headers that appear on the html page976            for c in columns:977                if '-' in c:978                    temp_col.append(c.split('-')[0])979                    if len(c.split('-'))>2:980                        temp_met.append(c.split('-')[1]+'-'+c.split('-')[2])981                    else:982                        temp_met.append(c.split('-')[1])983                else:984                    if 'MW' in c:985                        temp_met.append('pybel')986                    else:987                        temp_met.append('')988                    temp_col.append(c)989            # substructure matching using pybel990            try:991                smi_val = None992                if 'smiles_search' in from_form:993                    smarts = pybel.Smarts(from_form['smiles'][0])994                    smi_val = smarts995                    for i in range(len(data)):996                        mol = pybel.readstring("smi",data.loc[i]['SMILES'])997                        smarts.obsmarts.Match(mol.OBMol)998                        if len(smarts.findall(mol))==0:999                            data.drop(i,0,inplace=True)1000                    if len(data)==0:1001                        n_res='Number of results='+ str(len(data))+'\nNo such candidates exist in your database'1002                    else:1003                        n_res=len(data)1004                        counts = n_res1005                    if n_res>50:1006                        search_results = data1007                        search_results.columns = data.columns1008                        data = data[:51]1009                elif len(keys)>1:1010                    if len(data)==0:1011                        n_res='Number of results='+ str(len(data))+'\nNo such candidates exist in your database'1012                    else:1013                        n_res=len(data)1014                        counts = n_res1015                    if n_res>50:1016                        search_results = data1017                        search_results.columns = data.columns1018                        data = data[:51]1019                else:1020                    n_res = counts1021            except:1022                n_res='Invalid Smarts entered'1023                data=pd.DataFrame()1024            1025            desc=['','']1026            columns =[]1027            # creating tuple of tuples for column headers (required for html page)1028            for i in range(len(temp_met)):1029                columns.append((temp_col[i],temp_met[i]))1030            # calculating statistics for each page1031            for i in data.columns[2:]:1032                desc.append('mean={}, std={}, min={}, max={}'.format(data[i].describe()['mean'].round(2),data[i].describe()['std'].round(2),data[i].describe()['min'].round(2),data[i].describe()['max'].round(2)))1033            data = tuple(data.itertuples(index=False,name=None))1034            if len(columns) == 2:1035                to_order = False1036            else:1037                to_order = True1038            noprev=True1039            db = db.replace('_chembddb','')1040            ini=01041            if type(n_res) != str and n_res < 50:1042                fin = n_res1043                nonext=True1044            else:1045                nonext=False1046                fin = 501047            if 'MW' in from_form:1048                return render_template('search_db.html',ini=ini,fin=fin, MW_from=MW_from, MW_to=MW_to,data = data,properties=properties,columns=columns,temp_met=temp_met,methods=methods,n_res=n_res,basis=basis_sets,functionals=functionals,to_order = to_order,forcefields=forcefields,all_dbs=all_dbs,title=db,desc=desc,noprev=noprev,nonext=nonext)1049            else:1050                return render_template('search_db.html',ini=0,fin=fin,data = data,properties=properties,columns=columns,temp_met=temp_met,methods=methods,n_res=n_res,basis=basis_sets,functionals=functionals,forcefields=forcefields,to_order=to_order, all_dbs=all_dbs,title=db,desc=desc,noprev=noprev,nonext=nonext)1051    elif 'next-50' in request.form:1052        nonext=False1053        from_form = request.form1054        from_form = from_form.to_dict(flat=False)1055        n_res_done = 01056        if ' offset' in sql:1057            # checking the offset in the previous sql query, n_res_done tells us how many results have been displayed already1058            n_res_done = int(sql[sql.rfind('offset')+7:-1])1059            sql = sql[:sql.rfind('offset')+6] + ' '+str(n_res_done + 50) +';'1060            n_res_done = n_res_done + 501061        else:1062            n_res_done = 501063            sql = sql[:-1]+' offset '+str(n_res_done)+';'1064        if ('property' not in sql and 'MW' not in sql) or len(keys)>1:1065            data = search_results[n_res_done:n_res_done+51]1066            columns = list(data.columns)1067            if len(keys)==0:1068                to_order = False1069            else:1070                columns=[c.replace('(NA/NA)','') for c in columns]1071                columns=[c.replace('(na/na)','') for c in columns]1072                columns=[c.replace('(NA)','') for c in columns]1073                columns=[c.replace('(na)','') for c in columns]1074                to_order = True1075        else:   1076            to_order = True         1077            cur.execute(sql)1078            data1=cur.fetchall() 1079            data, columns = post_process(sql, data1)1080        temp_col=[]1081        temp_met=[]1082        for c in columns:1083            if '-' in c:1084                temp_col.append(c.split('-')[0])1085                if len(c.split('-'))>2:1086                    temp_met.append(c.split('-')[1]+'-'+c.split('-')[2])1087                else:1088                    temp_met.append(c.split('-')[1])1089            else:1090                temp_col.append(c)1091                if 'MW' in c:1092                    temp_met.append('pybel')1093                else:1094                    temp_met.append('')1095        desc=['','']1096        columns =[]1097        ini = n_res_done1098        if (counts - n_res_done) < 50: 1099            fin = counts1100            nonext = True1101        else:1102            nonext = False1103            fin = n_res_done + 50                1104        noprev=False1105        if temp_met!=[]:1106            for i in range(len(temp_met)):1107                columns.append((temp_col[i],temp_met[i]))1108            for i in data.columns[2:]:1109                desc.append('mean={}, std={}, min={}, max={}'.format(data[i].describe()['mean'].round(2),data[i].describe()['std'].round(2),data[i].describe()['min'].round(2),data[i].describe()['max'].round(2)))1110        if 'order by' in sql:1111            if 'DESC' in sql:        1112                data=data.sort_values(by=data.columns[-1],ascending=False)1113            else:1114                data=data.sort_values(by=data.columns[-1])1115        data = tuple(data.itertuples(index=False,name=None))1116        if 'MW' in sql and 'value.property_id=' not in sql:1117            return render_template('search_db.html',ini=ini,fin=fin, to_order=to_order,noprev=False,nonext=nonext,data = data,properties=properties,columns=columns,temp_met=temp_met,methods=methods,n_res=n_res,basis=basis_sets,functionals=functionals,forcefields=forcefields,all_dbs=all_dbs,title=db,desc=desc)1118        else:1119            return render_template('search_db.html',ini=ini,fin=fin,to_order=to_order,nonext=nonext,noprev=False,data = data,properties=properties,columns=columns,temp_met=temp_met,methods=methods,n_res=n_res,basis=basis_sets,functionals=functionals,forcefields=forcefields,all_dbs=all_dbs,title=db,desc=desc)1120    elif 'prev-50' in request.form:1121        noprev=False1122        from_form = request.form1123        from_form = from_form.to_dict(flat=False)1124        n_res_done = 01125        if ' offset' in sql:1126            # checking the offset in the previous sql query, this number tells us how many results have been displayed already1127            n_res_done = int(sql[sql.rfind('offset')+7:-1]) - 501128            sql = sql[:sql.rfind('offset')+6] + ' '+str(n_res_done) +';'1129            noprev = False1130        if n_res_done ==0:1131            noprev = True1132            nonext = False1133        if ('property' not in sql and 'MW' not in sql) or len(keys)>1:1134            data = search_results[n_res_done:n_res_done+51]1135            columns = list(data.columns)1136            if len(keys)==0:1137                to_order = False1138            else:1139                columns=[c.replace('(NA/NA)','') for c in columns]1140                columns=[c.replace('(na/na)','') for c in columns]1141                columns=[c.replace('(NA)','') for c in columns]1142                columns=[c.replace('(na)','') for c in columns]1143                to_order = True1144        else:1145            cur.execute(sql)1146            data1=cur.fetchall()1147            data, columns = post_process(sql, data1)1148            to_order = True1149        temp_col=[]1150        temp_met=[]1151        for c in columns:1152            if '-' in c:1153                temp_col.append(c.split('-')[0])1154                if len(c.split('-'))>2:1155                    temp_met.append(c.split('-')[1]+'-'+c.split('-')[2])1156                else:1157                    temp_met.append(c.split('-')[1])1158            else:1159                temp_col.append(c)1160                if 'MW' in c:1161                    temp_met.append('pybel')1162                else:1163                    temp_met.append('')1164        desc=['','']1165        columns =[]1166        fin = n_res_done + 50     1167        if temp_met!=[]:1168            for i in range(len(temp_met)):1169                columns.append((temp_col[i],temp_met[i]))1170            for i in data.columns[2:]:1171                desc.append('mean={}, std={}, min={}, max={}'.format(data[i].describe()['mean'].round(2),data[i].describe()['std'].round(2),data[i].describe()['min'].round(2),data[i].describe()['max'].round(2)))1172        1173        if 'order by' in sql:1174            if 'DESC' in sql:        1175                data=data.sort_values(by=data.columns[-1],ascending=False)1176            else:1177                data=data.sort_values(by=data.columns[-1])1178        data = tuple(data.itertuples(index=False,name=None))1179        ini = n_res_done1180        if 'MW' in sql and 'value.property_id=' not in sql:1181            return render_template('search_db.html',ini=n_res_done,fin=fin,to_order=to_order, noprev = noprev, nonext=False,data = data,properties=properties,columns=columns,temp_met=temp_met,methods=methods,n_res=n_res,basis=basis_sets,functionals=functionals,forcefields=forcefields,all_dbs=all_dbs,title=db,desc=desc)1182        else:1183            return render_template('search_db.html',ini=n_res_done,fin=fin, to_order=to_order, noprev = noprev,nonext=False,data = data,properties=properties,columns=columns,temp_met=temp_met,methods=methods,n_res=n_res,basis=basis_sets,functionals=functionals,forcefields=forcefields,all_dbs=all_dbs,title=db,desc=desc)        1184    elif 'download_csv' in request.form or 'download_json' in request.form:1185        from_form = request.form1186        from_form = from_form.to_dict(flat=False)1187        desc=['','']1188        ## re-executing query to get all results1189        if 'MW' not in sql and 'property' not in sql:1190            data = search_results1191            columns = search_results.columns1192            to_order = False1193        else:1194            if 'MW' in sql and 'property' not in sql:1195                sql = sql[:sql.rindex('limit')]+';'1196            else:1197                sql = sql[:sql.rindex(')')+1]+';'                   1198            cur.execute(sql)        1199            all_results = cur.fetchall()1200            data, columns = post_process(sql, all_results)1201            to_order = True1202        1203        if 'download_json' in request.form:1204            import json1205            data.to_json('results.json')1206            msg='Results have been downloaded as results.json'1207        else:1208            print(data)1209            data.to_csv('results.csv',index=None)1210            msg='Results have been downloaded as results.csv'1211        ## results that go to the html are still limited to 501212        for i in data.columns[2:]:1213            desc.append('mean={}, std={}, min={}, max={}'.format(data[i].describe()['mean'].round(2),data[i].describe()['std'].round(2),data[i].describe()['min'].round(2),data[i].describe()['max'].round(2)))1214        # search_results.to_csv('results.csv',index=None)1215        1216        columns=[]1217        for i in data.columns:1218            if '-' not in i:1219                if 'MW' in i:1220                    columns.append((i,'pybel'))1221                else:1222                    columns.append((i,''))1223            else:1224                if len(i.split('-')) > 2:1225                    columns.append((i.split('-')[0],i.split('-')[1]+'-'+i.split('-')[2]))1226                else:1227                    columns.append((i.split('-')[0],i.split('-')[1])) 1228        data = tuple(data.itertuples(index=False,name=None))1229        return render_template('search_db.html',data = data,ini=ini, fin=fin, to_order=to_order, properties=properties,columns=columns,methods=methods,msg=msg,n_res=n_res,functionals=functionals,basis=basis_sets,forcefields=forcefields,all_dbs=all_dbs,noprev=noprev,nonext=nonext,title=db,desc=desc)1230    elif 'orderby_property' in request.form:1231        from_form=request.form1232        from_form = from_form.to_dict(flat=False)1233        ascending = True1234        if 'ascending' in from_form['select_order']:1235            if 'order by' not in sql:1236                if 'property' not in sql and 'MW' in sql:1237                    sql = sql[:sql.rindex('limit')] + ' order by molecule.MW ' +sql[sql.rindex('limit'):]1238                else:1239                    sql = sql[:sql.rindex(')')+1]+ ' order by Value.num_value ' + sql[sql.rindex(')')+1:]1240            elif 'order by' in sql and 'DESC' in sql:1241                if 'property' not in sql and 'MW' in sql:1242                    sql = sql[:sql.rindex('DESC')] + sql[sql.rindex('DESC')+5:]1243                else:1244                    sql = sql[:sql.rindex('value')+5] + ' ' + sql[sql.rindex('value')+11:]1245        else:1246            ascending = False1247            if 'order by' in sql and 'DESC' not in sql:1248                if 'property' not in sql and 'MW' in sql:1249                    sql = sql[:sql.rindex('MW')+3] + 'DESC ' + sql[sql.rindex('MW')+3:]   1250                else:               1251                    sql = sql[:sql.rindex('value')+5] + ' DESC' + sql[sql.rindex('value')+5:]1252            elif 'order by' not in sql:1253                if 'property' not in sql and 'MW' in sql:1254                    sql = sql[:sql.rindex('limit')] + ' order by molecule.MW DESC ' +sql[sql.rindex('limit'):]1255                else:1256                    sql = sql[:sql.rindex(')')+1]+ ' order by value.num_value DESC ' + sql[sql.rindex(')')+1:]1257        1258        if sql.count('value.num_value') > 4:1259            multiprop = True1260            sql = sql[:sql.rfind(')')+1]+';'1261        cur.execute(sql)1262        all_results = cur.fetchall()1263        data, columns = post_process(sql, all_results)1264        search_results = data              1265        search_results.columns = columns1266        if 'MW' not in sql and 'property' in sql:1267            search_results = search_results.sort_values(by=from_form['property_orderby'], ascending = ascending)1268        desc=['','']1269        data = tuple(search_results[:50].itertuples(index=False,name=None))1270        for i in search_results.columns[2:]:1271            desc.append('mean={}, std={}, min={}, max={}'.format(search_results[i].describe()['mean'].round(2),search_results[i].describe()['std'].round(2),search_results[i].describe()['min'].round(2),search_results[i].describe()['max'].round(2)))1272        columns=[]1273        for i in search_results.columns:1274            if '-' not in i:1275                if 'MW' in i:1276                    columns.append((i,'pybel'))1277                else:1278                    columns.append((i,''))1279            else:1280                if len(i.split('-')) > 2:1281                    columns.append((i.split('-')[0],i.split('-')[1]+'-'+i.split('-')[2]))1282                else:1283                    columns.append((i.split('-')[0],i.split('-')[1]))1284        return render_template('search_db.html',data = data,properties=properties,to_order = True, ini=ini,fin=fin,noprev=noprev,nonext=nonext,columns=columns,methods=methods,n_res=n_res,basis=basis_sets,functionals=functionals,forcefields=forcefields,all_dbs=all_dbs,title=db,desc=desc)1285    else:1286        return render_template('search_db.html',properties=properties,methods=methods,basis=basis_sets, functionals=functionals, forcefields=forcefields,all_dbs=all_dbs,title=db)1287@app.route('/molecule-<dbid>',methods=['GET','POST'])1288def molecule(dbid):1289    # cur,conn=connect_mysql()1290    import urllib.parse1291    db=dbid.split('-')[0]1292    db=db.replace(' ','_')1293    id=dbid.split('-')[1]1294    sql = 'SELECT Molecule.id, Molecule.MW, Molecule.SMILES,Molecule.Standard_Inchi,Molecule.Standard_Inchi_Key,CAS_Registry_Number,IUPAC_Name,Other_name,Chemical_Formula,Property.Property_str, Property.Unit, Model.method_name,  Functional.name, Basis_set.name,forcefield.name, Value.num_value from Molecule inner join value on Molecule.id=Value.Molecule_id inner join Property on Property.id=VALUE.property_id INNER JOIN Model on Value.model_id=Model.id inner join functional on functional.id=value.functional_id inner join basis_set on basis_set.id=value.basis_id inner join forcefield on forcefield.id=value.forcefield_id where Molecule.id={}'.format(id)1295    db=db+'_chembddb'1296    cur.execute('USE {};'.format(db))1297    cur.execute(sql)1298    result=cur.fetchall()1299    mol_data=pd.DataFrame(list(result),columns=['ID','MW','SMILES','Standard_Inchi','Standard_Inchi_Key','CAS_Registry_Number','IUPAC_Name','Other_name','Chemical_Formula','Property','Unit','Method','Functional','Basis_set','Forcefield','Value'])1300    mol_data['ALL']=mol_data['ID'].astype(str) +',;'+mol_data['MW'].astype(str)+',;'+mol_data['SMILES']+',;'+mol_data['Standard_Inchi']+',;'+mol_data['Standard_Inchi_Key']+',;'+mol_data['CAS_Registry_Number']+',;'+mol_data['IUPAC_Name']+',;'+mol_data['Other_name']+',;'+mol_data['Chemical_Formula']1301    mol_data['Property(Unit)']=mol_data['Property']+' ('+mol_data['Unit']+')\n'+'- '+mol_data['Method']+'('+mol_data['Functional']+'/'+mol_data['Basis_set']+')('+mol_data['Forcefield']+')'1302    mol_data=mol_data[['ALL','Property(Unit)','Value']]1303    mol_data=mol_data.pivot(index='ALL',columns='Property(Unit)')1304    mol_data=mol_data['Value'].reset_index()1305    mol_data[['ID','MW','SMILES','Standard_Inchi','Standard_Inchi_Key','CAS_Registry_Number','IUPAC_Name','Other_name','Chemical_Formula']]=mol_data['ALL'].str.split(',;',expand=True)1306    #print(url_smi)1307    #print(mol_data['SMILES'][0])1308    # converting smiles to chemspider ID NOTE: use this for other mol identifiers1309    1310    #url = "http://cactus.nci.nih.gov/chemical/structure/{}/chemspider_id".format(mol_data['SMILES'][0])1311    #print(url)1312    cols=['ID','MW','SMILES','Standard_Inchi','Standard_Inchi_Key','CAS_Registry_Number','IUPAC_Name','Other_name','Chemical_Formula']1313    for i in mol_data.columns[1:-9]:1314        cols.append(i)1315    mol_data=mol_data[cols]1316    msg = ''1317    mol_data,added = molidentfiers.populate_molidentifiers(mol_data)1318    mol_data = mol_data.replace('NONE',np.nan)1319    mol_data.dropna(axis=1,inplace=True)1320    url_smi = urllib.parse.quote_plus(mol_data['SMILES'][0])1321    smi = str(mol_data.loc[0]['SMILES'])1322    mol_ob = pybel.readstring("smi",smi)1323    mymol = pybel.readstring("smi", mol_ob.write("can"))1324    mymol.make3D(forcefield='mmff94', steps=50)1325    mymol.write('xyz',app.config['UPLOAD FOLDER']+'/chembddb_{}.xyz'.format(mol_data['ID'][0]),overwrite=True)1326    with open(app.config['UPLOAD FOLDER']+'/chembddb_{}.xyz'.format(mol_data['ID'][0])) as f:1327        xyz = f.read()1328        mol_data['XYZ'] = xyz1329    added = list(set(added).intersection(set(mol_data.columns)))1330    button = []1331    for i in range(len(mol_data.columns)):1332        if mol_data.columns[i] in added:1333            button.append('True')1334        else:1335            button.append('')1336    button = pd.Series(button,index=mol_data.columns,name=1)1337    mol_data = mol_data.append(button)1338    cols = mol_data.columns[:-1]1339    if request.method == 'POST' and 'addtodb' in request.form:1340        print('here')1341        print(request.form['addtodb'])1342        identifier = request.form['addtodb'].split(',')[0]1343        value = request.form['addtodb'][len(identifier)+1:]1344        if 'InChI=' in value:1345            value = value[6:]1346        print(value)1347        print(identifier)1348        q = 'Update Molecule set {}="{}" where id={};'.format(identifier,value,id)1349        print(q)1350        cur.execute(q)1351        con.commit()1352        print('done')1353        mol_data[identifier][1] = ''1354    #ids = molidentifiers()1355    if request.method == 'POST' and 'Download' in request.form:1356        mol_data.to_json('molecule.json')1357        msg = 'Downloaded molecule.json'1358    cols=[c.replace('(na/na)','') for c in cols]1359    cols=[c.replace('(na)','') for c in cols]1360    cols=[c.replace('(na)','') for c in cols]1361    mol_data = tuple(mol_data.itertuples(index=False,name=None))1362    mol_data = (tuple(cols[:]),)+mol_data1363    mol_data=tuple(zip(*mol_data))1364    db=db.replace('_',' ')1365    return render_template('molecule.html',mol_data=mol_data,columns=cols,title=db,all_dbs=all_dbs,url_smi=url_smi,msg=msg)1366@app.route('/delete',methods=['GET','POST'])1367def delete(host='',user='',pw='',db=''):1368    """1369    Delete a database that was created using chembddb or delete data from one.1370    Parameters1371    ----------1372    host: str default=''1373        the hostname is the domain name or server name1374    user: str default=''1375        the username for mysql1376    pw: str default=''1377        the password for mysql1378    db: str default=''1379        the name of the database that needs to be set up1380    """1381    global all_dbs1382    if db !='':1383        try:1384            db = db+'_chembddb'1385            a,b = connect_mysql(host=host,user=user,pw=pw)1386            cur.execute('drop database %s;'%db)1387            return 'Successfully deleted the database'1388        except:1389            return 'Failed! database does not exist'1390    else:1391        cur.execute('show databases;')1392        all_dbs=[]1393        all_dbs_tup=cur.fetchall()1394        for i in all_dbs_tup:1395            if '_chembddb' in i[0] and 'unit_list' not in i[0]:1396                m=i[0]1397                all_dbs.append((m[:-9],))1398        details=request.form1399        details=details.to_dict(flat=True)1400        if 'dbname' in details:1401            dbname=details['dbname']+'_chembddb'1402            cur.execute('use {};'.format(dbname))1403            cur.execute('Select * from Property')1404            properties=cur.fetchall()1405            cur.execute('Select id,method_name from Model')1406            results=cur.fetchall()1407            cur.execute("Select * from Functional")1408            functionals=cur.fetchall()1409            cur.execute("Select * from Basis_set")1410            basis_sets=cur.fetchall()1411            cur.execute("Select * from Forcefield")1412            forcefields=cur.fetchall()1413            methods=[]1414            for i in results:1415                methods.append(i[1])1416            dbname = dbname[:-9]1417        if 'submit' in details:1418            return render_template('delete.html',data=True,dbname=dbname,properties=properties,methods=methods,functionals=functionals,basis=basis_sets,forcefields=forcefields,all_dbs=all_dbs)1419        elif 'search-query' in details:1420            #if 'exampleRadios' not in details:1421            if details['exampleRadios'] == 'option_null':1422                dbname = dbname+'_chembddb'1423                cur.execute('drop database {}'.format(dbname))1424                cur.execute('show databases;')1425                all_dbs_tup=cur.fetchall()1426                all_dbs=[]1427                for i in all_dbs_tup:1428                    if '_chembddb' in i[0] and 'unit_list' not in i[0]:1429                        m=i[0]1430                        all_dbs.append((m[:-9],))1431                #return render_template('delete.html',data=True,properties=properties,methods=methods,functionals=functionals,basis=basis_sets,forcefields=forcefields,all_dbs=all_dbs,success_msg='database {} deleted'.format(details['dbname']))1432                return render_template('delete.html',all_dbs=all_dbs, success_msg = 'database {} deleted successfully'.format(dbname.replace('_chembddb','')))1433            elif details['exampleRadios']=='option1':1434                if 'MW' in details and details['MW']!='':1435                    mw_from=details['MW_from_val']1436                    mw_to=details['MW_to_val']1437                if 'smiles_search' in details:1438                    smi = details['smiles']1439                    smi_obj = pybel.readstring('smi',smi)1440                    can_smi = smi_obj.write('can').strip()1441                    mol_wt = round(smi_obj.molwt,3)1442                    cur.execute('select id,SMILES,MW from Molecule where SMILES=\'{0}\' and (MW-{1}) < 0.00001;'.format(can_smi,mol_wt))1443                    to_delete=list(cur.fetchall())1444                    sql = 'delete from Value where molecule_id={};'.format(to_delete[0][0])1445                    cur.execute(sql)1446                    cur.execute('delete from Molecule where id={};'.format(to_delete[0][0]))1447                    con.commit()1448            else:1449                dbname = details['dbname'] + '_chembddb'1450                cur.execute('USE {};'.format(dbname))1451                # name of property check boxes have '_id' in them1452                keys=[i for i in details if '_id' in i]1453                # find molecule id if smiles_search in details1454                # find MW from and to1455                for k in keys:1456                    prop_id=int(details[k][0])1457                    # Remove the property from the database1458                    if details[k[:-3]+'_from_val']=='' and details[k[:-3]+'_to_val']=='':1459                        sql='DELETE FROM Property WHERE id={};'.format(prop_id)1460                        cur.execute(sql)1461                        con.commit()1462                        cur.execute('use {};'.format(dbname))1463                        cur.execute('Select * from Property')1464                        properties=cur.fetchall()1465                        cur.execute('Select id,method_name from Model')1466                        results=cur.fetchall()1467                        cur.execute("Select * from Functional")1468                        functionals=cur.fetchall()1469                        cur.execute("Select * from Basis_set")1470                        basis_sets=cur.fetchall()1471                        cur.execute("Select * from Forcefield")1472                        forcefields=cur.fetchall()1473                        methods=[]1474                        for i in results:1475                            methods.append(i[1])1476                    else:1477                        from_val=float(details[k[:-3]+'_from_val'])1478                        to_val=float(details[k[:-3]+'_to_val'])1479                        if from_val > to_val:1480                            return render_template('delete.html',data=True, dbname=details['dbname'].replace('_chembddb',''),properties=properties,methods=methods,functionals=functionals,basis=basis_sets,forcefields=forcefields,all_dbs=all_dbs,err_msg='Minimum value for one of the properties is greater than the maximum value for it.')1481                        else:1482                            sql='DELETE FROM Value WHERE property_id={} and num_value > {} and num_value < {};'.format(prop_id,from_val,to_val)1483                            cur.execute(sql)1484                            #cur.execute('select id from Molecule')1485                            #mol_ids = cur.fetchall()1486                            #cur.execute('select molecule_id from Value')1487                            #mol_ids_val = cur.fetchall()1488                            #to_delete = []1489                            #mol_ids_val = set(mol_ids_val)1490                            #for i in mol_ids:1491                            #    if i not in mol_ids_val:1492                            #        to_delete.append(i[0])1493                            #print('HERE:\n')1494                            #print(to_delete)1495                            #cur.execute('delete from Molecule where id={};'.format(str(tuple(to_delete))))1496                            con.commit()1497            return render_template('delete.html',data=True, dbname=details['dbname'].replace('_chembddb',''),properties=properties,methods=methods,functionals=functionals,basis=basis_sets,forcefields=forcefields,all_dbs=all_dbs,success_msg='Deleted from database {}.'.format(details['dbname'].replace('_chembddb','')))1498        else:1499            return render_template('delete.html',all_dbs=all_dbs)1500def backup_restore(host='',user='',pw='',db='',filename=''):1501    import subprocesses1502    if db !='':1503        pass1504    else:1505        try:1506            s= 'mysqldump -h localhost -P 3306 -u '+user+' -p'+pw+ ' '+db+' --single-transaction  > ' + filename1507            subprocesses.Popen(s,shell=True)1508            return 'done'1509        except:1510            return 'error'1511        1512@app.route('/uploads/<filename>')1513def uploaded_file(filename):1514    return send_from_directory(app.config['UPLOAD FOLDER'],filename)1515def run_config():1516    print('Open http://localhost:5000/connect')1517    app.run(debug=True)1518    for i in os.listdir():1519        if 'chembddb' in i and 'xyz' in i:1520            os.remove(i)...

findruns.py

Source:findruns.py

1import os2from os import path3import matplotlib.pyplot as plt4import numpy as np5import nuphase_data_reader6from scipy.fftpack import fft7from BalloonThreshold import BalloonThreshold8def ReturnFFTForPlot(t,v):9    fft = np.fft.fft(v)10    N = len(v)11    freqs = np.fft.fftfreq(len(v),d=t[1]-t[0])12    db = 10.0*np.log10(np.abs(fft[:int(N/2)]/1000)**2/50.0)13    return(freqs[:int(N/2)],db)14mydir = "./data/2018/"15def findrunnumbers(directorypath):16    #Grab list of all TSV files:17    TSV_files = []18    #mydir = "../SouthPole_Radiosonde/data/2018"19    for root, dirs, files in os.walk(mydir):20        for filename in files:21            print(filename)22            TSV_files.append(mydir+filename)23    TSV_files = sorted(TSV_files)24    print(TSV_files)25    runnumbers = []26    first_run = 500 #change this as you find balloons so that you don't have to keep starting over at run 50027    for bfile in TSV_files:28        #grab times from this balloon29        thresholdtimes = BalloonThreshold(bfile)30        print('')31        print('')32        print('new balloon file: ', bfile)33        34        for PA_run in range(first_run,4103): #loop over runs35            36            try:37                d = nuphase_data_reader.Reader("/project2/avieregg/nuphase/telem/root",PA_run)38                39                #print(d.N())40                41                #grab first and last time in run:42                d.setEntry(0)43                h = d.header()44                readout_time_i = h.readout_time[0]45                46                d.setEntry(d.N()-1)47                h = d.header()48                readout_time_f = h.readout_time[0]49                #print(readout_time_i)50                #print(readout_time_f)51                print(PA_run, thresholdtimes[0]-readout_time_i)52                #if threshold is between the two run times, save run and exit for loop53                if readout_time_i < thresholdtimes[0] < readout_time_f:54                    print('success!',PA_run)55                    runnumbers.append(PA_run)56                    first_run = runnumbers[-1] #set beginning of loop to = current run. This works because the runs are in consecutive order and so are the balloons.57                    58                    break59                #if balloon time is before current run, assume that the phased array was not on and continue to new balloon file60                elif thresholdtimes[0]<readout_time_i:61                    62                    print('Phased Array likely not on during this balloon run. Continue')63                    first_run = PA_run64                    #PA_run=410365                    break66                #move on to next run if current run doesn't exist67            except:68                print("Run " + str(PA_run) + " doesn't exist. Moving on to the next run.")69    return runnumbers70#runnumbers = findrunnumbers(mydir)71#print(runnumbers)72#runnumbers = [518,525,552,619,694,774,914,1153,1264,1376,1487,1730]73runnumbers = [517,518,519]74def plotfunction(runlist):75    all_dbs = defaultdict(list)76    all_freqs = defaultdict(list)77    78    for r in runlist:79        d = nuphase_data_reader.Reader("/project2/avieregg/nuphase/telem/root",r)80        d.setEntry(0)81        #all_dbs[j].append(np.max(db))82        startevent = 50083        endevent = d.N()84        numofevents = endevent-startevent85        for i in range(0,numofevents):86            d.setEntry(i)87            h = d.header()88            readout_time = h.readout_time[0]89            for j in [0,1,2,3,4,6,7]:90                t = d.t()91                wf = d.wf(j)92                freqs,db = ReturnFFTForPlot(t,wf)93                freqs=freqs[1:]94                db=db[1:]95                all_dbs[j].append(np.max(dbs))96                all_freqs[j].append(freqs[np.argmax(db)])97                #print(all_dbs)98                #print(all_freqs[j])99                #all_dbs.update({j:all_dbs[j]+np.max(db)})100                101                102        plt.figure(1)103        for j in [0,1,2,3,4,6,7]:104            plt.subplot(4,2,j+1)105            print(len(all_dbs[j]))106            plt.scatter(np.linspace(startevent,endevent,numofevents),np.asarray(all_dbs[j]),label='Channel'+str(j))107            plt.xlabel('Run Number')108            plt.ylabel('~dB')109            #plt.ylim([-60.0,-15.0])110            #plt.title('Channel'+str(j)111            plt.legend()112            plt.grid(True)113            114        plt.figure(2)115        for j in [0,1,2,3,4,6,7]:116            plt.subplot(4,2,j+1)117            plt.scatter(np.linspace(startevent,endevent,numofevents), np.asarray(all_freqs[j])*1000,label='Channel'+str(j))118            plt.xlabel('Run Number')119            plt.ylabel('Frequency (MHz)')120            #plt.ylim([-60.0,-15.0])121            #plt.title('Channel'+str(j)122            plt.legend()123            plt.grid(True)124        plt.show()125#plotfunction(runnumbers)126def MakeSpectrogram():127    first_run = runnumbers[0]128    last_run= runnumbers[(len(runnumbers)-1)]129    freq_spacing = 2.9296874999999996 #MHz130    freq_resolution = 1 #four times what it is131    channel = 0#make spectrogram for one channel at a time132    bin_size = 10 #seconds133    #set up run list:134    runlist = np.linspace(first_run,last_run,int(last_run-first_run+1),dtype=int)135    #load data and start/end time:136    d = nuphase_data_reader.Reader("/project2/avieregg/nuphase/telem/root",first_run)137    h = d.header()138    t0 = h.readout_time[0]139    d = nuphase_data_reader.Reader("/project2/avieregg/nuphase/telem/root",last_run)140    d.setEntry(d.N()-1)141    h = d.header()142    tf = h.readout_time[0]143    event_times = []144    freqs, dbs = ReturnFFTForPlot(d.t(),d.wf(0))145    all_dbs = np.zeros([int(len(freqs)/freq_resolution),int((tf-t0)/bin_size)+1])146    all_counters = np.zeros(int((tf-t0)/bin_size)+1)147    for run in runlist:148        print(run)149        if(os.path.exists('/project2/avieregg/nuphase/telem/root/run'+str(run)+'/event.root')):150            d = nuphase_data_reader.Reader("/project2/avieregg/nuphase/telem/root",run)151            #event_list = np.arange(0,d.N(),100,dtype=int)152            for i in range(0,d.N()):153                d.setEntry(i)154                h = d.header()155                ts = h.readout_time[0]156                #get FFT 157                freqs, new_dbs = ReturnFFTForPlot(d.t(),d.wf(channel))158                #new_dbs = np.zeros(int(len(dbs)/freq_resolution))159                #for d_count in range(0,int(len(dbs)/freq_resolution)):160                #    new_dbs[d_count]=sum(dbs[d_count*freq_resolution:d_count*freq_resolution+freq_resolution])/freq_resolution161                all_dbs[:,int((ts-t0)/bin_size)]=all_dbs[:,int((ts-t0)/bin_size)]+new_dbs162                all_counters[int((ts-t0)/bin_size)]=all_counters[int((ts-t0)/bin_size)]+1163                event_times.append(h.readout_time[0])164    plt.imshow(all_dbs/all_counters,aspect='auto',extent=[0,(tf-t0)/3600.0,freqs[-1]*1000,freqs[0]*1000])165    plt.title(str(run))166    plt.xlabel('Time (hr)')167    plt.ylabel('Frequency (MHz)')168    plt.colorbar()169    plt.show()170    ...

views.py

Source:views.py

1from django.shortcuts import render2from django.db.models import Q3from .models import SolutionMasterSpecies, SolutionSpecies, Phases, SurfaceMasterSpecies, SurfaceSpecies,\4                    ExchangeMasterSpecies, ExchangeSpecies, Rates, Refs5from scripts.VistorStatistics import clientStatistics6# Create your views here.7def index(request):8    clientStatistics(request)9    NumElements = SolutionMasterSpecies.objects.count()10    NumSpecies = SolutionSpecies.objects.count()11    NumPhases = Phases.objects.count()12    NumSurfaceMasters = SurfaceMasterSpecies.objects.count()13    NumSurfaceSpecies = SurfaceSpecies.objects.count()14    NumExchangeMasters = ExchangeMasterSpecies.objects.count()15    NumExchangeSpecies = ExchangeSpecies.objects.count()16    NumRates = Rates.objects.count()17    return render(request, 'phreeqcdb/index.html',18                  {'NumElements': NumElements,19                  'NumSpecies': NumSpecies,20                  'NumPhases': NumPhases,21                   'NumSurfaceMasters': NumSurfaceMasters,22                   'NumSurfaceSpecies': NumSurfaceSpecies,23                   'NumExchangeMasters': NumExchangeMasters,24                   'NumExchangeSpecies': NumExchangeSpecies,25                   'NumRates': NumRates,26                   })27def master(request):28    clientStatistics(request)29    all_master = SolutionMasterSpecies.objects.filter(~Q(DBSource__DBID__contains='test'))30    all_master = sorted(all_master, key=lambda x: x.Element)31    # get refs32    all_refs = []33    for p in all_master:34        all_refs.append(p.Ref)35    # get DBs36    all_DBs = []37    for p in all_master:38        all_DBs.append(p.DBSource)39    return render(request, 'phreeqcdb/master.html',40                  {'all_master': all_master, 'refs': list(set(all_refs)), 'dbs': list(set(all_DBs))})41def species(request):42    clientStatistics(request)43    all_species = SolutionSpecies.objects.filter(~Q(DBSource__DBID__contains='test'))44    # get refs45    all_refs = []46    for p in all_species:47        all_refs.append(p.Ref)48    # get DBs49    all_DBs = []50    for p in all_species:51        all_DBs.append(p.DBSource)52    return render(request, 'phreeqcdb/species.html',53                  {'all_species': all_species, 'refs': list(set(all_refs)), 'dbs': list(set(all_DBs))})54def phases(request):55    clientStatistics(request)56    all_phases = Phases.objects.filter(~Q(DBSource__DBID__contains='test'))57    # get refs58    all_refs = []59    for p in all_phases:60        all_refs.append(p.Ref)61    # get DBs62    all_DBs = []63    for p in all_phases:64        all_DBs.append(p.DBSource)65    return render(request, 'phreeqcdb/phases.html',66                  {'all_phases': all_phases, 'refs': list(set(all_refs)), 'dbs': list(set(all_DBs))})67def surfacemaster(request):68    clientStatistics(request)69    all_master = SurfaceMasterSpecies.objects.filter(~Q(DBSource__DBID__contains='test'))70    # get refs71    all_refs = []72    for p in all_master:73        all_refs.append(p.Ref)74    # get DBs75    all_DBs = []76    for p in all_master:77        all_DBs.append(p.DBSource)78    return render(request, 'phreeqcdb/surfacemaster.html',79                  {'all_master': all_master, 'refs': list(set(all_refs)), 'dbs': list(set(all_DBs))})80def surfacespecies(request):81    clientStatistics(request)82    all_species = SurfaceSpecies.objects.filter(~Q(DBSource__DBID__contains='test'))83    # get refs84    all_refs = []85    for p in all_species:86        all_refs.append(p.Ref)87    # get DBs88    all_DBs = []89    for p in all_species:90        all_DBs.append(p.DBSource)91    return render(request, 'phreeqcdb/surfacespecies.html',92                  {'all_species': all_species, 'refs': list(set(all_refs)), 'dbs': list(set(all_DBs))})93def exchangemaster(request):94    clientStatistics(request)95    all_master = ExchangeMasterSpecies.objects.filter(~Q(DBSource__DBID__contains='test'))96    # get refs97    all_refs = []98    for p in all_master:99        all_refs.append(p.Ref)100    # get DBs101    all_DBs = []102    for p in all_master:103        all_DBs.append(p.DBSource)104    return render(request, 'phreeqcdb/exchangemaster.html',105                  {'all_master': all_master, 'refs': list(set(all_refs)), 'dbs': list(set(all_DBs))})106def exchangespecies(request):107    clientStatistics(request)108    all_species = ExchangeSpecies.objects.filter(~Q(DBSource__DBID__contains='test'))109    # get refs110    all_refs = []111    for p in all_species:112        all_refs.append(p.Ref)113    # get DBs114    all_DBs = []115    for p in all_species:116        all_DBs.append(p.DBSource)117    return render(request, 'phreeqcdb/exchangespecies.html',118                  {'all_species': all_species, 'refs': list(set(all_refs)), 'dbs': list(set(all_DBs))})119def rates(request):120    clientStatistics(request)121    all_rates = Rates.objects.filter(~Q(DBSource__DBID__contains='test'))122    # get refs123    all_refs = []124    for p in all_rates:125        all_refs.append(p.Ref)126    # get DBs127    all_DBs = []128    for p in all_rates:129        all_DBs.append(p.DBSource)130    return render(request, 'phreeqcdb/rates.html',...

Automation Testing Tutorials

Learn to execute automation testing from scratch with LambdaTest Learning Hub. Right from setting up the prerequisites to run your first automation test, to following best practices and diving deeper into advanced test scenarios. LambdaTest Learning Hubs compile a list of step-by-step guides to help you be proficient with different test automation frameworks i.e. Selenium, Cypress, TestNG etc.