def load_database(connection_string):
# Create and load the database
create_database(connection_string)
# Now that the database is created, connect to it and load up the JSON data
db = Database(connection_string, reference_tables=REFERENCE_TABLES)
db.load_database(DB_PATH, verbose=False)
print('New database generated.')
# Close all connections
db.session.close()
db.engine.dispose()
def load_db():
# Utility function to load the database
db_file = 'SIMPLE.db'
db_file_path = Path(db_file)
db_connection_string = 'sqlite:///SIMPLE.db' # SQLite browser
if RECREATE_DB and db_file_path.exists():
os.remove(db_file) # removes the current .db file if one already exists
if not db_file_path.exists():
create_database(db_connection_string) # creates empty database based on the simple schema
db = Database(db_connection_string) # connects to the empty database
db.load_database('data/') # loads the data from the data files into the database
else:
db = Database(db_connection_string) # if database already exists, connects to .db file
return db
def db():
# Create a fresh temporary database and assert it exists
# Because we've imported simple.schema, we will be using that schema for the database
if os.path.exists(DB_NAME):
os.remove(DB_NAME)
connection_string = 'sqlite:///' + DB_NAME
create_database(connection_string)
assert os.path.exists(DB_NAME)
# Connect to the new database and confirm it has the Sources table
db = Database(connection_string)
assert db
assert 'source' in [c.name for c in db.Sources.columns]
# Load the database contents
# This should take care of finding serious issues (key/column violations)
db.load_database(DB_PATH, verbose=False)
return db
def load_simpledb(db_file, recreatedb=True):
# Utility function to load the database
db_file_path = Path(db_file)
db_connection_string = 'sqlite:///' + db_file
if recreatedb and db_file_path.exists():
os.remove(
db_file) # removes the current .db file if one already exists
if not db_file_path.exists():
try: # Use fancy in-memory database, if supported by astrodbkit2
db = Database(
'sqlite://'
) # creates and connects to a temporary in-memory database
db.load_database(
'data/'
) # loads the data from the data files into the database
db.dump_sqlite(db_file) # dump in-memory database to file
db = Database(db_connection_string
) # replace database object with new file version
except RuntimeError:
# use in-file database
create_database(
db_connection_string
) # creates empty database based on the simple schema
db = Database(
db_connection_string) # connects to the empty database
db.load_database(
'data/'
) # loads the data from the data files into the database
else:
db = Database(db_connection_string
) # if database already exists, connects to .db file
return db
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)
# Load database contents from JSON
db = Database(destination_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()})
db.load_database('bdnyc')
# NOTE: This currently does not work since there are inconsistencies
# in the sqlite bdnyc contents (strings in integer fields for example)
# Also: some manual editing was required since SPECTRUM is not a valid Postgres type
# and keeps getting interpreted as numeric as opposed to text
# Script to add Y dwarfs spectral types
from astrodbkit2.astrodb import create_database
from astrodbkit2.astrodb import Database
#from simple.schema import *
from astropy.table import Table
import numpy as np
import re
from utils import convert_spt_string_to_code
connection_string = 'sqlite:///../../SIMPLE.db' # SQLite
create_database(connection_string)
db = Database(connection_string)
db.load_database('../../data')
# load table
ingest_table = Table.read('Y-dwarf_table.csv', data_start=2)
names = ingest_table['source']
n_sources = len(names)
regime = ['infrared'
] * 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
# Set correct connection string
if DB_TYPE == 'sqlite':
# First, remove the existing database in order to recreate it from the schema
# If the schema has not changed, this part can be skipped
if os.path.exists(DB_NAME):
os.remove(DB_NAME)
connection_string = 'sqlite:///' + DB_NAME
elif DB_TYPE == 'postgres':
# For Postgres, we connect and drop all database tables
connection_string = 'postgresql://' + DB_NAME
try:
db = Database(connection_string)
db.base.metadata.drop_all()
db.session.close()
db.engine.dispose()
except RuntimeError:
# Database already empty or doesn't yet exist
pass
create_database(connection_string)
# Now that the database is created, connect to it and load up the JSON data
db = Database(connection_string)
db.load_database(DB_PATH, verbose=False)
print('New database generated.')
# Close all connections
db.session.close()
db.engine.dispose()
VERBOSE = False
verboseprint = print if VERBOSE else lambda *a, **k: None
db_file = 'SIMPLE.db'
db_file_path = Path(db_file)
db_connection_string = 'sqlite:///SIMPLE.db' # SQLite browser
if RECREATE_DB and db_file_path.exists():
os.remove(db_file) #removes the current .db file if one already exists
if not db_file_path.exists():
create_database(db_connection_string
) #creates empty database based on the simple schema
db = Database(db_connection_string) #connects to the empty database
db.load_database(
'data/') #loads the data from the data files into the database
else:
db = Database(db_connection_string
) #if database already exists, connects to .db file
#Adding missing publications to publication table
add_publication(db, name='Sahl16', bibcode='J/MNRAS/455/357', dryrun=False)
add_publication(db, name='Liu16', bibcode='J/ApJ/833/96', dryrun=False)
add_publication(db, name='Wang18', bibcode='2018PASP..130f4402W', dryrun=False)
add_publication(db, name='Bedi17', bibcode='2017MNRAS.470.1140B', dryrun=False)
add_publication(db, name='Delo17', bibcode='2017A&A...608A..79D', dryrun=False)
add_publication(db, name='Luhm16', bibcode='2016AJ....152...78L', dryrun=False)
# load table of sources to ingest
input_file = ("scripts/ingests/ATLAS_table.vot")
ATLAS = Table.read(input_file, format='votable')
RECREATE_DB = True
VERBOSE = False
verboseprint = print if VERBOSE else lambda *a, **k: None
db_file = 'SIMPLE.db'
db_file_path = Path(db_file)
db_connection_string = 'sqlite:///SIMPLE.db' # SQLite
if RECREATE_DB and db_file_path.exists():
os.remove(db_file)
if not db_file_path.exists():
create_database(db_connection_string)
db = Database(db_connection_string)
db.load_database('data')
# try:
# db_file_path = db_file.resolve(strict=True)
# except:
# # SIMPLE.db file does not exist so create it
# create_database(db_connection_string)
# db = Database(db_connection_string)
# db.load_database('data')
# 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')
print(results)
# Direct SQL queries
results = db.sql_query('select * from sources')
print(results)
print(results[0].keys())
# Use inventory to check a single object (output is dictionary)
data = db.inventory('2MASS J13571237+1428398', pretty_print=True)
print(type(data))
# Delete a row
for row in db.query(db.Photometry).all():
print(row)
db.Photometry.delete().where(db.Photometry.c.band == 'WISE_W1').execute()
db.query(db.Photometry).all()
db.query(db.Photometry).count()
# Delete entire table
# NOTE: data linked via foreign keys are also deleted
db.query(db.Telescopes).all()
db.Telescopes.delete().execute()
db.query(db.Telescopes).count()
# Reload via json file
db.load_table('Telescopes', 'data')
# Reload everything
# NOTE: this clears existing DB values to replace them with on-disk file data
db.load_database('data', verbose=True)
],
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)
# --------------------------------------------------------------------------------------
# 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)).\
