def load_sncoor(self,
sncoor,
sncoor_tname='sncoor',
xmatch_tname='xmatchres'):
tables = CasJobs.getTables(context="MyDB")
tablenames = [x['Name'] for x in tables]
for tname in [sncoor_tname, xmatch_tname]:
if tname in tablenames:
self.delete_table(tname)
self.sncoor_tname = sncoor_tname
self.xmatch_tname = xmatch_tname
self.sncoor = sncoor
f.peak,f.integr,f.rms
FROM SpecPhoto AS sp
JOIN wise_xmatch AS xm ON sp.objid = xm.sdss_objid
JOIN FIRST AS f ON sp.objid = f.objid
JOIN wise_allsky AS w ON xm.wise_cntr = w.cntr
WHERE
(sp.psfmag_r-sp.extinction_r > 0)
"""
#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)
def testCasJobsTables():
schema= CasJobs.getSchemaName()
print("schema=",schema)
tables= CasJobs.getTables("MyDB")
print(tables)
def test_CasJobs_getTables(self):
tables = CasJobs.getTables(context="MyDB")
CasJobs_TestCSVFile = "SciScriptTestFile.csv" # In[ ]: #get user schema info casJobsId = CasJobs.getSchemaName() print(casJobsId) # In[ ]: #get info about tables inside MyDB database context: tables = CasJobs.getTables(context="MyDB") print(tables) # In[ ]: #execute a quick SQL query: df = CasJobs.executeQuery(sql=CasJobs_TestQuery, context=CasJobs_TestDatabase, format="pandas") print(df) # In[ ]: #submit a job, which inserts the query results into a table in the MyDB database context. #Wait until the job is done and get its status.
# <li><em>Name:</em> the name of the table</li>
# <li><em>Rows:</em> the number of rows in the table</li>
# <li><em>Size:</em> the size of the table in kilobytes</li>
# </ul>
#
# The code cell gives two options for printing the list of tables: using Python's <code>pprint</code> library or using the <code>tables_formatted(tableList)</code> convenience function defined above. The convenience function sorts the list of tables alphabetically by name, and displays the dates into datetime values. Try uncommenting and commenting those lines in the Code cell below to see what both options do.
# In[ ]:
#check_token = SciServer.Authentication.getToken()
my_token = SciServer.Authentication.login('portmanm', 'uB53!BYLQXr5XhN')
#this_context = "MyDB" # Your MyDB
this_context = 'DR7' # SDSS Data Release 14
tables = CasJobs.getTables(context=this_context)
#print('Tables in '+this_context+':\n')
#pprint(tables) # Standard human-readable printing using Python's pprint module
# tables_formatted(tables) # Sorting and better printing using a convenience function
# ## Run a quick query and get results directly
#
# Now that you know what contexts (datasets) are available to you, and you know what tables can be found in those contexts, you are ready to write and submit a query to that context. A query is a request for data, written in SQL (Structured Query Language), a programming language designed for efficient database searches.
#
# SkyServer features a <a href="http://skyserver.sdss.org/public/en/help/howto/search/searchhowtohome.aspx" target="_blank">tutorial for learning SQL</a>, as well as <a href="http://skyserver.sdss.org/public/en/help/docs/sql_help.aspx" target="_blank">tips for writing good queries</a> and a long list of <a href="http://skyserver.sdss.org/public/en/help/docs/realquery.aspx" target="_blank">sample queries</a> that you can adapt to create your queries (links open in new windows).
#
# Once you have written a query, you can get results by running (executing) it in CasJobs. To run a query in CasJobs directly from a Code cell in SciServer Compute, use the <strong><code>CasJobs.executeQuery(sql,...)</code></strong> function. The function takes as input a string containing a properly-formatted SQL query (and optional parameters listed below), and returns a table containing query results (in one of several formats with a default of a <a href="https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.html" target="_blank">pandas dataframe</a>).
#
# The <em>sql</em> parameter is required. The <em>context</em> parameter is recommended to explicitly state the context to which the query will be submitted; the default value is 'MyDB'. For a full list of parameters taken by the <code>CasJobs.executeQuery(sql,...)</code> function, see <a href="http://www.sciserver.org/docs/sciscript-python/SciServer.html#module-SciServer.CasJobs" target="_blank">its documentation on the SciServer documentation site</a>.
#
