first_names, last_names, cities, supervisors, statuses, clubs = np.loadtxt(data, comments='#', dtype=(np.str_), skiprows=5, delimiter='|', usecols=(0,1,2,3,4,5), unpack=True) ### loop through each entry in the arrays created above ### for i in range(0,len(last_names)): # create a student object s = Student() s.first_name = first_names[i] s.last_name = last_names[i] session.add(s) # next, create a city object # see if the city is already in the database try: one_city = session.query(City).filter(City.label==cities[i]).one() # if it isn't, then add it except sqlalchemy.orm.exc.NoResultFound: one_city = City() one_city.label = cities[i] session.add(one_city) # connect the city info to the student s.city = one_city # repeat this process for clubs # students can be in more than one club, or none clublist = clubs[i].split(', ') for j in range(0,len(clublist)): if clublist[j] != '': # maybe the student isn't in any clubs # see if the club is already in the database try:
import sys import sqlalchemy from SQLiteConnection import engine, Session from ModelClasses import * from sqlalchemy import or_, and_ import pandas as pd import seaborn as sns import math import matplotlib.pyplot as plt session = Session() cutoff = 2 dbsearch = session.query(Transition).join(Transition.isotopologue).filter(or_(Transition.K_mu > cutoff, Transition.K_mu < -cutoff)).all() data_dict = {'molecule': [], 'exomol ID': [], 'wavenumber': [], 'intensity':[], 'log(intensity)': [], 'K_mu': [], 'upper J': []} for entry in dbsearch: data_dict['molecule'].append(entry.isotopologue.name) data_dict['exomol ID'].append(entry.exomol_ID) data_dict['wavenumber'].append(entry.wavenumber) data_dict['intensity'].append(entry.intensity) data_dict['log(intensity)'].append(math.log10(entry.intensity)) data_dict['K_mu'].append(entry.K_mu) data_dict['upper J'].append(entry.upper.J) print(len(data_dict['molecule'])) data_df = pd.DataFrame(data=data_dict)
def pop_db_nochck(file1, file2, intensity_cutoff, temperature): '''A function to populate that database ExoMol_K.db with output files from DUO. Needed are the .states and .trans files of an isotopologue without mass perturbation and with perturbation. functions will join the states and trans files as well as calculate the intensity. a function will then compare every transition frequency and calculate the K_mu and K_I. The db will have all of the states and trans information of the non perturbative mass of the isotopologue as well as the fractional change in mass, intensity, and frequency, K_mu and K_I''' from func_join_statestrans import join_statestrans from func_compare import compare '''dictionary of nuclear statistical weight factor for each isotopologue''' g_ns_dict = { '31P16O': 2, '31P32S': 2, '14N32S': 3, '32S1H': 2, '45Sc1H': 16, '27Al16O': 6, '27Al18O': 6, '26Al16O': 6, '14N16O': 2, '28Si1H': 2, '51V16O': 8, '48Ti16O': 1, '12C12C': 1 } # print(file1) name1 = file1.split('/') name2 = file2.split('/') #get the fractional change in mu from the second datafile name try: frac_delta_mu = 10**float(name2[-1].split('e')[1]) # print (frac_delta_mu) except ValueError: print( f'filenames not set up to have change in mu before extension, fix and try again' ) sys.exit(0) # check if data1 and data2 are the same molecule molecule1 = name1[-1].split('_')[0] molecule2 = name2[-1].split('_')[0] if molecule1 != molecule2: print("You're not comparing the same molecule data files") print(molecule1) # print(molecule2) '''set g_ns; add to dictionary if not present''' try: g_ns = (g_ns_dict[molecule1]) except KeyError: g_ns_dict[molecule1] = input( f'g_ns for {molecule1} not in dictionary, add now: ') # if molecule1 in g_ns_dict: # g_ns = (g_ns_dict[molecule1]) # else: print('Molecule g_ns not in dictionary') g_ns = (g_ns_dict[molecule1]) '''join states and trans files for molecule, calculate intensity''' data1_statestrans, Q_T = join_statestrans(file1, g_ns, temperature) data2_statestrans, Q2T = join_statestrans(file2, g_ns, temperature) print('comparing') '''compare statestrans for two data files, calculate K ect for each transition''' data_compare = compare(data1_statestrans, data2_statestrans, frac_delta_mu, intensity_cutoff) name = molecule1 # print(frac_delta_mu) count = 0 print('opening db') '''open session to db''' session = Session(autoflush=False) '''check if isotopologue in db, if not; add. create relationship between isotopologue and transition''' try: trans_iso = session.query(Isotopologue).filter( Isotopologue.name == name).filter( Isotopologue.temperature == temperature).one() print('Isotopologue exists already') sys.exit(0) except sqlalchemy.orm.exc.NoResultFound: #not in db, add #print('add') trans_iso = Isotopologue() trans_iso.name = name trans_iso.temperature = temperature trans_iso.g_ns = g_ns_dict[name] trans_iso.Q_T = Q_T session.add(trans_iso) session.flush() except sqlalchemy.orm.exc.MultipleResultsFound: raise Exception("Too many in db - FIX!") sys.exit(0) '''loop through all transitions check if in db, if not; add''' for key in data_compare: #try: #new_trans = session.query(Transition).filter(Transition.exomol_ID==key).filter(Transition.wavenumber==data1_statestrans[key][21]).one() #except sqlalchemy.orm.exc.NoResultFound: #not in db, add new_trans = Transition() new_trans.exomol_ID = key new_trans.einstien_A = data1_statestrans[key][20] new_trans.intensity = data1_statestrans[key][22] new_trans.wavenumber = data1_statestrans[key][21] new_trans.change_mu = frac_delta_mu new_trans.change_nu = data_compare[key][2] new_trans.change_I = data_compare[key][3] new_trans.K_mu = data_compare[key][4] new_trans.K_I = data_compare[key][5] '''link to isotopologue''' new_trans.isotopologue_id = trans_iso.id #except sqlalchemy.orm.exc.MultipleResultsFound: #raise Exception("Too many in db - FIX!") # print('search for iso') '''check if energylevels in db, if not; add. create relationship between energylevels and transition''' try: upper_el = session.query(EnergyLevel).filter( EnergyLevel.exomol_ID == key.split(' - ')[0]).filter( EnergyLevel.isotopologue_id == trans_iso.id).one() except sqlalchemy.orm.exc.NoResultFound: #not in db, add upper_el = EnergyLevel() upper_el.exomol_ID = key.split(' - ')[0] # print('link up energylevel to iso') upper_el.isotopologue_id = trans_iso.id # print('linked') upper_el.energy = data1_statestrans[key][0] upper_el.degeneracy = data1_statestrans[key][1] upper_el.J = data1_statestrans[key][2] upper_el.Tparity = data1_statestrans[key][3] upper_el.Rparity = data1_statestrans[key][4] upper_el.state = data1_statestrans[key][5] upper_el.v = data1_statestrans[key][6] upper_el.Lambda = data1_statestrans[key][7] upper_el.Sigma = data1_statestrans[key][8] upper_el.Omega = data1_statestrans[key][9] session.add(upper_el) session.flush() except sqlalchemy.orm.exc.MultipleResultsFound: raise Exception("Too many in db - FIX!") # print('here') new_trans.upper_id = upper_el.id try: lower_el = session.query(EnergyLevel).filter( EnergyLevel.exomol_ID == key.split(' - ')[1]).filter( EnergyLevel.isotopologue_id == trans_iso.id).one() except sqlalchemy.orm.exc.NoResultFound: #not in db, add lower_el = EnergyLevel() lower_el.exomol_ID = key.split(' - ')[1] # print('link low energylevel to iso') lower_el.isotopologue_id = trans_iso.id # print('linked') lower_el.energy = data1_statestrans[key][10] lower_el.degeneracy = data1_statestrans[key][11] lower_el.J = data1_statestrans[key][12] lower_el.Tparity = data1_statestrans[key][13] lower_el.Rparity = data1_statestrans[key][14] lower_el.state = data1_statestrans[key][15] lower_el.v = data1_statestrans[key][16] lower_el.Lambda = data1_statestrans[key][17] lower_el.Sigma = data1_statestrans[key][18] lower_el.Omega = data1_statestrans[key][19] session.add(lower_el) session.flush() except sqlalchemy.orm.exc.MultipleResultsFound: raise Exception("Too many in db - FIX!") new_trans.lower_id = lower_el.id session.add(new_trans) session.flush() count += 1 print(count) print('closing db') '''commit new additions to db''' session.commit() engine.dispose() # cleanly disconnect from the database
line = data.readline() keys = line.rstrip().split('\t') for line in data: vals = line.rstrip().split('\t') entry = dict(zip(keys, vals)) current_student = Student() current_student.first_name = entry['first_name'] current_student.last_name = entry['last_name'] session.add(current_student) try: student_status = session.query(Status) \ .filter(Status.label==entry['status']).one() except sqlalchemy.orm.exc.NoResultFound: student_status = Status() student_status.label = entry['status'] session.add(student_status) current_student.status = student_status if entry['supervisors'] != '': super_list = entry['supervisors'].split(', ') for supinfo in super_list: sup = supinfo.split('/') try: one_super = session.query(Supervisor) \ .filter(Supervisor.name==sup[0]).one()
from SQLiteConnection import engine, Session from ModelClasses import * filename = 'student_data.txt' data = open(filename) session = Session() student = Student() student.first_name = "Amelia" student.last_name = "Pond" session.add(student) try: one_supervisor = session.query(Supervisor).filter(Supervisor.last_name=='Tennant') \ .filter(Supervisor.first_name=='David').one() except sqlalchemy.orm.exc.NoResultFound: one_supervisor = Supervisor() one_supervisor.last_name = "Tennant" one_supervisor.first_name = "David" session.add(one_supervisor) except sqlalchemy.orm.exc.MultipleResultsFound: print "There is more than one Doctor!" sys.exit(1) student.supervisors.append(one_supervisor) session.commit()
# or use argparse # data = open(filename) session = Session(autoflush=False) for key in statestrans: new_trans = Transition() new_trans.exomol_ID = key new_trans.einstien_A = statestrans[key][20] new_trans.intensity = statestrans[key][22] new_trans.wavenumber = statestrans[key][21] try: trans_iso = session.query(Isotopologue).filter( Isotopologue.name == name).filter( Isotopologue.temperature == temperature).one() #print('try') except sqlalchemy.orm.exc.NoResultFound: #not in db, add print('add') trans_iso = Isotopologue() trans_iso.name = name trans_iso.temperature = temperature trans_iso.g_ns = g_ns trans_iso.Q_T = Q_T session.add(trans_iso) session.flush() except sqlalchemy.orm.exc.MultipleResultsFound: raise Exception("Too many in db - FIX!") new_trans.isotopologue = trans_iso.id
from ModelClasses import * filename = 'student_data.txt' data = open(filename) lines = data.readlines() data.close() session = Session() for line in lines[5:]: line = line.split("|") #print line try: a_student = session.query(Student).filter(Student.last_name==line[1]).filter(Student.first_name==line[0]).one() except sqlalchemy.orm.exc.NoResultFound: student = Student() student.first_name = line[0] student.last_name = line[1] session.add(student) print("Adding {} {}".format(line[0],line[1])) except sqlalchemy.orm.exc.MultipleResultsFound: print("**{} {} is already in database!**".format(line[0],line[1])) #Eccleston/Room 205, Baker/Room 315 supers = line[3].split(',')
#--------------------------------------------------------------- # Start a new object for each student, add data fields to object #--------------------------------------------------------------- newStudent = Student() newStudent.first_name = str(line[0]) newStudent.last_name = str(line[1]) session.add(newStudent) print "Name: {0}, {1}".format(line[1], line[0]) #---------------------------------------------------------------------- # Check to see if the city the student is from is already in the db # If it's not, create the new city object, point it towards the student # And add it to the db #---------------------------------------------------------------------- try: one_city = session.query(City).filter(City.label==str(line[2])).one() except sqlalchemy.orm.exc.NoResultFound: one_city = City() one_city.label = str(line[2]) session.add(one_city) newStudent.city = (one_city) #---------------------------------------------------- # Split up the supervisors if there are more than one #---------------------------------------------------- supline = line[3].split(', ') #---------------------------------------------------------------------- # Check to see if the supervisor and their room number are in the db # If they aren't, create the new supervisor object, point it towards
keys = line.rstrip().split('|') i = 0 for line in data: i += 1 vals = line.rstrip().split('|') entry = dict(zip(keys,vals)) current_student = Student() current_student.first_name = entry['first_name'] current_student.last_name = entry['last_name'] session.add(current_student) try: student_status = session.query(Status) \ .filter(Status.label==entry['status']).one() except sqlalchemy.orm.exc.NoResultFound: student_status = Status() student_status.label=entry['status'] session.add(student_status) current_student.status = student_status if entry['supervisors'] != '': super_list = entry['supervisors'].split(', ') for supinfo in super_list: sup = supinfo.split('/') try: one_super = session.query(Supervisor) \ .filter(Supervisor.name==sup[0]).one()
def search_db(): #initiate db session session = Session() #options to only search specific molecules, wavenumber ranges, and intensities #is none are selected - search through db and grab all options set_mol = input('Choose specific molecules? ') dbmol = session.query(Isotopologue.name).distinct() mol = [] [mol.append(item[0]) for item in dbmol] if set_mol =='yes': molecule = input('molecule input: ') molecule = molecule.split(' ') else: molecule = mol #check if molecule in db - if not option to func_pop_db for item in molecule: if item not in mol: add = input(f'{item} not in database, add? ') if add =='yes': f_1 = input('file 1: ') f_2 = input('file 2: ') in_co = float(input('Intensity cutoff for db: ')) temp = float(input('Temperature: ')) pop_db(f_1, f_2, in_co, temp) print(f'{item} added to db') else: print('Exiting') sys.exit(0) print(f'Searching {molecule}') #[print(name[0]) for name in iso if name[0] in molecule] set_range = input('Set transition wavenumber range? ') if set_range =='yes': rangelow = input('From: ') rangeup = input('To: ') else: rangelow = session.query(func.min(Transition.wavenumber)).one()[0] rangeup = session.query(func.max(Transition.wavenumber)).one()[0] print(f'Wavenumber range {rangelow, rangeup}') set_intense = input('Set transition intensity cutoff?(y/n) ') if set_intense == 'yes': intensity_cutoff = float(input('Search for number of transitions with intensity greater than: ')) else: intensity_cutoff = float(session.query(func.min(Transition.intensity)).one()[0]) print(f'Intensity cutoff {intensity_cutoff}') #loop over search options for given initial conditions exit = str() while exit != 'yes': task = input('What would you like to do out of the following; K values, count transitions, column headings, raw SQL? ' ) #option 1 - get table and column headings from db if task =='column headings': con = engine.connect() print(f'tables in database are:\n {engine.table_names()}') usrinpt = input('table name for column headings: ') try: testlist = con.execute(f'select * from {usrinpt}').keys() [print(row) for row in testlist] except sqlalchemy.exc.OperationalError: print("Invalid search terms") #option 2 is a raw SQL search - works best with simple queries elif task == 'raw SQL': con = engine.connect() usrinpt = input('raw SQL comands: ') try: rawSQL = con.execute(usrinpt) #[print(row) for row in testlist] except sqlalchemy.exc.OperationalError: print("Invalid search terms") #save output to memory as list raw_outpt = [] for row in rawSQL: raw_outpt.append(row) print(row) #save to csv file save_raw = input('save output as csv? ') if save_raw =='yes': filename = input('filename? ') with open(f'{filename}.txt', 'w') as file: [file.write(f'{row}\n') for row in raw_outpt] #create pdf plot plot_raw = input('plot raw SQL output? ') if plot_raw == 'yes': plot_SQL(raw_outpt) #option 3 count the number of transitions - separates by temp, and molecule #also counts the number of electronic states involved elif task == 'count': tally = session.query(Transition).join(Transition.isotopologue).filter(Isotopologue.name.in_(molecule)).filter(and_(Transition.wavenumber >= rangelow, Transition.wavenumber<= rangeup)).filter(Transition.intensity > intensity_cutoff).all() isos = set() [isos.add(item.isotopologue.name) for item in tally] isos = list(isos) print(isos) temps = set() [temps.add(item.isotopologue.temperature) for item in tally] temps = list(temps) print(temps) for temp in temps: print(f'T = {temp}') for iso in isos: count = 0 el_states = set() for item in tally: if item.isotopologue.name == iso and item.isotopologue.temperature == temp: count+=1 el_states.add(item.upper.state) el_states.add(item.lower.state) print(f'There are {count} {iso} transitions with intensity greater than {intensity_cutoff} between {rangelow, rangeup}') print(f'there are {len(el_states)} electronic states for {iso}') #option 4 - goes one further with filter and has a |K| condition of. the db search # splits on temp and molecule elif task == 'K values': cutoff =float(input('Search for |K| greater than: ')) K = session.query(Transition).join(Transition.isotopologue).filter(Isotopologue.name.in_(molecule)).filter(and_(Transition.wavenumber >= rangelow, Transition.wavenumber<= rangeup)).filter(Transition.intensity > intensity_cutoff).filter(or_(Transition.K_mu > cutoff, Transition.K_mu <= -cutoff)).order_by(Transition.wavenumber).all() print(f'There are {len(K)} transitions that match that criteria') isos = set() [isos.add(item.isotopologue.name) for item in K] isos = list(isos) temps = set() [temps.add(item.isotopologue.temperature) for item in K] temps = list(temps) for temp in temps: print(f'T = {temp}') for iso in isos: count = 0 for item in K: if item.isotopologue.name == iso and item.isotopologue.temperature == temp: count+=1 print(f'{count} are from {iso}') #print to screen - can change what is printed - only have name, ID, and K atm K_print=input('Would you like to print the exomol IDs? ') [print(item.isotopologue.name, item.exomol_ID, item.K_mu) for item in K if K_print=='yes'] #save to csv - same ability as above save_K = input('save output as csv? ') if save_K =='yes': filename = input('filename? ') # outpt = [['isotopologue.name', 'exomol_ID', 'K_mu']] # [outpt.append([item.isotopologue.name, item.exomol_ID, item.K_mu]) for item in K] outpt = [['temperature', 'isotopologue.name', 'exomol_ID', 'wavenumber', 'K_mu', 'Intensity', 'upper.energy', 'degeneracy', 'J', 'Tparity', 'Rparity', 'state', 'v', 'Lambda', 'Sigma', 'Omega', 'lower.energy', 'degeneracy', 'J', 'Tparity', 'Rparity', 'state', 'v', 'Lambda', 'Sigma', 'Omega']] [outpt.append([item.isotopologue.temperature, item.isotopologue.name, item.exomol_ID, item.wavenumber, item.K_mu, item.intensity, item.upper.energy, item.upper.degeneracy, item.upper.J, item.upper.Tparity, item.upper.Rparity, item.upper.state, item.upper.v, item.upper.Lambda, item.upper.Sigma, item.upper.Omega, item.lower.energy, item.lower.degeneracy, item.lower.J, item.lower.Tparity, item.lower.Rparity, item.lower.state, item.lower.v, item.lower.Lambda, item.lower.Sigma, item.lower.Omega]) for item in K ] with open(f'{filename}.txt', 'w') as file: [file.write(f'{row}\n') for row in outpt] #create pdf plot - one for each molecule - plus one for all input_plot = input('Would you like to plot these? ') if input_plot == 'yes': plot_K(K, cutoff) exit = input('Are you finished searching? ') engine.dispose() # cleanly disconnect from the database
if add: supervisors_to_add.append(new_supervisor) ############################################################# # # Open the session, do some DB writes # ############################################################# session = Session() #CLUB #Add clubs to table if they don't already exist existing_clubs = list(session.query(Club)) existing_club_names = list() for club in existing_clubs: existing_club_names.append(club.name) for club in clubs_to_add: if club.name not in existing_club_names: session.add(club) #CITY #Add cities to table if they don't already exist existing_cities = list(session.query(City)) existing_city_names = list() for city in existing_cities: existing_city_names.append(city.label) for city in cities_to_add: if city.label not in existing_city_names:
'Data_32S1H/32S1H_J10_100K_e-4', 10**-30, temperature) print(len(compare)) # filename = 'student_data.txt' # or use argparse # data = open(filename) session = Session(autoflush=False) for key in compare: try: new_trans = session.query(Transition).filter( Transition.exomol_ID == key).filter( Transition.wavenumber == statestrans[key][21]).one() except sqlalchemy.orm.exc.NoResultFound: #not in db, add new_trans = Transition() new_trans.exomol_ID = key new_trans.einstien_A = statestrans[key][20] new_trans.intensity = statestrans[key][22] new_trans.wavenumber = statestrans[key][21] new_trans.change_mu = change_mu new_trans.change_nu = compare[key][2] new_trans.change_I = compare[key][3] new_trans.K_mu = compare[key][4] new_trans.K_I = compare[key][5] except sqlalchemy.orm.exc.MultipleResultsFound: raise Exception("Too many in db - FIX!")
#for the column names line = line.strip('\n') line = line.split('|') student = Student() student.first_name = line[0] student.last_name = line[1] session.add(student) for supervisor_room in line[3].split(', '): if supervisor_room != [] #supervisor,room = supervisor_room.split('/') try: one_supervisor = session.query(Supervisor).filter(Supervisor.last_name==supervisor_room).one() except sqlalchemy.orm.exc.NoResultFound: one_supervisor = Supervisor() one_supervisor.last_name = supervisor_room session.add(one_supervisor) except sqlalchemy.orm.exc.MultipleResultsFound: print ('There is more than one Doctor!') sys.exit(1) student.supervisors.append(one_supervisor) # try: # one_city = session.query(City).filter(City.last_name==line[2]).one() # except sqlalchemy.orm.exc.NoResultFound: # one_city = City()