] * n_sources # all source have IR spectral classifications
spectral_types = ingest_table['SpT']
spt_refs = ingest_table['spt_ref']
# sources names in database Names table
db_names = []
for name in names:
db_name = db.search_object(name, output_table='Sources')[0].source
db_names.append(db_name)
# Convert SpT string to code
spectral_type_codes = convert_spt_string_to_code(spectral_types, verbose=True)
# add new references to Publications table
ref_list = spt_refs.tolist()
included_ref = db.query(db.Publications.c.name).filter(
db.Publications.c.name.in_(ref_list)).all()
included_ref = [s[0] for s in included_ref]
new_ref = list(set(ref_list) - set(included_ref))
new_ref = [{'name': s} for s in new_ref]
if len(new_ref) > 0:
db.Publications.insert().execute(new_ref)
# Make astropy table with all relevant columns and add to SpectralTypes Table
SpT_table = Table(
[db_names, spectral_types, spectral_type_codes, regime, spt_refs],
names=('source', 'spectral_type_string', 'spectral_type_code', 'regime',
'reference'))
db.add_table_data(SpT_table, table='SpectralTypes', fmt='astropy')
db.save_db('../../data')
from sqlalchemy import types # for BDNYC column overrides
# Establish connection to database
# Note that special parameters have to be passed to allow the BDNYC schema work properly
connection_string = 'sqlite:///../BDNYCdb-1/bdnyc_database.db'
db = Database(connection_string,
reference_tables=['changelog', 'data_requests', 'publications', 'ignore', 'modes',
'systems', 'telescopes', 'versions', 'instruments'],
primary_table='sources',
primary_table_key='id',
foreign_key='source_id',
column_type_overrides={'spectra.spectrum': types.TEXT(),
'spectra.local_spectrum': types.TEXT()})
# Query similarly to SIMPLE
results = db.query(db.sources).limit(10).all()
for row in results: print(row)
# The spectra table contains columns of type SPECTRUM, the column_type_overrides allows us to work with them as text
for c in db.spectra.columns: print(c, c.type)
db.query(db.spectra).limit(10).all()
_ = db.inventory(11, pretty_print=True)
# Can output the full contents of BDNYC as json files
db.save_db('bdnyc')
# Copy to another database
source_connection_string = 'sqlite:///../BDNYCdb-1/bdnyc_database.db' # SQLite
destination_connection_string = 'postgresql://localhost/BDNYC' # Postgres
copy_database_schema(source_connection_string, destination_connection_string)
# else:
# # SIMPLE.db file does exist
# if RECREATE_DB: # Recreate database anyway
# os.remove(db_file)
# create_database(db_connection_string)
# db = Database(db_connection_string)
# db.load_database('data')
# else: # Use pre-existing database
# db = Database(db_connection_string)
# ===============================================================
# Ingest new reference if missing
# ===============================================================
# Adding new reference Manj19 to publications table in database
manj19_search = db.query(
db.Publications).filter(db.Publications.c.name == 'Manj19').table()
if len(manj19_search) == 0 and not DRY_RUN:
new_ref = [{'name': 'Manj19'}]
# Should have included bibcode and doi
# new_ref = [{'name': 'Manj19', 'bibcode': '2019AJ....157..101M', 'doi': '10.3847/1538-3881/aaf88f'}]
db.Publications.insert().execute(new_ref)
#add DOI and Bibcode after Manj19 already added
add_doi_bibcode = db.Publications.update().where(db.Publications.c.name == 'Manj19').\
values(bibcode='2019AJ....157..101M', doi='10.3847/1538-3881/aaf88f', description='Cloud Atlas: HST nir spectral library')
db.engine.execute(add_doi_bibcode)
# ===============================================================
# load table of sources to ingest
ingest_table = Table.read("scripts/ingests/ATLAS_table.vot")
ingest_table_df = ingest_table.to_pandas()
'instruments'
],
primary_table='sources',
primary_table_key='id',
foreign_key='source_id',
column_type_overrides={
'spectra.spectrum': types.TEXT(),
'spectra.local_spectrum': types.TEXT()
})
# SIMPLE
connection_string = 'sqlite:///SIMPLE.db'
db = Database(connection_string)
# Copy first publications that are not already in SIMPLE
temp = db.query(db.Publications.c.name).all()
existing_simple = [s[0] for s in temp]
temp = bdnyc.query(bdnyc.publications)\
.filter(db.publications.c.shortname.notin_(existing_simple))\
.all()
# Reformat data into something easier for SIMPLE to import
new_db_mapping = {'DOI': 'doi', 'shortname': 'name'}
data = [{
new_db_mapping.get(k, k): x.__getattribute__(k)
for k in x.keys() if k not in 'id'
} for x in temp]
db.Publications.insert().execute(data)
# Verify insert and save to disk
# Query examples against the database
from astrodbkit2.astrodb import Database
# Establish connection to database
connection_string = 'sqlite:///SIMPLE.db'
db = Database(connection_string)
# Query for all sources
results = db.query(db.Sources).all()
print(results)
# Query for all publications
db.query(db.Publications).all()
# Query for sources with declinations larger than 0
db.query(db.Sources).filter(db.Sources.c.dec > 0).all()
# Query and sort sources by declination
db.query(db.Sources.c.source).order_by(db.Sources.c.dec).all()
# Query for join Sources and Publications and return just several of the columns
results = db.query(db.Sources.c.source, db.Sources.c.reference, db.Publications.c.name)\
.join(db.Publications, db.Sources.c.reference == db.Publications.c.name)\
.all()
print(results)
# Query with AND
db.query(db.Sources).filter(and_(db.Sources.c.dec > 0,
db.Sources.c.ra > 200)).all()
'source': '2MASS J00192626+4614078',
'regime': 'infrared',
'spectrum': 'https://s3.amazonaws.com/bdnyc/SpeX/Prism/U10013_SpeX.fits',
'telescope': 'IRTF',
'instrument': 'SpeX',
'mode': 'Prism',
'reference': 'Cruz18',
'wavelength_units': 'um',
'flux_units': 'erg s-1 cm-2 A-1',
'observation_date': datetime.fromisoformat('2004-11-08')
}]
db.Spectra.insert().execute(spec_data)
# Verify inventory lists the new spectrum
_ = db.inventory('2MASS J00192626+4614078', pretty_print=True)
# Getting spectrum object as a specutils Spectrum1D object
# Refer to https://astrodbkit2.readthedocs.io/en/latest/#general-queries-with-transformations for more information
t = db.query(db.Spectra.c.spectrum).filter(
db.Spectra.c.source == '2MASS J00192626+4614078').table(
spectra=['spectrum'])
t = db.query(db.Spectra.c.spectrum).filter(
db.Spectra.c.source == '2MASS J00192626+4614078').limit(1).spectra()
# Confirm that it is a Spectrum1D object
spec = t[0][0]
print(type(spec))
# Save database modifications to disk
db.save_database('data')
connection_string = 'sqlite:///../BDNYCdevdb/bdnycdev.db'
bdnyc = Database(connection_string,
reference_tables=['changelog', 'data_requests', 'publications', 'ignore', 'modes',
'systems', 'telescopes', 'versions', 'instruments'],
primary_table='sources',
primary_table_key='id',
foreign_key='source_id',
column_type_overrides={'spectra.spectrum': types.TEXT(),
'spectra.local_spectrum': types.TEXT()})
# SIMPLE
connection_string = 'sqlite:///SIMPLE.db'
db = Database(connection_string)
# Copy first publications that are not already in SIMPLE
temp = db.query(db.Publications.c.name).all()
existing_simple = [s[0] for s in temp]
temp = bdnyc.query(bdnyc.publications)\
.filter(db.publications.c.shortname.notin_(existing_simple))\
.all()
# Reformat data into something easier for SIMPLE to import
new_db_mapping = {'DOI': 'doi', 'shortname': 'name'}
data = [{new_db_mapping.get(k, k): x.__getattribute__(k)
for k in x.keys() if k not in 'id'
}
for x in temp]
db.Publications.insert().execute(data)
# Verify insert and save to disk
# Connect to the database; if creating it, also populate it
# Note that we need to specify this is a new reference table (until AstrodbKit2 gets updated for it)
if not db_file_path.exists():
create_database(db_connection_string)
db = Database(db_connection_string, reference_tables=REFERENCE_TABLES)
db.load_database('data/')
else:
db = Database(db_connection_string, reference_tables=REFERENCE_TABLES)
# ---------------------------------------------------------------------------------
# Add telescope/instrument information first
# Fetch existing telescopes, add if missing
telescopes = list(set([s['telescope'] for s in filters_to_add]))
existing = db.query(db.Telescopes).filter(db.Telescopes.c.name.in_(telescopes)).table()
if len(existing) > 0:
existing = existing['name'].tolist()
new_telescopes = list(set(telescopes)-set(existing))
insert_data = [{'name': s} for s in new_telescopes]
if len(insert_data) > 0:
db.Telescopes.insert().execute(insert_data)
# Fetch existing instruments, add if missing
instruments = list(set([s['instrument'] for s in filters_to_add]))
existing = db.query(db.Instruments).filter(db.Instruments.c.name.in_(instruments)).table()
if len(existing) > 0:
existing = existing['name'].tolist()
new_instruments = list(set(instruments)-set(existing))
insert_data = [{'name': s} for s in new_instruments]
if len(insert_data) > 0:
connection_string = 'sqlite:///SIMPLE.db'
db = Database(connection_string)
# --------------------------------------------------------------------------------------
# Reload from directory, if needed
db.load_database('data', verbose=False)
# --------------------------------------------------------------------------------------
# For each source in SIMPLE, search in BDNYC and grab specified photometry
# Will be only grabbing WISE data for now
telescope = 'WISE'
band_list = ['WISE_W1', 'WISE_W2', 'WISE_W3', 'WISE_W4']
# Don't include sources that already have photometry in these bands
temp = db.query(db.Photometry.c.source).filter(
db.Photometry.c.band.in_(band_list)).distinct().all()
sources_with_photometry = [s[0] for s in temp]
sources = db.query(db.Sources).\
filter(db.Sources.c.source.notin_(sources_with_photometry)).\
pandas()
# Get the BDNYC source_id values for our SIMPLE sources
source_dict = {}
for i, row in sources.iterrows():
bd_source = bdnyc.search_object(
row['source'],
output_table='sources',
table_names={'sources': ['designation', 'names']},
fmt='pandas')
if len(bd_source) != 1: