-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathdatabasetools.py
403 lines (240 loc) · 14.2 KB
/
databasetools.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
from astrodbkit import astrodb
from astropy.io import fits
from contextlib import redirect_stdout
import pandas as pd
import numpy as np
import re
import io
import os
import math
import csv
import pip
Dependencies = [
'astrodbkit',
'numpy',
'pandas']
class dbtools:
"""
Columbia University Astronomy Lab database handler
"""
def __init__(self):
"""This is the __init_ section of the class where initial constants, variables etc are declared and other
initial namespace operations are performed"""
#Schema of the Database
self.database_schema = {
#Keys are table names and values are column names within the tables
'SOURCES': ['id','ra','dec','designation','publication_shortname','names','components',
'companions','comments','version'],
'CHANGELOG': ['id','date','user','machine_name','modified_tables','user_description','filename'],
'IGNORE': ['id','id1','id2','tablename'],
'MODES':['id','mode','publication_shortname'],
'SYSTEMS': ['id','name','publication_shortname'],
'PUBLICATIONS':['id','bibcode','shortname','DOI','description'],
'INSTRUMENTS' : ['id', 'name', 'publication_shortname'],
'TELESCOPES' : ['id','name','publication_shortname'],
'DATA_REQUESTS' : ['id','source_id','source','data_request','purpose'],
'VERSIONS' : ['id', 'version', 'data_published', 'publication_shortname', 'comment'],
'PHOTOMETRY': ['id','source_id','band','magnitude','magnitude_unc','system_id','telescope_id',
'instrument_id', 'publication_shortname','epoch','comments','versions'],
'SPECTRAL_TYPES' : ['id','source_id','spectral_type','spectral_type_unc','publication_shortname','adopted',
'comments','version','num_spt','num_spt_unc'],
'PARALLAXES' : ['id','source_id','parallax','parallax_unc','publication_shortname','adopted','comments',
'version'],
'PROPER_MOTIONS' : ['id', 'source_id', 'proper_motion_ra','proper_motion_ra_unc', 'proper_motion_dec',
'proper_motion_dec_unc','V_tan','V_tan_unc','publication_shortname',
'comments', 'versions'],
'RADIAL_VELOCITIES' : ['id','source_id','radial_velocity','radial_velocity_unc','spectrum_id',
'publication_shortname','comments','version'],
'SPECTRA' : ['id', 'source_id', 'spectrum' ,'wavelength_units', 'flux_units' ,'wavelength_order',
'regime','publication_shortname','obs_date','instrument_id','telescope_id','mode_id',
'filename','comments','best','version','local_spectrum'],
'COMPANIONS' : ['id','source_id','publication_shortname','type','n', 'status'],
'ACTIVITY' : ['id','source_id','regime','line','wavelength_min','wavelength_max', 'frequency_min',
'frequency_max','ew','ew_unc','l_lbol','l_lbol_unc','flux','flux_unc','publication_shortname',
'epoch','telescope_id','instrument_id','spectrum_id','comments'],
'MEMBERSHIP' : ['id', 'source_id', 'publication_shortname', 'pmem', 'pmem_unc' ,'comments'],
'MODELS' : ['id', 'model', 'publication_shortname'],
'MASS' : ['id', 'source_id', 'mass', 'mass_unc', 'model_id', 'publication_shortname', 'comments'],
'ROTATION' : ['id', 'source_id', 'publication_shortname', 'period', 'period_unc','telescope_id',
'instrument_id','comments','tau','tau_model_id','adopted']
}
#Dictonary where primary_ids are stored
self.primary_id_dict = {
'SOURCES': 0,
'CHANGELOG': 0,
'IGNORE' : 0,
'MODES' : 0,
'SYSTEMS' : 0,
'PUBLICATIONS': 0,
'INSTRUMENTS' : 0,
'TELESCOPES': 0,
'DATA_REQUESTS' : 0,
'VERSIONS': 0,
'PHOTOMETRY': 0,
'SPECTRAL_TYPES' : 0,
'PARALLAXES' : 0,
'PROPER_MOTIONS' : 0,
'RADIAL_VELOCITIES' : 0,
'SPECTRA' : 0,
'COMPANIONS': 0,
'ACTIVITY' : 0,
'MEMBERSHIP' : 0,
'MODELS' : 0,
'MASS' : 0,
'ROTATION' : 0
}
for key in self.primary_id_dict:
self.primary_id_dict[key] = self.get_index('tabledata/{}.sql'.format(key))
self.source_id = 0
#none value array
self.globalnulls = [-9999.0,-99.0,'no string','X','None','Nan','nan']
# def remove none values using none value dict
#def check mapping file is ok (check for ' )
# add the latest row check for .sql creation
# take backups
#HELPER FUNCTIONS
#creates an empty mapping file to be used when auto importing a fits file
def create_mapping(self, filename = 'mapping.csv'):
"""Creates an unfilled csv file with column names of the database and empty rows that can be
filled in order to map the .fits file into the csv.
"""
with open(filename, 'w') as csvfile:
writer = csv.writer(csvfile)
for key in self.database_schema:
writer.writerow([key] + self.database_schema[key])
for i in range(5):
writer.writerow('\n')
print('Completed')
#gets the highest index number from .sql data files
def get_index(self,filename):
try:
for line in reversed(open(filename).readlines()):
if '\n' == line:
continue
index = int(re.search('[0-9]+',line)[0])
break
return index
except FileNotFoundError:
return 0
def write_row(self,tablename,valuesarray):
"""Writes a row of data values given in valuesarray into the given tablename
Parameters
----------
tablename: str
name of the table in which the data will be inserted
valuesarray: python array
array of values to be inserted into the database
Comments
--------
This function can be used to override the astrodbkit wrapper whenever it doesn't work.
"""
self.primary_id_dict[tablename] += 1
values = [str(i) for i in valuesarray]
values[0] = str(self.primary_id_dict[tablename])
if tablename != 'SOURCES':
values[1] = str(self.source_id)
if not os.path.exists('tabledata'):
os.makedirs('tabledata')
file = open('tabledata/{}.sql'.format(tablename.lower()),'a+')
file.write("INSERT INTO '{}' VALUES\n".format(tablename))
valuestowrite = '({});\n'.format(','.join(values))
file.write(valuestowrite)
file.close()
#END HELPER FUNCTIONS
""" def add_statics():
"""
def add_statics(self):
self.publications=[
'dotter2008','delfosse2000','kraus2007','douglas2014','agueros2011','kovacs2014','kafka2006',
'skrutskie2006','zacharias2012']
self.telescopes = [
'SDSS','2MASS','WISE','UKIRT','HST','Spitzer','IRTF','Keck I','Keck II','KPNO 4m',
'KPNO 2.1m','KPNO Bok','MMT','CTIO 1.5m','CTIO 4m','Gemini North','Gemini South',
'ARC 3.5m','Subaru','Magellan I Baade','Magellan II Clay','ESO 1m','CFHT','NTT',
'Palomar 200-inch','Pan-STARRS 1','Palomar 60-inch','CTIO 0.9m','SOAR 4.1m','GTC',#30
'ESO VLT U1','ESO VLT U2','ESO VLT U3','ESO VLT U4','MDM 2.4m','MDM 1.3m','WIYN 3.5m'
'Mayall']
self.instruments = [
'R-C Spec','GMOS-N','GMOS-S','FORS','LRIS','SpeX','LDSS3','FOCAS','NIRSPEC','IRS',#10
'FIRE','MagE','GoldCam','SINFONI','OSIRIS','Triplespec','X-Shooter','GNIRS','WIRCam','CorMASS',#20
'ISAAC','IRAC','DIS','SuSI2','IRCS','NIRI','P1640','NIFS','STIS','CGS4'#30
'NIRC','IRCAM','SPHERE','WFC3','SofI','WIRCam','NICI','PANIC','NEWFIRM','UIST',#40
'MegaCam','UFTI','MIKE','GPI','NaCo','FourStar','Flamingos2','ModSpec','Hydra','SDSS-I/II',#50
'APOGEE','BOSS','OSMOS']
self.modes = [
'Prism','SXD','LXD1.9','SL','LXD2.3', 'Echelle','LL','VPH Grism','Red Grism']
self.systems = [
'SDSS','2MASS','WISE','AB','ST','Vega','MKO','CIT','Johnson','Cousins',#10
'Landolt','Stromgren','Bessel','GALEX','DENIS','APASS','Kepler','Gaia']
self.models = [
'dotter','dotter2008','delfosse2000','kraus2007']
#CHECK FOR NEW MODEL NAMES?,
def add_fits(self, fitsfile, csvmapfile):
""" automatically populates the database from the fits file using the mapping.csv
file that links the two
Parameters
----------
fits_file: str
The name and path of the Flexible Image Transport System file
cvsmapfile: str
the name and path of the .csv file with a particular expected format that is used to map the
values in the fits file to database
Constructs
----------
.sql files that can be used by astrodbkit to add data into the database
"""
# open the fits file and save the data segment into a variable
fitsdata = fits.open(fitsfile)[1].data
# open the .csv file for mapping and save into a variable
mapping = pd.read_csv(csvmapfile,header=None,na_values='\n')
#get the headers of the fits file
fitscolumnnames = fitsdata.columns.names
for datarow in fitsdata:
#increment source_id which is unique for each .fits row
self.source_id += 1
for index,row in mapping.iterrows():
#gate that switches to the current tablename based on the mapping.csv file
if type(row[0]) != float:
tablename = row[0]
schema = self.database_schema[tablename.upper()]
#continue to next column as the first column is just for table names
continue
#The .csv file returns a float(nan) object as None so float means we are at a row with values.
elif type(row[0]) == float:
values = ['NULL'] * len(schema)
servicearray = []
for i in range(len(row)):
if type(row[i]) == float:
if math.isnan(row[i]):
continue
else:
values[i-1] = str(row[i])
else:
split = row[i].split(',')
if row[i] in fitscolumnnames:
servicearray.append(datarow[row[i]])
try:
float(str(datarow[row[i]]))
values[i-1] = datarow[row[i]]
except ValueError:
values[i-1] = "'{}'".format(datarow[row[i]])
elif len(split) > 1 and split[0] in fitscolumnnames :
package = [str(datarow[header]) for header in split]
values[i-1] = "'{}'".format(','.join(package))
else:
values[i-1] = "'{}'".format(row[i])
nullvalues = [i for i in servicearray if i in self.globalnulls]
if values.count('NULL') < len(values) and len(nullvalues) != len(servicearray):
if tablename == 'SOURCES':
sourcenulls = [-9999.0,-99.0,'no string','X',0,-9999,'-9999','-9999.0','-99.0','0']
valuestosplit = re.sub("'","",values[5])
sourcenamevalue = valuestosplit.split(',')
sourcenames = [ 'ASAS','','2MASS','SWASP','USNO','UCAC','GOLDMAN','EPIC']
zippedsourcenames = list(zip(sourcenames,sourcenamevalue))
values[5] = "'{}'".format(','.join(['{} {}'.format(i,j) for (i,j)\
in zippedsourcenames if j not in sourcenulls]))
self.write_row(tablename,values)
else:
self.write_row(tablename,values)
else:
continue