def testArbitraryQuery(self):
"""
Test that an arbitrary query can be executed.
No attempt is made to validat the results.
"""
table = db.Table('Summary', 'obsHistID', database=self.database, driver=self.driver)
query = 'select count(expMJD), filter from ObsHistory, ObsHistory_Proposal'
query += ' where obsHistID = ObsHistory_obsHistID group by Proposal_propID, filter'
results = table.execute_arbitrary(query)
#This is a specific case that gave me trouble when refactoring DBObject
#Something about the fact that the database was stored in unicode
#tripped up numpy.rec.fromrecords(). This test will verify that the
#problem has not recurred
query = 'select sessionID, version, sessionDate, runComment from Session'
dtype=np.dtype([('id',int),('version',str,256),('date',str,256),('comment',str,256)])
results = table.execute_arbitrary(query,dtype=dtype)
self.assertTrue(isinstance(results[0][0],int))
self.assertTrue(isinstance(results[0][1],str))
self.assertTrue(isinstance(results[0][2],str))
self.assertTrue(isinstance(results[0][3],str))
self.assertEqual(results[0][0],1133)
self.assertEqual(results[0][1],'3.1')
self.assertEqual(results[0][2],'2014-07-11 17:02:08')
self.assertEqual(results[0][3],'all DD + regular survey for 1 lunation')
def opsim_query_stack10(dbadr, constraint):
import lsst.sims.maf.db as db
from lsst.sims.catalogs.generation.db import *
dbobj = DBObject(dbadr)
tableid = 'output_opsim3_61'
table = db.Table(tableid, 'obshistid', dbadr)
ccc = table.query_columns_Array(
colnames=['fieldra', 'fielddec', 'rawseeing', 'filter', 'expmjd'],
constraint=constraint)
print ccc
return ccc
def testTable(self):
"""Test that we can connect to a DB table and pull data."""
# Make a connection
table = db.Table('Summary', 'obsHistID', database=self.database, driver=self.driver)
# Query a particular column.
data = table.query_columns_Array(colnames=['finSeeing'])
self.assertTrue(isinstance(data, np.ndarray))
self.assertTrue('finSeeing' in data.dtype.names)
# Check error is raised if grabbing a non-existent column
self.assertRaises(ValueError, table.query_columns_Array, colnames=['notRealName'])
# Check that can apply a sql constraint.
constraint = 'filter = "r" and finSeeing < 1.0'
data = table.query_columns_Array(colnames=['finSeeing', 'filter'], constraint=constraint)
maxseeing = data['finSeeing'].max()
self.assertTrue(maxseeing <= 1.0)
filter = np.unique(data['filter'])
self.assertEqual(filter, 'r')