def insertCoordinate(sql_select, sql_select_count, insertTable):
coordinateDic = getCoordinateDic(sql_select, sql_select_count)
conn = sqlHelper.getMySQLConn()
sql_insert = sqlHelper.genInsertSQL(insertTable, 3)
for x in coordinateDic.keys():
for y in coordinateDic[x].keys():
count = coordinateDic[x][y]
sqlHelper.execWithValues(conn, sql_insert, [x, y, count])
sqlHelper.closeConn(conn)
def migrationSDSS(filePath, tableName, colCount, blankColCount=0):
sql_insert = sqlHelper.genInsertSQL(tableName, colCount)
data = open(filePath)
conn = sqlHelper.getMySQLConn()
count = 0
for row in data:
cols = row.split(",")
if blankColCount > 0:
for i in range(blankColCount):
cols.append("")
sqlHelper.execWithValues(conn, sql_insert, cols)
count += 1
if count % 10000 == 0:
print(count)
sqlHelper.closeConn(conn)
def importLamostData(sql_select, sql_insert):
conn = sqlHelper.getMySQLConn()
conn45 = sqlHelper.getMySQL45Conn()
count = 0
data = sqlHelper.getFetchManyByConn(conn45, sql_select)
for info in data:
sqlRecord = []
colIndex = 1
try:
for col in info:
if colIndex == 13:
classType = info[10]
subClass = info[11]
mainClass = astroUtil.getMainClass(classType, subClass)
sqlRecord.append(mainClass)
sqlRecord.append(col)
colIndex += 1
sqlHelper.execWithValues(conn, sql_insert, sqlRecord)
count += 1
except Exception, ex:
exUtil.printMessage(ex, "Migration")
def dereplicationByCoordinate(tableName, idField="objID", raField="ra", decField="dec", orderbyField="objID"):
sql_select = "select %s,%s,`%s` from %s order by %s desc" % (idField, raField, decField, tableName, orderbyField)
sql_delete = "delete from " + tableName + " where " + idField + " = %s"
data = sqlHelper.getFetchAll(sql_select)
data_saved = []
conn = sqlHelper.getMySQLConn()
count = 0
derepCount = 0
for row in data:
objID = row[0]
ra = row[1]
dec = row[2]
if checkReplication(ra, dec, data_saved):
sqlHelper.execWithValues(conn, sql_delete, [objID])
derepCount += 1
print str(derepCount) + ":" + str(objID)
else:
data_saved.insert(0, [ra, dec])
count += 1
if count % 1000 == 0:
print "handled count : " + str(count)
print "total : %s , derep : %s" % (count, derepCount)
sqlHelper.closeConn(conn)
if crossedCount == 0:
print "failed :" + specid
else:
sqlHelper.execWithValues(self.conn, sql_insert, [self.specid, minID])
print "%s - succeed [%s] : %s" % (self.totalCount, str(crossedCount), specid)
data1 = sqlHelper.getFetchAll(sql_select_lamost)
data2 = sqlHelper.getFetchAll(sql_select_sdss)
data_lamost = []
data_sdss = []
for row in data1:
data_lamost.append([row[0], float(row[1]), float(row[2])])
for row in data2:
data_sdss.append([row[0], float(row[1]), float(row[2])])
conn = sqlHelper.getMySQLConn()
totalCount = 0
for row_lamost in data_lamost:
specid = row_lamost[0]
ra = row_lamost[1]
dec = row_lamost[2]
crossThread = handler(specid, ra, dec, data_sdss, conn, totalCount)
crossThread.start()
totalCount += 1
sqlHelper.closeConn(conn)
def preHandle(self):
self.sql_insert = sqlHelper.genInsertSQL(self.insertTable, 20)
self.conn = sqlHelper.getMySQLConn()