#/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)