#!/usr/bin/env python
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']))
# IMPORTANT: this relies on SQLiteConnection.py, DatabaseConnection.py, & ModelClasses.py. # Make sure you have all the column names defined as classes in ModelClasses.py. from __future__ import print_function import sys import sqlalchemy from SQLiteConnection import engine, Session from ModelClasses import * import numpy as np filename = 'student_data.txt' data = open(filename) ### magical 'connect to database' command ### # be sure to specify the filename of the .sqlite database in SQLiteConnection.py !! session = Session() ### read in info from the text file specified above ### # we'll save it as arrays of strings (one per column) 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)
#!/usr/bin/python import sys import sqlalchemy from SQLiteConnection import engine, Session from ModelClasses import * filename = 'student_data.txt' data = open(filename) session = Session() s = Student() s.first_name = "Egon" s.last_name = "Spengler" session.add(s) lacrosse = Club() lacrosse.label = "lacrosse" session.add(lacrosse) s.clubs.append(lacrosse) session.commit() engine.dispose() # cleanly disconnect from the database sys.exit(0)
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
#!/usr/bin/env python
# Author: Claire Lackner
import sys
import sqlalchemy
from SQLiteConnection import engine, Session
from ModelClasses import *
filename = 'student_data.txt'
data = open(filename)
session = Session()
line = data.readline()
while line[0] == '#':
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:
#!/usr/bin/python import sys import sqlalchemy 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)
from func_join_statestrans import join_statestrans
g_ns = 1
filename = '48Ti16O_ELv1'
name = filename.split('_')[0]
temperature = 10
statestrans, Q_T = join_statestrans(filename, g_ns, temperature)
print(len(statestrans))
# filename = 'student_data.txt'
# 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
#!/usr/bin/python
import sys
import sqlalchemy
from SQLiteConnection import engine, Session
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]))
import sys
import sqlalchemy
from SQLiteConnection import engine, Session
from ModelClasses import *
#---------------------------------------------
# Define the file to be read and open the file
#---------------------------------------------
filename = 'student_data.txt'
student_data = open(filename, 'r')
#----------------------
# Start the SQL Session
#----------------------
session = Session()
#--------------------------------------
# Read the N header lines from the file
#--------------------------------------
n_header = 5
for i in range(n_header):
student_data.readline()
#-------------------------------------------------------
# Read each line form the file and populate the database
#-------------------------------------------------------
for line in student_data:
line = line.strip().split('|')
print "---------------------------"
#---------------------------------------------------------------
# Start a new object for each student, add data fields to object
sql_definition = 'CREATE TABLE "sspp" ('
sql_definition += '"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE'
for i in range(len(table.dtype)):
colname = table.dtype.names[i]
nptype = str(table.dtype[i])
coltype = numpytype2sqltype(nptype)
sql_definition += ', "{0}" {1}'.format(colname, coltype)
sql_definition += ');'
print(sql_definition)
# read in data
session = Session()
session.begin()
start = time.time()
i = 0
for row in table:
sspp = Sspp()
for colname, nptype in table.dtype.descr:
#print(colname, nptype, row[colname])
if 'i' in nptype:
#print(type(row[colname]))
setattr(sspp, colname, int(row[colname]))
else:
setattr(sspp, colname, row[colname])
session.add(sspp)
#!/usr/bin/env python
import sys
import sqlalchemy
from SQLiteConnection import engine, Session
from ModelClasses import *
filename = 'student_data.txt'
data = open(filename)
session = Session()
session.begin()
line = data.readline()
while line[0] == '#':
line = data.readline()
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)
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
@author: J.Campbell-White from D.Muna's code """ #!/usr/bin/python import sys import sqlalchemy from SQLiteConnection import engine, Session from ModelClasses import * #filename = 'student_data.txt' #data = open(filename) session = Session() objects = Object() objects.id = 1 objects.ra = 2.5 objects.decl = 89.2 objects.name = 'polaris' session.add(objects) ''' 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"
add = True
for supervisor in supervisors_to_add:
if supervisor.last_name == new_supervisor.last_name and supervisor.room == new_supervisor.room:
add = False
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()
'27Al18O': 6,
'26Al16O': 6
}
#statestrans, compare, Q_T, change_mu = clean('Data_14N32S/14N32S_J10_100K_e-0', 'Data_14N32S/14N32S_J10_100K_e-4',10**-30, temperature)
statestrans, compare, Q_T, change_mu = clean('Data_32S1H/32S1H_J10_100K_e-0',
'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]
#!/usr/bin/python
import sys
import sqlalchemy
from SQLiteConnection import engine, Session
from ModelClasses import *
filename = 'student_data.txt'
data = open(filename)
session = Session()
#student = Student()
for line in data:
print(line)
if line[0] == '#':
continue
else:
#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(', '):
