def testJoin(self): """ Test a join """ dbobj = DBObject(driver=self.driver, database=self.database) query = 'SELECT doubleTable.id, intTable.id, doubleTable.log, intTable.thrice ' query += 'FROM doubleTable, intTable WHERE doubleTable.id = intTable.id' results = dbobj.get_chunk_iterator(query, chunk_size=10) dtype = [('id', int), ('id_1', int), ('log', float), ('thrice', int)] i = 0 for chunk in results: if i < 90: self.assertEqual(len(chunk), 10) for row in chunk: self.assertEqual(2 * (i + 1), row[0]) self.assertEqual(row[0], row[1]) self.assertAlmostEqual(numpy.log(row[0]), row[2], 6) self.assertEqual(3 * row[0], row[3]) self.assertEqual(dtype, row.dtype) i += 1 self.assertEqual(i, 99) #make sure that we found all the matches whe should have results = dbobj.execute_arbitrary(query) self.assertEqual(dtype, results.dtype) i = 0 for row in results: self.assertEqual(2 * (i + 1), row[0]) self.assertEqual(row[0], row[1]) self.assertAlmostEqual(numpy.log(row[0]), row[2], 6) self.assertEqual(3 * row[0], row[3]) i += 1 self.assertEqual(i, 99)
def testColumnNames(self): """ Test the method that returns the names of columns in a table """ dbobj = DBObject(driver=self.driver, database=self.database) names = dbobj.get_column_names('doubleTable') self.assertEqual(len(names), 3) self.assertTrue('id' in names) self.assertTrue('sqrt' in names) self.assertTrue('log' in names) names = dbobj.get_column_names('intTable') self.assertEqual(len(names), 3) self.assertTrue('id' in names) self.assertTrue('twice' in names) self.assertTrue('thrice' in names) names = dbobj.get_column_names() keys = ['doubleTable', 'intTable', 'junkTable'] for kk in names: self.assertTrue(kk in keys) self.assertEqual(len(names['doubleTable']), 3) self.assertEqual(len(names['intTable']), 3) self.assertTrue('id' in names['doubleTable']) self.assertTrue('sqrt' in names['doubleTable']) self.assertTrue('log' in names['doubleTable']) self.assertTrue('id' in names['intTable']) self.assertTrue('twice' in names['intTable']) self.assertTrue('thrice' in names['intTable'])
def testTableNames(self): """ Test the method that returns the names of tables in a database """ dbobj = DBObject(driver=self.driver, database=self.database) names = dbobj.get_table_names() self.assertEqual(len(names), 3) self.assertTrue('doubleTable' in names) self.assertTrue('intTable' in names)
def testMinMax(self): """ Test queries on SQL functions by using the MIN and MAX functions """ dbobj = DBObject(driver=self.driver, database=self.database) query = 'SELECT MAX(thrice), MIN(thrice) FROM intTable' results = dbobj.execute_arbitrary(query) self.assertEqual(results[0][0], 594) self.assertEqual(results[0][1], 0) dtype = [('MAXthrice', int), ('MINthrice', int)] self.assertEqual(results.dtype, dtype)
def testPassingConnection(self): """ Repeat the test from testJoin, but with a DBObject whose connection was passed directly from another DBObject, to make sure that passing a connection works """ dbobj_base = DBObject(driver=self.driver, database=self.database) dbobj = DBObject(connection=dbobj_base.connection) query = 'SELECT doubleTable.id, intTable.id, doubleTable.log, intTable.thrice ' query += 'FROM doubleTable, intTable WHERE doubleTable.id = intTable.id' results = dbobj.get_chunk_iterator(query, chunk_size=10) dtype = [('id', int), ('id_1', int), ('log', float), ('thrice', int)] i = 0 for chunk in results: if i < 90: self.assertEqual(len(chunk), 10) for row in chunk: self.assertEqual(2 * (i + 1), row[0]) self.assertEqual(row[0], row[1]) self.assertAlmostEqual(numpy.log(row[0]), row[2], 6) self.assertEqual(3 * row[0], row[3]) self.assertEqual(dtype, row.dtype) i += 1 self.assertEqual(i, 99) #make sure that we found all the matches whe should have results = dbobj.execute_arbitrary(query) self.assertEqual(dtype, results.dtype) i = 0 for row in results: self.assertEqual(2 * (i + 1), row[0]) self.assertEqual(row[0], row[1]) self.assertAlmostEqual(numpy.log(row[0]), row[2], 6) self.assertEqual(3 * row[0], row[3]) i += 1 self.assertEqual(i, 99)
def testReadOnlyFilter(self): """ Test that the filters we placed on queries made with execute_aribtrary() work """ dbobj = DBObject(driver=self.driver, database=self.database) controlQuery = 'SELECT doubleTable.id, intTable.id, doubleTable.log, intTable.thrice ' controlQuery += 'FROM doubleTable, intTable WHERE doubleTable.id = intTable.id' controlResults = dbobj.execute_arbitrary(controlQuery) #make sure that execute_arbitrary only accepts strings query = ['a', 'list'] self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query) #check that our filter catches different capitalization permutations of the #verboten commands query = 'DROP TABLE junkTable' self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query) self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query.lower()) query = 'DELETE FROM junkTable WHERE id=4' self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query) self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query.lower()) query = 'UPDATE junkTable SET sqrt=0.0, log=0.0 WHERE id=4' self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query) self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query.lower()) query = 'INSERT INTO junkTable VALUES (9999, 1.0, 1.0)' self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query) self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query.lower()) query = 'Drop Table junkTable' self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query) query = 'Delete FROM junkTable WHERE id=4' self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query) query = 'Update junkTable SET sqrt=0.0, log=0.0 WHERE id=4' self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query) query = 'Insert INTO junkTable VALUES (9999, 1.0, 1.0)' self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query) query = 'dRoP TaBlE junkTable' self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query) query = 'dElEtE FROM junkTable WHERE id=4' self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query) query = 'uPdAtE junkTable SET sqrt=0.0, log=0.0 WHERE id=4' self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query) query = 'iNsErT INTO junkTable VALUES (9999, 1.0, 1.0)' self.assertRaises(RuntimeError, dbobj.execute_arbitrary, query)
def __init__(self): self._dbo = DBObject(database='sysarch', host='terminal.lsst.org', port='3306', driver='mysql') self._object_list = [('Name', str, 300), ('Object_ID', np.int), ('ParentID', np.int), ('Object_type', str, 300), ('Author', str, 300), ('Version', str, 300), ('Note', str, 300), ('Package_ID', np.int)] self._object_dtype = np.dtype(self._object_list) self._object_query = 'select' for datum in self._object_list: if self._object_query!='select': self._object_query += ',' self._object_query += ' t.%s' % datum[0] self._object_query += ' from t_object t'
def testSingleTableQuery(self): """ Test a query on a single table (using chunk iterator) """ dbobj = DBObject(driver=self.driver, database=self.database) query = 'SELECT id, sqrt FROM doubleTable' results = dbobj.get_chunk_iterator(query) dtype = [('id', int), ('sqrt', float)] i = 1 for chunk in results: for row in chunk: self.assertEqual(row[0], i) self.assertAlmostEqual(row[1], numpy.sqrt(i)) self.assertEqual(dtype, row.dtype) i += 1 self.assertEqual(i, 201)
def testValidationErrors(self): """ Test that appropriate errors and warnings are thrown when connecting """ with warnings.catch_warnings(record=True) as w: warnings.simplefilter("always") DBObject('sqlite:///' + self.database) assert len(w) == 1 #missing database self.assertRaises(AttributeError, DBObject, driver=self.driver) #missing driver self.assertRaises(AttributeError, DBObject, database=self.database) #missing host self.assertRaises(AttributeError, DBObject, driver='mssql+pymssql') #missing port self.assertRaises(AttributeError, DBObject, driver='mssql+pymssql', host='localhost')
def testDtype(self): """ Test that passing dtype to a query works (also test q query on a single table using .execute_arbitrary() directly """ dbobj = DBObject(driver=self.driver, database=self.database) query = 'SELECT id, log FROM doubleTable' dtype = [('id', int), ('log', float)] results = dbobj.execute_arbitrary(query, dtype=dtype) self.assertEqual(results.dtype, dtype) for xx in results: self.assertAlmostEqual(numpy.log(xx[0]), xx[1], 6) self.assertEqual(len(results), 200) results = dbobj.get_chunk_iterator(query, chunk_size=10, dtype=dtype) results.next() for chunk in results: self.assertEqual(chunk.dtype, dtype)
import time if __name__ == "__main__": # grid step size in magnitude and declination # can be set to whatever the user wants dmag = 0.1 ddec = 0.1 t_start = time.time() output_file_name = "mag_vs_dec.eps" db = DBObject(database='LSSTCATSIM', host='localhost', port=51433, driver='mssql+pymssql') cmd = "SELECT catalogid, ra, decl, umag_noatm, gmag_noatm, rmag_noatm, " cmd += "imag_noatm, zmag_noatm, ymag_noatm, residual " cmd += "FROM bright_stars WHERE ra < 20.0 and ra > 0.0" query_dtype = np.dtype([('id', long), ('ra', float), ('dec', float), ('u', float), ('g', float), ('r', float), ('i', float), ('z', float), ('y', float), ('residual', float)]) u_grid = {} g_grid = {} r_grid = {} i_grid = {}
import time if __name__ == "__main__": output_dir = "magnitude_out_dir" monet_root_dir = os.path.join("/astro", "store", "pogo1", "monetBrightStars") monet_dir_list = [] for ii in range(6): name = os.path.join(monet_root_dir, "Disc_%d" % ii) monet_dir_list.append(name) db = DBObject(database='LSSTCATSIM', host='fatboy.phys.washington.edu', port=1433, driver='mssql+pymssql') data_dtype = np.dtype([('id', long), ('ra', float), ('dec', float), ('mura', float), ('mudec', float), ('b', float), ('v', float), ('u', float), ('g', float), ('r', float), ('i', float), ('z', float), ('y', float), ('j', float), ('h', float), ('k', float), ('w1', float), ('w2', float), ('w3', float), ('w4', float), ('sst', float), ('flag', long)]) query_dtype = np.dtype([('id', long), ('u', float), ('g', float), ('r', float), ('i', float), ('z', float), ('y', float), ('residual', float)])