示例#1
0
#/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)
示例#2
0
#/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	\
示例#3
0
#/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		\
示例#4
0
#/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(
示例#5
0
#/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")
示例#6
0
#/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)