def make_sql_column_string(table_source, dataset, *args):
'''
Return string with all column names to return with SQL query, comma separated.
Arguments:
- table_source: SkyServer table name to draw from
- dataset: SkyServer dataset to draw from
- (optional) column features to drop, if any. Can be single feature (eg 'cont') and multiple features
'''
try:
columns = SkyQuery.listTableColumns(tableName=table_source,
datasetName=dataset)
except Exception as err:
if str(err) == 'User token is not defined. First log into SciServer.':
token = login()
columns = SkyQuery.listTableColumns(tableName=table_source,
datasetName=dataset)
else:
raise err
column_names = [col['name'] for col in columns]
if len(args) > 0:
column_names = drop_side_or_cont_(column_names)
id_index, subclass_index = column_names.index(
'SPECOBJID'), column_names.index('SUBCLASS')
column_names[:0] = [
column_names.pop(id_index),
column_names.pop(subclass_index)
]
column_string = ', '.join(column_names)
return column_string
def downloadFile(username, password, tableName_, databaseName_, filename):
login(username, password)
print("Authentication complete, downloading table")
table = SkyQuery.getTable(tableName=tableName_, datasetName=databaseName_)
print("Download complete, writing to CSV")
table.to_csv(filename)
print("Table contents have been written to file " + filename)
print("Table info: \n", table)
def test_SkyQuery_uploadTable_getTable_getTableInfo_listTableColumns_dropTable(
self):
try:
result = SkyQuery.dropTable(tableName=SkyQuery_TestTableName,
datasetName="MyDB")
except:
pass
result = SkyQuery.uploadTable(uploadData=SkyQuery_TestTableCSV,
tableName=SkyQuery_TestTableName,
datasetName="MyDB",
format="csv")
self.assertEqual(result, True)
table = SkyQuery.getTable(tableName=SkyQuery_TestTableName,
datasetName="MyDB",
top=10)
self.assertEqual(SkyQuery_TestTableCSVdownloaded,
table.to_csv(index=False))
info = SkyQuery.getTableInfo(tableName="webuser." +
SkyQuery_TestTableName,
datasetName="MyDB")
columns = SkyQuery.listTableColumns(tableName="webuser." +
SkyQuery_TestTableName,
datasetName="MyDB")
result = SkyQuery.dropTable(tableName=SkyQuery_TestTableName,
datasetName="MyDB")
self.assertEqual(result, True)
"""
#quick synchronous job - probably isn't what you want for any meaningful runs:
#queryResponse = CasJobs.executeQuery(query, "dr14", format='pandas')
#gals = queryResponse
#check tables in myDB
tname = 'test'
data = CasJobs.getTables('MyDB')
if data:
print(data)
print(
'There are already tables in your database under that name, removing them before doing new query...'
)
#CLEAN UP: delete table from myDB
SkyQuery.dropTable(tableName=tname, datasetName='myDB')
#long asynchronous job
print(
'submitting SQL job to SciServer... could take some time depending on query length and SciServer load...'
)
jobID = CasJobs.submitJob(query + "into myDB." + tname, "dr14")
CasJobs.waitForJob(jobID, verbose=True)
try:
#Download table from MYDB into Pandas dataframe. additional parms: , top=10
print('Attempting to download table...')
data_table = SkyQuery.getTable(tableName=tname, datasetName='MyDB')
print('Done! Table shape is: ' + str(data_table.shape))
#save df to disk in case of super long queries:
filename = 'test_query_table_1000'
def test_SkyQuery_listDatasetTables(self):
tables = SkyQuery.listDatasetTables("MyDB")
def test_SkyQuery_getDatasetInfo(self):
info = SkyQuery.getDatasetInfo("MyDB")
def test_SkyQuery_listAllDatasets(self):
datasets = SkyQuery.listAllDatasets()
def test_SkyQuery_listJobs(self):
quickJobsList = SkyQuery.listJobs('quick')
longJobsList = SkyQuery.listJobs('long')
def test_SkyQuery_cancelJob(self):
isCanceled = SkyQuery.cancelJob(
SkyQuery.submitJob(query=SkyQuery_Query, queue="long"))
self.assertEqual(isCanceled, True)
def test_SkyQuery_waitForJob(self):
jobId = SkyQuery.submitJob(query=SkyQuery_Query, queue="quick")
jobDescription = SkyQuery.waitForJob(jobId=jobId, verbose=True)
self.assertEqual(jobDescription["status"], "completed")
def test_SkyQuery_getJobStatus(self):
jobId = SkyQuery.submitJob(query=SkyQuery_Query, queue="quick")
jobDescription = SkyQuery.getJobStatus(jobId=jobId)
def test_SkyQuery_submitJob(self):
jobId = SkyQuery.submitJob(query=SkyQuery_Query, queue="quick")
self.assertNotEqual(jobId, "")
def test_SkyQuery_getQueueInfo(self):
queueInfo = SkyQuery.getQueueInfo('quick')
queueInfo = SkyQuery.getQueueInfo('long')
def test_SkyQuery_listQueues(self):
queueList = SkyQuery.listQueues()
# In[ ]:
#help(SkyQuery)
# In[ ]:
token1 = Authentication.login(Authentication_loginName, Authentication_loginPassword);
# In[ ]:
#list all databses or datasets available
datasets = SkyQuery.listAllDatasets()
print(datasets)
# In[ ]:
#get info about the user's personal database or dataset
info = SkyQuery.getDatasetInfo("MyDB")
print(info)
# In[ ]:
#list tables inside dataset