def StartRange(self, logic, mss_code): if logic == 1 or logic == 2: db = DBConnection.DBCon() db.setAll('localhost', 'root', '123123', 'ReallyFine', 'utf8') conn = db.ConnDB() cur = conn.cursor() startIDX = 2 sql = "select max(created_time) from public_data_pm25 where created_date = '" sql += self.searchDate + "' and mss_code = '" + str(mss_code) + "'" cur.execute(sql) data = cur.fetchone()[0] if data is None: cur.close() conn.close() return startIDX else: startIDX = data + 2 cur.close() conn.close() return startIDX
import pymysql import DBConnection if len(sys.argv) < 3: print("python3", sys.argv[0], "searchDate beforeValue") else: crawler = crawler.RF_Crawler() searchDate = sys.argv[2] beforeValue = sys.argv[3] measureBS = 'MINDB_60M' restParam = 'a&K' startLoc = 4090 endLoc = 5090 saveRange = endLoc - startLoc + 1 db = DBConnection.DBCon() db.setAll('localhost', 'root', '123123', 'ReallyFine', 'utf8') con = db.ConnDB() cur = con.cursor() # save location data for index in range(saveRange): loc = startLoc + index param = searchDate + '&' + beforeValue + '&' + measureBS + '&' + str( loc) + '&' + restParam url = crawler.setProperty(3, param) elements = crawler.getAllElements('a', 2) print(elements) startPattern = re.compile('.*">')
def saveData(self, logic, data_list, size): if logic == 1: db = DBConnection.DBCon() db.setAll('localhost', 'root', '123123', 'ReallyFine', 'utf8') conn = db.ConnDB() cur = conn.cursor() # find district code dt_name = data_list[1][1:3] sql = "select count(dt_code) from measure_station where dt_code = '" sql += self.district + "'" cur.execute(sql) data_check = cur.fetchone() if data_check is not None and data_check[0] == size: return 2 print( 'measure station data is not exist, first saved measure station data and then saved data_values' ) sql = "select dt_code from district where dt_name = '" sql += dt_name + "'" cur.execute(sql) dt_code = cur.fetchone()[0] pattern = re.compile(']') for index in range(size): i = index * 26 location = data_list[i] station = data_list[i + 1] search_char = pattern.search(station) end = search_char.end() sql = "select * from measure_station where exists(select * from measure_station where dt_code = '" sql += self.district + "' and mss_name = '" + station[ end:] + "')" cur.execute(sql) ex_check = cur.fetchone() if ex_check is not None: continue else: sql = "insert into measure_station(dt_code, mss_location, mss_name) values('" sql += dt_code + "', '" + location + "', '" + station[ end:] + "');" cur.execute(sql) cur.close() conn.commit() conn.close() return 1 elif logic == 2: db = DBConnection.DBCon() db.setAll('localhost', 'root', '123123', 'ReallyFine', 'utf8') conn = db.ConnDB() cur = conn.cursor() pattern = re.compile(']') for index in range(size): i = index * 26 station = data_list[i + 1] search_char = pattern.search(station) end = search_char.end() sql = "select mss_code from measure_station where mss_name = '" sql += station[end:] + "'" cur.execute(sql) mss_code = cur.fetchone()[0] startIDX = self.StartRange(logic, mss_code) for j in range(startIDX, 26): time = j - 1 j = i + j if data_list[j] == '-': data_list[j] = '-1' elif data_list[j] == '': break sql = "insert into public_data_pm25(mss_code, data_value, created_date, created_time) values(" sql += str(mss_code) + ", " + data_list[ j] + ", '" + self.searchDate + "', " + str(time) + ");" cur.execute(sql) cur.close() conn.commit() conn.close() return 1