#/usr/bin/python # Sample script for connecting to SDSS DR9, running a query, and printing results import mx.ODBC.unixODBC as odbc import numpy as np # Connect to the database db = odbc.DriverConnect("DSN=ramses8;Database=BestDR9;UID=wsaro;PWD=wsaropw") # Initiate the Cursor cursor = db.cursor() # Execute the Query cursor.execute("SELECT * FROM INFORMATION_SCHEMA.COLUMNS") # Get the results row = cursor.fetchone() # array to store results results_array = [] # Loop through results, printing the first two columns while row: results_array.append(row) row = cursor.fetchone() results_array_np = np.asarray(results_array) print(type(results_array_np)) np.save('/home/cmurray/results_array.npy', results_array_np)
#/usr/bin/python import mx.ODBC.unixODBC as odbc import numpy as np # Connect to the database db = odbc.DriverConnect("DSN=ramses17;UID=wsaro;PWD=wsaropw") # Initiate the Cursor cursor = db.cursor() # Add galexID column to the table ########################################## # Execute the Query cursor.execute("ALTER TABLE cmurray..mgs_multiwavelength ADD galexID bigint;") # Add galexIDs into the table # query for galexID cursor.execute(" \ UPDATE cmurray..mgs_multiwavelength \ SET cmurray..mgs_multiwavelength.galexID = x.slaveObjID \ FROM cmurray..mgs_multiwavelength as m \ INNER JOIN UKIDSSDR10PLUS..lasSourceXGR6PhotoObjAll AS x \ on m.ukidssID=x.masterObjID \ INNER JOIN GalexGR6..photoObjAll as p \ on p.objID = x.slaveObjID \ WHERE x.distanceMins < 0.0666667 \ AND x.distanceMins IN (SELECT MIN(distanceMins) \ FROM UKIDSSDR10PLUS..lasSourceXGR6PhotoObjAll \ WHERE masterObjID = x.masterObjID) \ AND x.distanceMins IN (SELECT MIN(distanceMins) \ FROM UKIDSSDR10PLUS..lasSourceXGR6PhotoObjAll \
#/usr/bin/python # Sample script for connecting to SDSS DR9, running a query, and printing results import mx.ODBC.unixODBC as odbc import numpy as np # Connect to the database db_SDSS = odbc.DriverConnect( "DSN=ramses17;Database=BestDR13;UID=wsaro;PWD=wsaropw") db_VESPA = odbc.DriverConnect( "DSN=ramses8;Database=VESPA;UID=wsaro;PWD=wsaropw") # Initiate the Cursor cursor_SDSS = db_SDSS.cursor() cursor_VESPA = db_VESPA.cursor() # Execute the Query #cursor.execute("SELECT galaxy.petroMag_r,galaxy.modelMag_u,galaxy.modelMag_g,galaxy.modelMag_r,galaxy.modelMag_i,galaxy.modelMag_z, specObj.z FROM galaxy, specObj where galaxy.objID = specObj.bestObjID AND galaxy.primTarget = 0x00000040") #cursor_SDSS.execute("SELECT TOP 1 g.petroMag_r,g.modelMag_u,g.modelMag_g,g.modelMag_r, \ #g.modelMag_i,g.modelMag_z, s.z, dr7.dr7objid \ #FROM galaxy as g \ #JOIN PhotoObjDR7 as dr7 on dr7.SpecObjID=g.specObjID \ #JOIN specObj as s on g.objID=s.bestObjID \ #WHERE s.primTarget=0x00000040") cursor_SDSS.execute( "SELECT g.petroMag_r, s.z, g.modelMag_u, g.modelMag_g, g.modelMag_r, g.modelMag_i,g.modelMag_z, g.modelFluxIvar_u ,g.modelFluxIvar_g , g.modelFluxIvar_r,g.modelFluxIvar_i, g.modelFluxIvar_z, dr7.specObjID \ FROM galaxy as g \ INNER JOIN PhotoObjDR7 as dr7 \
#/usr/bin/python # Sample script for connecting to SDSS DR9, running a query, and printing results import mx.ODBC.unixODBC as odbc import numpy as np # Connect to the database db = odbc.DriverConnect("DSN=ramses17;Database=cmurray;UID=wsaro;PWD=wsaropw") # Initiate the Cursor cursor = db.cursor() # Execute the Query cursor.execute("CREATE TABLE mgs_multiwavelength \ ( \ specObjID bigint, \ objID bigint, \ petroMag_r real, \ z real, \ ur_colour real);") # load dataset data_tbl = np.load('/home/cmurray/data/final_mgs_array.npy') #data_tbl = data_tbl.T # throw data into table cursor.executemany( "INSERT INTO mgs_multiwavelength (specObjID, objID, petroMag_r, z, ur_colour) \ VALUES (?, ?, ?, ?, ?);", map(tuple, data_tbl.tolist())) # Execute the Query cursor.execute(
#/usr/bin/python import mx.ODBC.unixODBC as odbc import numpy as np import sys # Connect to the database connection_cmd = 'DSN=ramses17;Database=cmurray;UID=wsaro;PWD=wsaropw' db = odbc.DriverConnect(connection_cmd) # Initiate the Cursor cursor = db.cursor() # Execute the Query cursor.execute("SELECT objID, COUNT(*) \ FROM mgs_multiwavelength \ GROUP BY objID \ HAVING COUNT(*) > 1") # Get the results rows = cursor.fetchall() print('Duplicate objiD') print(rows) print(len(rows)) # Execute the Query cursor.execute("SELECT specobjID, COUNT(*) \ FROM mgs_multiwavelength \ GROUP BY specobjID \ HAVING COUNT(*) > 1")
#/usr/bin/python # Sample script for connecting to SDSS DR9, running a query, and printing results import mx.ODBC.unixODBC as odbc import numpy as np # Connect to the database db = odbc.DriverConnect("DSN=ramses8;Database=VESPA;UID=wsaro;PWD=wsaropw") # Initiate the Cursor cursor = db.cursor() # Execute the Query #cursor.execute("SELECT galaxy.petroMag_r,galaxy.modelMag_u,galaxy.modelMag_g,galaxy.modelMag_r,galaxy.modelMag_i,galaxy.modelMag_z, specObj.z FROM galaxy, specObj where galaxy.objID = specObj.bestObjID AND galaxy.primTarget = 0x00000040") #variable var = [("516080267020")] cursor.execute("SELECT TOP 10 * FROM lookuptable as l, galProp as g \ WHERE l.specObjID = 75375473458151424 AND l.indexP = g.indexP AND g.runID = 4") #c.execute("SELECT * FROM foo WHERE bar = %s AND baz = %s", (param1, param2)) # Get the results rows = cursor.fetchall() # array to store results for row in rows: print(row)