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)
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:
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('/')
# 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) # 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
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
#print(trans_iso.id)
try:
upper_el = session.query(EnergyLevel).filter(
EnergyLevel.exomol_ID == key.split(' - ')[0]).one()
except sqlalchemy.orm.exc.NoResultFound:
#not in db, add
upper_el = EnergyLevel()
upper_el.exomol_ID = key.split(' - ')[0]
upper_el.isotopologue = trans_iso.id
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
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
#---------------------------------------------------------------
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)
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)
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(',')
for supe in supers:
supe = supe.split('/')
if len(supe) < 2 :
continue
supe[0].strip(' ')
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)
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('/')
# 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)
i += 1
if i % 1000 == 0:
end = time.time()
print(i, (end - start) / 1000 * (1800000 - i) / 60, "min", 1000 / (end - start), "per second")
start = end
session.commit()
session.begin()
session.commit()
engine.dispose() # cleanly disconnect from the database
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" 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)
#
# 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:
session.add(city)
#STATUS
#Add statuses to table if they don't already exist
existing_statuses = list(session.query(Status))
existing_status_names = list()
