KaneAI - World’s First E2E Software Testing Agent.Book a Demo 
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.
LambdaTest Learning Hubs:
- JUnit Tutorial
- TestNG Tutorial
- Webdriver Tutorial
- WebDriverIO Tutorial
- Protractor Tutorial
- Selenium 4 Tutorial
- Jenkins Tutorial
- NUnit Tutorial
- Jest Tutorial
- Playwright Tutorial
- Cypress Tutorial
- PyTest Tutorial
YouTube
You could also refer to video tutorials over LambdaTest YouTube channel to get step by step demonstration from industry experts.
Run pytest-django automation tests on LambdaTest cloud grid
Perform automation testing on 3000+ real desktop and mobile devices online.
Try LambdaTest Now !!
Get 100 minutes of automation test minutes FREE!!
Was this article helpful?
